Skip to content

How to Resolve ERROR 1362 (HY000)

Updating of NEW row is not allowed in after trigger

You might think you can set new values of columns on the new row after inserting in a trigger, but you can't:

mysql> delimiter //
mysql> CREATE TRIGGER test_trigger AFTER INSERT ON table_name
    -> FOR EACH ROW
    -> begin
    ->     set new.total = new.salary + new.bonus;
    -> end//
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

OK, MySQL does not support this, how about a true update statement on itself instead of a "set":

mysql> CREATE TRIGGER test_trigger AFTER INSERT ON table_name
    -> FOR EACH ROW
    -> begin
    ->     update table_name set new.total = new.salary + new.bonus where id = new.id;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql>

The trigger is syntactically correct and can be created at compile-time, but it will fail at run-time when you are trying to insert a row.

mysql> insert into table_name set salary = 3000, bonus = 500;
ERROR 1442 (HY000): Can't update table 'table_name' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

It means that the new row is already locked by your statement, it's no way to update the row by the trigger and the transaction is rolled back, no row is inserted.

Solution

To solve this, the simplest way is to change the trigger time from AFTER INSERT into BEFORE INSERT in the first block of codes:

mysql> DROP TRIGGER test_trigger;
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter //
mysql> CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
    -> FOR EACH ROW
    -> begin
    ->     set new.total = new.salary + new.bonus;
    -> end//
Query OK, 0 rows affected (0.03 sec)

Let's try to insert a new row.

mysql> insert into table_name set salary = 3000, bonus = 500;//
Query OK, 1 row affected (0.00 sec)

The reason is that if you want to set new values of columns on the new row by a trigger, the best way is to make the new values join in the insert-ready ad hoc of the new row before the new row is actually inserted.

For more syntax about triggers, you may refer to the official document: MySQL :: MySQL 8.0 Reference Manual :: 23.3.1 Trigger Syntax and Examples.

Leave a Reply

Your email address will not be published. Required fields are marked *