sqlmysqldatabasetriggersmysql-error

"There is no OLD row in on INSERT trigger" error in MySQL

I tried to create a trigger with OLD.num and NEW.num as shown below:

CREATE TRIGGER my_trigger
AFTER INSERT ON test FOR EACH ROW
SET @old_num = OLD.num, @new_num = NEW.num;
             -- ↑ Here           -- ↑ Here

But, I got the error below:

ERROR 1363 (HY000): There is no OLD row in on INSERT trigger

So, how can I solve it?

Solution

  • The most likely explanation for getting an error 

    "There is no OLD row in on INSERT trigger"

    is that you are executing a statement that's creating an AFTER INSERT trigger, rather than creating an AFTER UPDATE trigger.

    The reason that you can't reference OLD values from the row, as the row existed prior to the INSERT, is that the row did not exist prior to the INSERT.