Explain the PL/SQL mutating table error and how to avoid it

How it works

ORA-04091 ('table is mutating') happens when a row-level trigger tries to query or DML the same table its own triggering statement is still in the middle of changing — Oracle can't guarantee a consistent read of a table that hasn't finished changing yet. Common fixes: move the logic into an AFTER STATEMENT trigger (or the after-statement section of a compound trigger), once the table has settled, or accumulate what's needed in a collection during the row-level firing and act on it afterward.

-- This raises ORA-04091 if it fires during an UPDATE on employees:
CREATE OR REPLACE TRIGGER trg_emp_bad
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
  v_avg_salary NUMBER;
BEGIN
  SELECT AVG(salary) INTO v_avg_salary FROM employees; -- querying the mutating table
  IF :NEW.salary > v_avg_salary * 3 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary too far above average');
  END IF;
END;
/
-- Fix: do the AVG(salary) check in an AFTER STATEMENT trigger (or a
-- compound trigger's after-statement section) instead.

Related Questions