ORA-04091: table is mutating, trigger may not see it
A row-level trigger (or a function it calls) queried or changed the table the triggering statement is modifying, which Oracle forbids.
- Error code
ORA-04091- Category
- PL/SQL
What it means
While a statement is changing a table, its rows are in flux, so Oracle doesn't let a row-level trigger on that table read or modify it. The full 19c message is table string.string is mutating, trigger/function may not see it. Statement-level triggers and compound triggers don't have this restriction for the statement-level parts.
Common causes
- A
FOR EACH ROWtrigger that runsSELECT COUNT(*)orUPDATEon its own table. - A function called from the trigger, or from the DML itself, that queries the table.
- Cascading foreign key actions that reach a table with such a trigger.
How to fix it
-
Use the
:NEWand:OLDvalues instead of re-querying the row. -
Move checks that need the whole table into a compound trigger: collect ids in the row section and query the table in
AFTER STATEMENT.CREATE OR REPLACE TRIGGER orders_limit FOR INSERT ON orders COMPOUND TRIGGER AFTER STATEMENT IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM orders WHERE status = 'OPEN'; IF l_count > 1000 THEN RAISE_APPLICATION_ERROR(-20001, 'Too many open orders'); END IF; END AFTER STATEMENT; END; -
Or enforce the rule with a constraint or in the application instead of a trigger.
Related errors
More pl/sql errors
ORA-01403no data foundORA-01422exact fetch returns more than requested rowsORA-06502PL/SQL: numeric or value errorORA-06550PL/SQL compilation error at line, columnORA-06508PL/SQL: could not find program unit being calledORA-06503PL/SQL: function returned without valueORA-04068existing state of packages has been discarded
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.