Oracle Mutating Table Error FAQs with Examples

An Oracle mutating table error occurs when a row-level trigger tries to read from or modify the same table that is currently being modified by the triggering statement.

The classic error is:

ORA-04091: table <TABLE_NAME> is mutating, trigger/function may not see it
ORA-06512: at "<TRIGGER_NAME>", line ...

The key idea is:

UPDATE employees
       ↓
Row-level trigger
       ↓
SELECT ... FROM employees
       ↓
❌ ORA-04091: table is mutating

1. What is a mutating table?

A mutating table is a table that is currently being modified by a DML statement.

For example:

UPDATE employees
SET salary = salary + 1000;

While Oracle is processing this statement, the employees table is considered mutating.

If a row-level trigger on employees tries to query employees, Oracle can raise:

ORA-04091

2. What is the classic example?

Suppose we have:

CREATE TABLE employees (
    employee_id NUMBER,
    department_id NUMBER,
    salary NUMBER
);

Create a row-level trigger:

CREATE OR REPLACE TRIGGER trg_emp_check
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees
    WHERE department_id = :NEW.department_id;

END;
/

Now execute:

UPDATE employees
SET salary = salary + 1000
WHERE department_id = 10;

Oracle may raise:

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

Why?

EMPLOYEES is being updated
        ↓
Row-level trigger fires
        ↓
Trigger queries EMPLOYEES
        ↓
EMPLOYEES is mutating
        ↓
ORA-04091

3. Why does Oracle raise ORA-04091?

Oracle prevents a row-level trigger from seeing an inconsistent intermediate state of the table.

Consider:

UPDATE employees
SET salary = salary * 1.10;

Suppose 1,000 rows are being updated:

Rows 1–99       → already processed
Row 100          → currently processing
Rows 101–1000    → not processed yet

If the trigger queries the table, what should Oracle return?

  • Old data?
  • New data?
  • Partially updated data?

To avoid this ambiguity, Oracle restricts this access and raises: 

ORA-04091

4. Does the mutating-table problem occur with every trigger?

No. The classic mutating-table problem is associated with row-level triggers.

For example:

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    ...
END;
/

Potential mutating-table problem:

UPDATE employees
       ↓
FOR EACH ROW
       ↓
Query employees
       ↓
❌ ORA-04091

5. What about a statement-level trigger?

A statement-level trigger generally does not have this mutating-table problem. 

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

    DBMS_OUTPUT.PUT_LINE(
        'Employee count = ' || v_count
    );

END;
/

There is no FOR EACH ROW. Therefore, Oracle executes it once after the statement completes, when the table is no longer in the row-by-row mutating state.

6. What is the difference between row-level and statement-level behavior?

Suppose:

UPDATE employees
SET salary = salary + 1000;

affects 500 rows.

Row-level trigger:

UPDATE
   ↓
Row 1 → trigger
Row 2 → trigger
Row 3 → trigger
...
Row 500 → trigger

If each trigger execution queries employees:

❌ ORA-04091

Statement-level trigger:

UPDATE
   ↓
500 rows processed
   ↓
Statement trigger
   ↓
SELECT FROM employees
   ↓
✅ Generally allowed

7. Can a row-level trigger query another table?

Yes. The problem is generally querying the triggering table, not simply any table. 

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_dept_name VARCHAR2(100);
BEGIN

    SELECT department_name
    INTO v_dept_name
    FROM departments
    WHERE department_id = :NEW.department_id;

END;
/

If departments is not the table currently being modified, this is generally fine.

employees trigger
       ↓
query employees       ❌ Potential mutating-table error

employees trigger
       ↓
query departments     ✅ Generally okay

8. Can a row-level trigger modify another table?

Yes. For example:

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN

    INSERT INTO employee_audit
    (
        employee_id,
        old_salary,
        new_salary
    )
    VALUES
    (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary
    );

END;
/

Here:

EMPLOYEES
   ↓
Row trigger
   ↓
INSERT into EMPLOYEE_AUDIT

This is a normal and common use case.

9. Is modifying another table always safe?

Not necessarily.

You can have other problems if modifying another table causes another trigger to fire and creates a recursive or circular dependency. 

employees trigger
       ↓
UPDATE departments
       ↓
departments trigger
       ↓
UPDATE employees
       ↓
employees trigger
       ↓
...

This can lead to recursion or other errors.

The classic ORA-04091 mutating-table restriction concerns accessing the table currently being modified from its row-level trigger.

