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

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

A row trigger or a function read or changed a table that the current statement is still modifying.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14

While an UPDATE, INSERT or DELETE is running, its table is "mutating". A row-level trigger (FOR EACH ROW) or a function called from that statement cannot read or change the same table, because it would see a half-finished result.

The message names the mutating table (LAB_A.EMP here), and the ORA-06512 / ORA-04088 lines name the trigger or function that touched it. Start from those two names.

Causes and fixes

  1. A row trigger queries the same table it fires on
  2. A function called inside UPDATE reads the table being updated
  3. ON DELETE CASCADE fires a child trigger that updates the parent

1A row trigger queries the same table it fires on

The BEFORE UPDATE ... FOR EACH ROW trigger emp_sal_chk runs SELECT AVG(salary) FROM emp to compare the new salary with the department average. The trigger is on emp, so the UPDATE failed with ORA-04091 at "LAB_A.EMP_SAL_CHK", line 4, followed by ORA-04088.

The trigger was rewritten as a statement-level trigger (AFTER UPDATE OF salary ON emp, no FOR EACH ROW). It runs once after the UPDATE finishes, when the table can be read, and checks every row against its department average. The same UPDATE then ran: 2 rows updated, salaries 5500 and 4400.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (101, 10, 5000);
INSERT INTO emp VALUES (102, 10, 4000);
INSERT INTO emp VALUES (103, 20, 3000);
COMMIT;
CREATE OR REPLACE TRIGGER emp_sal_chk
BEFORE UPDATE OF salary ON emp
FOR EACH ROW
DECLARE
  v_avg NUMBER;
BEGIN
  SELECT AVG(salary) INTO v_avg FROM emp WHERE dept_id = :NEW.dept_id;
  IF :NEW.salary > v_avg * 2 THEN
    RAISE_APPLICATION_ERROR(-20001, 'salary over 2x dept average');
  END IF;
END;
/
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.


Trigger created.
Reproducerun as lab_a
UPDATE emp SET salary = salary * 1.1 WHERE dept_id = 10;
OUTPUT
UPDATE emp SET salary = salary * 1.1 WHERE dept_id = 10
       *
ERROR at line 1:
ORA-04091: table LAB_A.EMP is mutating, trigger/function may not see it
ORA-06512: at "LAB_A.EMP_SAL_CHK", line 4
ORA-04088: error during execution of trigger 'LAB_A.EMP_SAL_CHK'
Help: https://docs.oracle.com/error-help/db/ora-04091/
Fixrun as lab_a
DROP TRIGGER emp_sal_chk;
CREATE OR REPLACE TRIGGER emp_sal_chk
AFTER UPDATE OF salary ON emp
DECLARE
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM emp e
   WHERE e.salary > 2 * (SELECT AVG(x.salary) FROM emp x WHERE x.dept_id = e.dept_id);
  IF v_cnt > 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'salary over 2x dept average');
  END IF;
END;
/
OUTPUT
Trigger dropped.


Trigger created.

A statement trigger has no :NEW / :OLD, so it checks the table as a whole instead of each row.

After the fixrun as lab_a
UPDATE emp SET salary = salary * 1.1 WHERE dept_id = 10;
SELECT emp_id, dept_id, salary FROM emp ORDER BY emp_id;
OUTPUT
2 rows updated.


    EMP_ID    DEPT_ID     SALARY
---------- ---------- ----------
       101         10       5500
       102         10       4400
       103         20       3000

3 rows selected.

2A function called inside UPDATE reads the table being updated

dept_avg(dept_id) runs SELECT AVG(salary) FROM emp. Calling it from UPDATE emp SET bonus = dept_avg(dept_id) * 0.1 means the function reads emp while emp is being updated, so it failed at "LAB_A.DEPT_AVG", line 4. There is no trigger involved here.

Move the lookup into the UPDATE itself as a correlated subquery: SET bonus = (SELECT AVG(x.salary) FROM emp x WHERE x.dept_id = e.dept_id) * 0.1. It updated 3 rows; the bonuses (450, 450, 300) match the department averages 4500 and 3000 checked first.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, dept_id NUMBER, salary NUMBER, bonus NUMBER);
INSERT INTO emp VALUES (101, 10, 5000, NULL);
INSERT INTO emp VALUES (102, 10, 4000, NULL);
INSERT INTO emp VALUES (103, 20, 3000, NULL);
COMMIT;
CREATE OR REPLACE FUNCTION dept_avg (p_dept NUMBER) RETURN NUMBER IS
  v_avg NUMBER;
BEGIN
  SELECT AVG(salary) INTO v_avg FROM emp WHERE dept_id = p_dept;
  RETURN v_avg;
