Updating of NEW row is not allowed in after trigger

Question

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

Pettiford

Answer 1

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.

Badger

same as: #17823957 – Angi