Skip to content

How to Resolve Trigger "ERROR 1442 (HY000): Can't update table 'table_name' in stored function/trigger"

The following examples are trying to create triggers to update some data in current table at event AFTER INSERT.

Trigger #1:

CREATE TRIGGER `example1trigger` AFTER INSERT ON `table_name`
FOR EACH ROW
begin
update table_name
set sum = cost * 1.05
where id = NEW.id;
end;

Trigger #2:

CREATE TRIGGER `example2trigger` AFTER INSERT ON `table_name`
FOR EACH ROW
begin
update table_name
set create_date = CURRENT_DATE
where id = NEW.id;
end;

Both triggers will be created successfully, but when we actually insert a row in current table, it will generate an error:

ERROR 1442 (HY000): Can't update table 'table_name' in stored function/trigger because it is already used by statement which invoked this function/trigger.

The truth is that the current trigger is using table "table_name", there is no chance to use it in this invoking procedure.

Since the update statement depends the newly inserted data in Trigger #1, the solution is to abandon the trigger and put both INSERT and UPDATE in a store procedure instead.

It could have another solution for Trigger #2, you can change AFTER INSERT to BEFORE INSERT and simplify the statements like this:

CREATE TRIGGER `example2trigger` BEFORE INSERT ON `table_name`
FOR EACH ROW
begin
set NEW.create_date = CURRENT_DATE;
end;

It runs well without problem in most cases, but it does not guarantee every new row to be successful inserted after all.

For more syntax about triggers, you can refer to the official document: MySQL :: MySQL 5.7 Reference Manual :: 19.3.1 Trigger Syntax and Examples.

Leave a Reply

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