MySQL error 1442: Trigger can't update its own table
A trigger or stored function tries to modify the same table as the statement that fired it, which MySQL doesn't allow.
- Error number
1442- Name
ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG- SQLSTATE
HY000- Category
- Databases, tables and columns
What it means
In MySQL a trigger can't run INSERT, UPDATE or DELETE on the table the triggering statement is changing, and a stored function can't modify a table the calling statement uses. The way to change the row being written is to set NEW.column in a BEFORE trigger.
Common causes
- An
AFTER UPDATEtrigger that runsUPDATEon its own table, for example to set a timestamp. - A function called from a statement that modifies the same table.
How to fix it
-
Change the row itself in a
BEFOREtrigger.CREATE TRIGGER orders_touch BEFORE UPDATE ON orders FOR EACH ROW SET NEW.updated_at = NOW();
-
Or let the column do it:
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. -
Move changes to other rows of the same table into application code or a stored procedure called after the statement.
Related errors
1093Can't specify target table for updateORA-04091Oracle: table is mutating, trigger may not see it
More databases, tables and columns errors
1049Unknown database1046No database selected1054Unknown column in field list1146Table doesn't exist1050Table already exists1051Unknown table1060Duplicate column name1091Can't DROP; check that column/key exists
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.