10. Can a row-level trigger query the triggering table using :NEW?

Using :NEW itself is fine.

CREATE OR REPLACE TRIGGER trg_emp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN

    IF :NEW.salary < 0 THEN

        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary cannot be negative'
        );

    END IF;

END;
/

This does not query the table. It simply uses the current row's values: 

:OLD.salary
:NEW.salary

That's perfectly normal.

11. What is a bad trigger design?

Consider:

CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

END;
/

This is the classic bad design.

Then:

INSERT INTO employees
VALUES (101, 10, 50000);

can result in:

ORA-04091

because:

INSERT employees
       ↓
Row-level trigger
       ↓
SELECT employees
       ↓
Mutating table

12. How do I fix a mutating-table error?

There are several approaches. The correct solution depends on why you need to query the table.

Common solutions are:

  1. Avoid querying the triggering table.
  2. Use :OLD and :NEW.
  3. Use a statement-level trigger.
  4. Use a compound trigger.
  5. Use a package + statement-level processing.
  6. Use a constraint instead of a trigger.
  7. Redesign the business logic.

13. Solution 1: Use :OLD and :NEW

Sometimes you don't actually need to query the table.

Bad:

SELECT salary
FROM employees
WHERE employee_id = :NEW.employee_id;

If you're already processing that row, simply use: 

:NEW.salary

For example:

CREATE OR REPLACE TRIGGER trg_salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN

    IF :NEW.salary > 100000 THEN

        DBMS_OUTPUT.PUT_LINE(
            'High salary: ' || :NEW.salary
        );

    END IF;

END;
/

This is much simpler.

14. Solution 2: Use a statement-level trigger

Suppose your requirement is: after an update, count all employees whose salary exceeds 100,000.

Don't query the table from the row trigger. Instead:

CREATE OR REPLACE TRIGGER trg_emp_check
AFTER UPDATE ON employees
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees
    WHERE salary > 100000;

    DBMS_OUTPUT.PUT_LINE(
        'High salary employees = ' || v_count
    );

END;
/

Then:

UPDATE employees
SET salary = salary + 500;

The statement completes first, then the statement-level trigger runs.

15. Solution 3: Use a compound trigger

A compound trigger is one of the best solutions when you need both:

  • Row-level information
  • Statement-level processing
CREATE OR REPLACE TRIGGER trg_emp_compound
FOR UPDATE ON employees
COMPOUND TRIGGER

    AFTER EACH ROW IS
    BEGIN

        DBMS_OUTPUT.PUT_LINE(
            'Processing employee ' || :NEW.employee_id
        );

    END AFTER EACH ROW;


    AFTER STATEMENT IS
        v_count NUMBER;
    BEGIN

        SELECT COUNT(*)
        INTO v_count
        FROM employees
        WHERE salary > 100000;

        DBMS_OUTPUT.PUT_LINE(
            'High salary employees = ' || v_count
        );

    END AFTER STATEMENT;

END;
/

The important concept is:

UPDATE employees
       ↓
AFTER EACH ROW
       ↓
Collect information
       ↓
All rows processed
       ↓
AFTER STATEMENT
       ↓
Query employees

This avoids the classic mutating-table problem.

16. Why is a compound trigger useful?

Suppose 100 rows are updated.

You need:

For each row:

collect employee_id

After the statement:

query employees
perform validation

A normal row-level trigger cannot safely query the mutating table.

A compound trigger can separate the processing:

COMPOUND TRIGGER
|
+----------------------+----------------------+
|                      |
AFTER EACH ROW         AFTER STATEMENT
|                      |
Collect data           Query table
:NEW / :OLD            Table is stable

This is one of the most important Oracle solutions to ORA-04091.

17. Example: Enforcing a department salary rule

Suppose the business rule is:

The total salary of employees in a department must not exceed a certain amount.

A naïve trigger might do this:

CREATE OR REPLACE TRIGGER trg_salary_check
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_total NUMBER;
BEGIN

    SELECT SUM(salary)
    INTO v_total
    FROM employees
    WHERE department_id = :NEW.department_id;

    IF v_total > 1000000 THEN

        RAISE_APPLICATION_ERROR(
            -20001,
            'Department salary limit exceeded'
        );

    END IF;

END;
/

This can cause:

ORA-04091

because:

employees
    ↓
row-level trigger
    ↓
SELECT SUM(salary)
FROM employees
    ↓
❌ Mutating table

18. How would a compound trigger solve that?

