ORA-04091: table is mutating, trigger/function may not see it

ORA-04091: table SCHEMA.TABLE is mutating, trigger/function may not see it

A row-level trigger, or a function it calls, queried or changed the table that is being modified. The table is mid-change and its contents are not consistent. Use a compound trigger, or move the logic out of the row trigger.

What ORA-04091 means

While a statement is changing a table, a FOR EACH ROW trigger on that table cannot read it, because the result would depend on the order in which rows happen to be processed. Oracle prevents this with ORA-04091. The ORA-06512 lines after it show which trigger was running.

Common causes of ORA-04091

• A row-level trigger selecting from its own table, for example to count or sum rows.
• A function called from the trigger or from the DML that reads the table.
• A trigger on a child table reading the parent while a cascading delete runs.
• Using the trigger to enforce a rule across several rows.

How to fix ORA-04091

1. In many cases :NEW and :OLD already give what you need; no query is required.
2. Use a compound trigger: collect rows in the AFTER EACH ROW section, and do the query in AFTER STATEMENT, where the table is stable.
3. Consider a constraint or application logic for cross-row rules.
4. Avoid PRAGMA AUTONOMOUS_TRANSACTION as a shortcut: it does not see the uncommitted changes, so it can silently read wrong data.

ORA-04091 example

Reading the table in a row trigger fails; a compound trigger waits for the statement to finish:

CREATE OR REPLACE TRIGGER orders_total_trg
FOR INSERT OR UPDATE ON order_lines
COMPOUND TRIGGER
  TYPE t_ids IS TABLE OF order_lines.order_id%TYPE;
  g_ids t_ids := t_ids();

  AFTER EACH ROW IS
  BEGIN
    g_ids.EXTEND;
    g_ids(g_ids.LAST) := :NEW.order_id;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FOR i IN 1 .. g_ids.COUNT LOOP
      UPDATE orders o
         SET o.total = (SELECT SUM(l.amount) FROM order_lines l WHERE l.order_id = o.id)
       WHERE o.id = g_ids(i);
    END LOOP;
  END AFTER STATEMENT;
END orders_total_trg;
/

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Related errors

ORA-06550ORA-00060ORA-01403 All Oracle errors →

Oracle documentation for ORA-04091. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.