// Oracle error reference

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
Error message
table string.string is mutating, trigger/function may not see it

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 ROW trigger that runs SELECT COUNT(*) or UPDATE on 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

  1. Use the :NEW and :OLD values instead of re-querying the row.

  2. 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;
  3. Or enforce the rule with a constraint or in the application instead of a trigger.

Related errors

More pl/sql errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

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.