MySQL Error 1442

Question

I have two tables:

sanction with these attributes: DriverID, Calification, Points
people with these attributes: pID, city , TotalPoints

And i have this trigger:

DROP TRIGGER IF EXISTS updatePoints_tgr;

delimiter $$
CREATE  TRIGGER
updatePoints_tgr AFTER UPDATE
ON sanctions FOR EACH ROW
 BEGIN
     if NEW.points > OLD.points 
     THEN
         UPDATE people
         SET TotalPoints = TotalPoints + (NEW.points - OLD.points)
         WHERE people.pID = NEW.DriverID;
     elseif NEW.points < OLD.points 
     THEN
         UPDATE people
         SET TotalPoints = TotalPoints - (NEW.points - OLD.points)
         WHERE people.pID = NEW.DriverID;
     END IF;
 END$$
 delimiter ;

And when I try to execute this update

UPDATE sanctions 
JOIN people ON sanctions.DriverID=people.pID
SET points=points+6
WHERE city='Barcelona'  AND Calification='LightPenalty'

I get this error:

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

How can i fix it? Thanks.

I use Windows 10 and MySQL server 5.7.17

I'm not an expert at this, but I don't think you can.... which, to be fair, is a little surprising for an `AFTER UPDATE`. — Lightness Races in Orbit, Apr 14 '17 at 16:55
We can try to prove (using the bug tracker) that it's not a bug specific to that version (or maybe it is!) but instead an expected behaviour. I will come back to this question a bit later and see what I can find if nobody beats me to it, because it's interesting. — Lightness Races in Orbit, Apr 14 '17 at 17:02
Ahead of time, here's some vaguely related information confirming my suspicions (ish): http://stackoverflow.com/q/15300673/560648 — Lightness Races in Orbit, Apr 14 '17 at 17:17
@BoundaryImposition In that question it is using the same table on the trigger and on the update inside of the trigger. (Sorry if i am not explaining myself clearly), and I think that is not my case. — J.Coe, Apr 14 '17 at 17:26

score 0 · Answer 1 · answered Jun 14 '17 at 08:34

You cannot update a table (sanctions and people) where the trigger is invoked:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Doing so will generate Error 1442:

Error Code: 1442
Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Your case: The confusion can be due to the JOIN on sanctions and people for the UPDATE - making both the table in use, as stated above 'used' means 'reading or writing'.

To test - Try the UPDATE query without the JOIN (with people) - if it works, surely it is the case.

MySQL Error 1442

1 Answers1