Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A trigger or stored function fails with error 1442 on its own table

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

Short answer

MySQL forbids a stored function or trigger from modifying a table that the invoking statement is already reading or writing. That is error 1442. If a trigger needs to change the row being saved, assign to NEW in a BEFORE trigger. If a function is the problem, turn it into a stored procedure and CALL it.

  • Applies to MySQL 8.4 LTS, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the table name in the message, then print the trigger or function body to see which statement inside it touches that table.

    SHOW CREATE TRIGGER shop.orders_after_update\G
    SHOW CREATE FUNCTION shop.my_func\G
  2. 2Match what you see, then jump to its fix

    1. 1The table named in the error is the trigger's own table, for example an UPDATE orders inside a trigger on orders.A trigger writes to the table that fired itFix
    2. 2The error appears when a query such as UPDATE t SET c = my_func(id) runs, and my_func changes table t.A stored function modifies a table used by the calling statementFix

What does the error look like?

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

Cause 1 of 2

A trigger writes to the table that fired it

1Check

The trigger body runs a separate UPDATE or INSERT on the table it is attached to. The statement that fired the trigger is already using that table, so the rule applies.

SHOW CREATE TRIGGER shop.orders_after_update\G

2Fix

To change a value in the row being inserted or updated, do not run an UPDATE. Use a BEFORE trigger and assign to the column on NEW.

DROP TRIGGER shop.orders_after_update;
DELIMITER //
CREATE TRIGGER shop.orders_before_update
BEFORE UPDATE ON shop.orders
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END//
DELIMITER ;

3Verify

Run the statement that failed before.

UPDATE shop.orders SET status = 'paid' WHERE id = 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

A stored function modifies a table used by the calling statement

1Check

Stored functions, and triggers, may not modify a table the invoking statement is using for reading or writing. Stored procedures are not under this limit, unless the procedure is itself called from a function or a trigger.

SHOW CREATE FUNCTION shop.my_func\G

2Fix

Move the table-changing work into a stored procedure and call it with CALL from your application or script, instead of calling a function inside the statement.

DELIMITER //
CREATE PROCEDURE shop.refresh_totals(IN p_id INT)
BEGIN
  UPDATE shop.orders SET status = 'checked' WHERE id = p_id;
END//
DELIMITER ;
CALL shop.refresh_totals(1);

3Verify

CALL returns without error 1442.

CALL shop.refresh_totals(1);

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use BEFORE triggers when the aim is to adjust the row being written.
  • Keep logic that changes other rows of the same table out of triggers and functions; put it in a procedure.
  • Remember that a procedure called from inside a trigger carries the same limits as the trigger.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.