END;
/
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.


Function created.
Reproducerun as lab_a
UPDATE emp SET bonus = dept_avg(dept_id) * 0.1;
OUTPUT
UPDATE emp SET bonus = dept_avg(dept_id) * 0.1
                       *
ERROR at line 1:
ORA-04091: table LAB_A.EMP is mutating, trigger/function may not see it
ORA-06512: at "LAB_A.DEPT_AVG", line 4
Help: https://docs.oracle.com/error-help/db/ora-04091/
Checkrun as lab_a
SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id ORDER BY dept_id;
OUTPUT
   DEPT_ID    AVG_SAL
---------- ----------
        10       4500
        20       3000

2 rows selected.
After the fixrun as lab_a
UPDATE emp e
   SET bonus = (SELECT AVG(x.salary) FROM emp x WHERE x.dept_id = e.dept_id) * 0.1;
SELECT emp_id, dept_id, bonus FROM emp ORDER BY emp_id;
OUTPUT
3 rows updated.


    EMP_ID    DEPT_ID      BONUS
---------- ---------- ----------
       101         10        450
       102         10        450
       103         20        300

3 rows selected.

3ON DELETE CASCADE fires a child trigger that updates the parent

order_line references orders with ON DELETE CASCADE, and the row trigger order_line_total (AFTER DELETE on order_line) subtracts the amount from orders.total. Deleting order 1 cascades to its lines, the trigger tries to UPDATE orders, and orders is the table being deleted from. The error names LAB_A.ORDERS, not the child table.

Here the lines were deleted first (2 rows deleted), then the order (1 row deleted). When the lines are deleted on their own, orders is not mutating, so the trigger works.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, total NUMBER);
CREATE TABLE order_line (line_id NUMBER PRIMARY KEY, order_id NUMBER REFERENCES orders ON DELETE CASCADE, amount NUMBER);
INSERT INTO orders VALUES (1, 300);
INSERT INTO order_line VALUES (11, 1, 100);
INSERT INTO order_line VALUES (12, 1, 200);
COMMIT;
CREATE OR REPLACE TRIGGER order_line_total
AFTER DELETE ON order_line
FOR EACH ROW
BEGIN
  UPDATE orders SET total = total - :OLD.amount WHERE order_id = :OLD.order_id;
END;
/
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


Commit complete.


Trigger created.
Reproducerun as lab_a
DELETE FROM orders WHERE order_id = 1;
OUTPUT
DELETE FROM orders WHERE order_id = 1
            *
ERROR at line 1:
ORA-04091: table LAB_A.ORDERS is mutating, trigger/function may not see it
ORA-06512: at "LAB_A.ORDER_LINE_TOTAL", line 2
ORA-04088: error during execution of trigger 'LAB_A.ORDER_LINE_TOTAL'
Help: https://docs.oracle.com/error-help/db/ora-04091/
Checkrun as lab_a
COLUMN trigger_name FORMAT A20
COLUMN table_name FORMAT A12
COLUMN triggering_event FORMAT A8
SELECT trigger_name, table_name, triggering_event FROM user_triggers;
OUTPUT
TRIGGER_NAME         TABLE_NAME   TRIGGERI
-------------------- ------------ --------
ORDER_LINE_TOTAL     ORDER_LINE   DELETE

1 row selected.

This avoids the cascade rather than fixing it: every place that deletes orders has to delete the lines first. Check user_triggers on the child table to see which trigger is involved.

After the fixrun as lab_a
DELETE FROM order_line WHERE order_id = 1;
DELETE FROM orders WHERE order_id = 1;
SELECT COUNT(*) AS lines_left FROM order_line;
OUTPUT
2 rows deleted.


1 row deleted.


LINES_LEFT
----------
         0

1 row selected.

FAQ

How do I fix the mutating table error in a trigger?

Don't query the trigger's own table in a FOR EACH ROW trigger. In this lab, changing it to a statement-level trigger (no FOR EACH ROW) let the same UPDATE succeed.

Why does ORA-04091 happen without any trigger?

A function called inside an UPDATE or DELETE that reads the same table raises it too. Replace the function call with a correlated subquery in the statement.

Why does ORA-04091 name the parent table when I deleted from the parent?

With ON DELETE CASCADE, a trigger on the child table that updates the parent touches the table you are deleting from. Delete the child rows first, then the parent.

Which trigger caused ORA-04091?

Look at the ORA-06512 and ORA-04088 lines under the error. They name the trigger or function, e.g. error during execution of trigger 'LAB_A.EMP_SAL_CHK'.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors