Cannot update value on insert if salary is greater than-CodePudding

I have a problem creating a trigger for a basic table that will check on insert if one of the values inserted is bigger than 3000 and replace it with 0. It throws this error:

Can't update table 'staff' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

The structure of the table is very simple:

CREATE TABLE `staff` (
  `ID` int(11) NOT NULL,
  `NAZWISKO` varchar(50) DEFAULT NULL,
  `PLACA` float DEFAULT NULL
)

And the trigger for it looks like this:

BEGIN
  IF new.placa >= 3000 THEN
     UPDATE staff SET new.placa = 0;
  END IF;
END

I don't understand fully what occurs here, but I suspect some recursion, but I am quite new to the topic of triggers and I have lab coming, so I want to be prepared for it.

CodePudding user response：

MySQL disallows triggers from doing UPDATE/INSERT/DELETE against the same table for which the trigger executed, because there is too great a chance of causing an infinite loop. That is, in UPDATE trigger, if you could UPDATE the same table, that would cause the UPDATE trigger to execute, which would UPDATE the same table, and so on and so on.

But I guess you only want to change the value of placa on the same row being handled by the trigger. If so, just SET it:

BEGIN
  IF new.placa >= 3000 THEN
     SET new.placa = 0;
  END IF;
END

Remember that you must use a BEFORE trigger when changing column values.