Conceptually:

CREATE OR REPLACE TRIGGER trg_salary_check
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER

    AFTER STATEMENT IS
        v_total NUMBER;
    BEGIN

        SELECT SUM(salary)
        INTO v_total
        FROM employees;

        IF v_total > 1000000 THEN

            RAISE_APPLICATION_ERROR(
                -20001,
                'Salary limit exceeded'
            );

        END IF;

    END AFTER STATEMENT;

END;
/

The important idea is not the exact business rule, but the timing:

Rows are updated
       ↓
Statement completes
       ↓
AFTER STATEMENT
       ↓
Query employees
       ↓
Validate final state

19. Can a BEFORE EACH ROW trigger query the table?

Generally, no if it is the same table.

CREATE OR REPLACE TRIGGER trg_emp
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

END;
/

This is still a row-level trigger.

The fact that it is BEFORE rather than AFTER doesn't solve the mutating-table problem.

20. Can an AFTER EACH ROW trigger query the table?

Generally, no.

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

END;
/

Still:

FOR EACH ROW
       ↓
same table query
       ↓
ORA-04091

The problem is row-level timing, not simply BEFORE versus AFTER.

21. Does an AFTER STATEMENT trigger have the mutating-table problem?

Normally, no.

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

END;
/

This executes at statement level. Therefore, the table is no longer in the row-by-row mutating state.

22. Can a DELETE trigger cause ORA-04091?

Yes.

CREATE OR REPLACE TRIGGER trg_emp_delete
AFTER DELETE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

END;
/

Then:

DELETE FROM employees
WHERE department_id = 10;

can result in:

ORA-04091: table EMPLOYEES is mutating

The same principle applies to:

INSERT
UPDATE
DELETE

23. Can an INSERT trigger cause ORA-04091?

Yes.

CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

END;
/

Then:

INSERT INTO employees
VALUES (101, 10, 50000);

can cause:

ORA-04091

24. Can a trigger call a function that queries the same table?

Yes, and this is a very common hidden cause.

Suppose:

CREATE OR REPLACE FUNCTION get_employee_count
RETURN NUMBER
IS
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

    RETURN v_count;

END;
/

Now:

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        get_employee_count()
    );

END;
/

The trigger itself doesn't contain:

SELECT ... FROM employees

but the function does.

The execution path is:

UPDATE employees
       ↓
row trigger
       ↓
get_employee_count()
       ↓
SELECT employees
       ↓
ORA-04091

This is a very common interview scenario.

25. What does ORA-06512 mean in this situation?

You may see:

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

ORA-06512: at "SCOTT.TRG_EMP", line 8

The important error is:

ORA-04091

ORA-06512 generally tells you the PL/SQL location where the error propagated.

So when debugging:

ORA-04091
    ↓
Find trigger/function
    ↓
Find SELECT/operation against triggering table

26. How do I troubleshoot ORA-04091?

Step 1: Identify the table

Look at:

ORA-04091: table EMPLOYEES is mutating

So:

EMPLOYEES

is the triggering table.

Step 2: Find the trigger

ORA-06512: at "SCOTT.TRG_EMP", line 10

Step 3: Inspect the trigger

SELECT trigger_name,
       trigger_type,
       triggering_event,
       status
FROM user_triggers
WHERE trigger_name = 'TRG_EMP';

Step 4: Look for:

SELECT ...
FROM employees

or a function/procedure that eventually queries employees.

Step 5: Decide whether you actually need that query.

Often the solution is:

:NEW / :OLD

or

AFTER STATEMENT

or

COMPOUND TRIGGER

27. Can we use a package to solve a mutating-table problem?

Yes.

Historically, a common workaround was:

Row-level trigger
       ↓
Store information in package variables
       ↓
Statement-level trigger
       ↓
Query table

However, compound triggers are generally cleaner for this type of requirement.

Instead of managing package state manually:

Package variables
+
Multiple triggers

you can often use:

Compound trigger
with shared state across the timing sections.

28. Can constraints be used instead of a trigger?

Sometimes, and this is often preferable.

If the rule is a simple data integrity rule, consider:

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

For example, instead of:

CREATE OR REPLACE TRIGGER trg_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN

    IF :NEW.salary < 0 THEN

        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary cannot be negative'
        );

    END IF;

END;
/

You can use:

ALTER TABLE employees
ADD CONSTRAINT chk_salary
CHECK (salary >= 0);

This is simpler and avoids unnecessary trigger logic.

