Updating of NEW row is not allowed in after trigger

mysql triggers runtime-error insertafter
m.safivand picture m.safivand · Sep 17, 2015 · Viewed 14.4k times · Source

Why do I get an error using this trigger?

CREATE TRIGGER save_Assignee AFTER INSERT ON changeitem
FOR EACH ROW 
BEGIN 
    SET new.assignee = (
        SELECT assignee
        FROM jiraissue INNER JOIN changegroup ON jiraissue.ID = changegroup.issueid
    )
END


Error message:

#1362 - Updating of NEW row is not allowed in after trigger

Answer

Gordon Linoff picture Gordon Linoff · Sep 17, 2015

That is correct. You need a before insert trigger if you want to modify the data:

create TRIGGER save_Assignee BEFORE INSERT ON changeitem FOR EACH ROW
BEGIN
    SET new.assignee = (select assignee
                        from jiraissue INNER JOIN
                             changegroup
                             ON jiraissue.ID = changegroup.issueid
                       )
END

As suggested by the name, the AFTER insert trigger is run after the data has been updated. So, if you want to update the same row, use a before trigger.

Your subquery looks suspicious because it is not correlated with new and it looks like it could return more than one row.

Related questions

MySQL Trigger after update only if row has changed

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count &…

Read More
MySQL Trigger - Storing a SELECT in a variable

I have a trigger in which I want to have a variable that holds an INT I get from a SELECT, so I can use it in two IF statements instead of calling the SELECT twice. How do you declare/…

Read More
How do you list all triggers in a MySQL database?

What is the command to list all triggers in a MySQL database?

Read More