How to Fix MySQL Error 1442: Can't Update Table in Stored Function or Trigger

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 is raised when a trigger or stored function attempts to read from or write to the same table that fired the trigger. The error symbol is ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG.

Impact

The statement that caused the trigger to fire is rolled back immediately. If the trigger is part of a larger transaction, the entire transaction may be aborted depending on the error handling in place. The error surfaces at the time the triggering DML statement (INSERT, UPDATE, or DELETE) is executed — not when the trigger is created — so it can appear unexpectedly in production after a schema change or data migration.

In ORMs like Hibernate, SQLAlchemy, or ActiveRecord, this typically surfaces as a database-level exception wrapped in the framework's own exception type (e.g., PersistenceException, OperationalError, StatementInvalid). Because the trigger is invisible to the ORM, developers often see this error without understanding why a simple save() call is failing.

Common Causes

AFTER INSERT/UPDATE trigger that modifies the triggering table. A common pattern is an AFTER INSERT trigger on orders that tries to UPDATE orders SET status = 'processed' WHERE id = NEW.id. This directly modifies the table currently locked by the triggering statement.
BEFORE INSERT/UPDATE trigger that queries and modifies the same table. A BEFORE INSERT trigger that checks existing rows and also updates a counter or audit column on the same table will hit this restriction.
Stored function called from a trigger that touches the trigger's base table. If a stored function is invoked inside a trigger, and that function executes DML against the table that owns the trigger, MySQL raises 1442 because the function's call chain is traced back to the triggering statement.
Cascading trigger chains. Trigger A fires on table X, which updates table Y, which fires trigger B, which in turn tries to modify table X again. MySQL detects the cycle and raises the error.
Using NEW or OLD assignment inside a BEFORE trigger via a subquery on the same table. Attempting to derive a default value for NEW.col by selecting from the same table (e.g., SET NEW.rank = (SELECT MAX(rank) + 1 FROM items)) is blocked for INSERT triggers since the row is considered part of the table being modified.

Troubleshooting and Resolution Steps

Identify the trigger definition causing the error. Inspect all triggers on the affected table:

SHOW TRIGGERS FROM your_database LIKE 'table_name';
-- or more detail:
SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_TABLE = 'table_name'
  AND TRIGGER_SCHEMA = 'your_database';

Move the self-referencing logic out of the trigger and into the application layer. This is the most reliable fix. Instead of an AFTER INSERT trigger updating the same row, perform both the insert and the follow-up update in the application within a single transaction:
```
START TRANSACTION;
INSERT INTO orders (customer_id, amount) VALUES (42, 99.00);
UPDATE orders SET status = 'processed' WHERE id = LAST_INSERT_ID();
COMMIT;
```
Use a BEFORE trigger to set computed values instead of an AFTER trigger. If the goal is to set a derived column at insert time, a BEFORE INSERT trigger can set NEW.col directly without touching the table:
```
-- Instead of: AFTER INSERT updating the new row
-- Use: BEFORE INSERT setting NEW directly
CREATE TRIGGER set_order_status
BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.status = 'pending';
```
Note: you still cannot SELECT from orders inside a BEFORE INSERT trigger on orders.
Route the cross-table update through a separate helper table or queue. For audit logging or denormalization, write to a separate table (e.g., order_events) and aggregate later, rather than updating the source table from within its own trigger:
```
CREATE TRIGGER log_order_change
AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO order_events (order_id, old_status, new_status, changed_at)
VALUES (OLD.id, OLD.status, NEW.status, NOW());
```
Refactor stored functions to avoid DML on the calling table. If a stored function used in a trigger context needs to write audit data or counters, extract that write into the trigger body itself (pointing at a different table), or move the logic entirely to a stored procedure called from the application.
Break cascading trigger chains. If table X triggers an update on table Y which triggers a write back to X, redesign the schema or consolidate the logic into a single application-level operation or stored procedure. Use information_schema.TRIGGERS to map the full trigger graph across related tables.

Additional Information

The SQLSTATE HY000 is a generic "general error" class; the specific restriction is encoded in the MySQL error number 1442.
This restriction applies to all storage engines and has been consistent across MySQL 5.x, 8.0, and later versions. It is not a bug — it is enforced to prevent infinite loops and unpredictable locking behavior.
The restriction covers both reads and writes to the triggering table when the operation could cause recursive trigger invocation. However, MySQL 5.7+ does allow reading (SELECT) from the triggering table in some trigger contexts under certain conditions; writes are always blocked.
MariaDB enforces the same restriction and raises the same error code.
ORMs: Hibernate users should look for org.hibernate.exception.GenericJDBCException; SQLAlchemy raises sqlalchemy.exc.OperationalError; ActiveRecord raises ActiveRecord::StatementInvalid. In all cases, the underlying cause is the MySQL 1442 at the database level.
If you are using an ORM that transparently creates triggers (e.g., some audit logging plugins), check whether the plugin creates triggers on the same table as the entity being saved.

Frequently Asked Questions

Can I read from the triggering table inside a trigger? In most cases, MySQL allows SELECT statements on the triggering table inside a trigger body (e.g., to look up related rows). The restriction primarily targets write operations (INSERT, UPDATE, DELETE) that would modify the table currently involved in the triggering statement. However, the exact behavior can vary depending on MySQL version and trigger type (BEFORE vs AFTER), so it is safest to test explicitly.

Why does MySQL prevent triggers from updating their own table? MySQL's trigger implementation uses a statement-level lock model. If a trigger on orders could update orders, that update could fire the same trigger again, leading to infinite recursion. MySQL prevents this at parse/execute time rather than relying on a depth limit, which makes the restriction an absolute one rather than a configurable threshold.

Can I work around this with a stored procedure instead of a trigger? Yes. A stored procedure is called explicitly from application code, so it is not subject to the trigger re-entrancy restriction. You can replace an AFTER INSERT trigger that modifies the same table with a stored procedure that performs both the insert and the follow-up update atomically. The tradeoff is that the application must always call the stored procedure instead of issuing a raw INSERT.

Does this error only occur for the exact table that fired the trigger, or for all tables in the call chain? The restriction applies specifically to the table that caused the current trigger to fire. Other tables in the trigger body can be freely read and written. If you have a chain — trigger on table A modifies table B, which fires a trigger that modifies table A — that is also blocked, but the error will reference table A as the conflicting table.