ORA-04091: table is mutating, trigger/function may not see it
ORA-04091: table LAB_A.EMP is mutating, trigger/function may not see itA 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
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
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;
/Table created.
1 row created.
1 row created.
1 row created.
Commit complete.
Trigger created.UPDATE emp SET salary = salary * 1.1 WHERE dept_id = 10;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/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;
/Trigger dropped.
Trigger created.A statement trigger has no :NEW / :OLD, so it checks the table as a whole instead of each row.
UPDATE emp SET salary = salary * 1.1 WHERE dept_id = 10;
SELECT emp_id, dept_id, salary FROM emp ORDER BY emp_id;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
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;
/Table created.
1 row created.
1 row created.
1 row created.
Commit complete.
Function created.UPDATE emp SET bonus = dept_avg(dept_id) * 0.1;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/SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id ORDER BY dept_id; DEPT_ID AVG_SAL
---------- ----------
10 4500
20 3000
2 rows selected.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;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
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;
/Table created.
Table created.
1 row created.
1 row created.
1 row created.
Commit complete.
Trigger created.DELETE FROM orders WHERE order_id = 1;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/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;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.
DELETE FROM order_line WHERE order_id = 1;
DELETE FROM orders WHERE order_id = 1;
SELECT COUNT(*) AS lines_left FROM order_line;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