29. Is a mutating-table error the same as a recursive trigger error?

No. They are different concepts.

Mutating table:

Row trigger
    ↓
Access triggering table
    ↓
ORA-04091

Recursive/circular trigger behavior:

Trigger A
    ↓
modifies table B
    ↓
Trigger B
    ↓
modifies table A
    ↓
Trigger A
    ↓
...

They can sometimes occur together in badly designed systems, but they are not the same error.

30. Is ORA-04091 caused by a deadlock?

No. These are different problems.

Mutating table:

ORA-04091

means the row-level trigger is trying to access a table that is currently being modified.

Deadlock:

ORA-00060

typically involves transactions waiting on each other's locks.

Mutating table ≠ Deadlock

31. What is the most common interview example?

This is worth memorizing:

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

END;
/

Then:

UPDATE employees
SET salary = salary + 1000;

Result:

ORA-04091: table EMPLOYEES is mutating

Why?

UPDATE employees
       ↓
FOR EACH ROW trigger
       ↓
SELECT FROM employees
       ↓
❌ Mutating table

32. What is the best solution to the classic example?

If you don't need per-row processing, make it a statement-level trigger: 

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
DECLARE
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM employees;

    DBMS_OUTPUT.PUT_LINE(
        'Employee count = ' || v_count
    );

END;
/

Now:

UPDATE employees
SET salary = salary + 1000;

runs the trigger once after the statement.

33. What if I need both row-level and statement-level logic?

Use a compound trigger.

CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER

    AFTER EACH ROW IS
    BEGIN

        DBMS_OUTPUT.PUT_LINE(
            'Employee changed: ' ||
            :NEW.employee_id
        );

    END AFTER EACH ROW;


    AFTER STATEMENT IS
        v_count NUMBER;
    BEGIN

        SELECT COUNT(*)
        INTO v_count
        FROM employees;

        DBMS_OUTPUT.PUT_LINE(
            'Total employees = ' || v_count
        );

    END AFTER STATEMENT;

END;
/

This gives you:

Per-row information
+
Statement-level table access

without querying the mutating table during
the row-level phase.

34. What is the difference between :NEW/:OLD and querying the table?

Suppose you're updating:

UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;

Inside a row trigger, you already have:

:OLD.salary
:NEW.salary

So don't do this unnecessarily:

SELECT salary
FROM employees
WHERE employee_id = :NEW.employee_id;

Instead:

DBMS_OUTPUT.PUT_LINE(
    'Old salary = ' || :OLD.salary
);

DBMS_OUTPUT.PUT_LINE(
    'New salary = ' || :NEW.salary
);

Think:

Current row data
       ↓
:OLD / :NEW

Table-wide information
       ↓
Statement-level processing

35. Mutating Table Cheat Sheet

Item Answer
ORA-04091 Table is mutating
Main cause Row-level trigger accesses the triggering table
Common operations INSERT / UPDATE / DELETE
:OLD / :NEW ✅ Available in row trigger
SELECT same table ❌ Can cause ORA-04091
Query other table ✅ Generally okay
Statement trigger ✅ Generally okay
Compound trigger ✅ Common solution
Package workaround ✅ Possible
Constraints ✅ Prefer when appropriate

The Golden Rule

Don't query the table that fired a row-level trigger.

Instead:

Need current row?
       ↓
Use :OLD / :NEW


Need final table state?
       ↓
Use AFTER STATEMENT


Need both?
       ↓
Use a COMPOUND TRIGGER


Can the rule be a constraint?
       ↓
Prefer a CONSTRAINT

36. Final Interview Summary

If an interviewer asks:

"What is a mutating table error?"

A strong answer is:

ORA-04091 occurs when a row-level trigger attempts to read from or otherwise access the table that is currently being modified. Oracle prevents this because the table is in an intermediate state while the statement is processing rows. Common solutions are to use :OLD/:NEW, move the logic to statement-level processing, use a compound trigger, or replace the trigger with an appropriate constraint.

Remember this flow:

UPDATE employees
       ↓
Row-level trigger
       ↓
SELECT FROM employees
       ↓
❌ ORA-04091

Correct approaches:

Current row data
       ↓
:OLD / :NEW


Final table state
       ↓
AFTER STATEMENT


Need both
       ↓
COMPOUND TRIGGER


Simple integrity rule
       ↓
CONSTRAINT
Oracle Interview Tip:
Remember: Row-level trigger + access to the triggering table = potential ORA-04091.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)