Oracle Mutating Table Faqs With Examples

1. What is a mutating table error?

A mutating table error occurs when a row-level trigger tries to query or modify the same table that is currently being changed by the triggering DML statement.

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;

This can produce:

ORA-04091: table EMPLOYEES is mutating

2. Why does the mutating-table error occur?

If Oracle is still processing rows for an UPDATE, a row-level trigger firing during that operation cannot safely query the same table and see a consistent state. That is why Oracle raises ORA-04091.

UPDATE EMPLOYEES
      ↓
Row 1 → Trigger
Row 2 → Trigger
Row 3 → Trigger
...
Row 100 → Trigger
      ↓
Statement completes

3. What is the classic mutating-table example?

A common example is checking a rule by querying the same table inside a row-level trigger.

CREATE OR REPLACE TRIGGER trg_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_avg_salary NUMBER;
BEGIN
    SELECT AVG(salary)
    INTO v_avg_salary
    FROM employees;

    IF :NEW.salary > v_avg_salary * 2 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary is too high'
        );
    END IF;
END;
/

Running an UPDATE on employees can result in ORA-04091 because the trigger queries the same table while it is being updated.

4. Which triggers commonly cause mutating-table errors?

The problem is specifically associated with row-level triggers that access their triggering table during the DML operation. Statement-level triggers do not have the same row-by-row mutating-table problem.

5. Does every query on the same table cause a mutating-table error?

The problematic case is a row-level trigger querying its own triggering table during the triggering DML operation.

6. Can a row-level trigger modify the same table?

Trying to modify the triggering table from its own row-level trigger can also cause problems, including mutating-table errors and recursive behavior.

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    UPDATE employees
    SET last_modified = SYSDATE
    WHERE employee_id = :NEW.employee_id;
END;
/

7. How can we avoid a mutating-table error?

Common solutions are to use a statement-level trigger, use a compound trigger, use a collection to store row information, move processing to the AFTER STATEMENT section, or redesign the business rule.

Solution 1 — Use a Statement-Level Trigger

8. How can a statement-level trigger help?

A statement-level trigger executes after the entire DML statement finishes, so the table is no longer mutating.

CREATE OR REPLACE TRIGGER trg_emp_check
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;
/

9. What is the limitation of a statement-level trigger?

A statement-level trigger does not have individual row context, so you cannot use :OLD and :NEW in the statement-level section.

Solution 2 — Compound Trigger

10. Why is a compound trigger useful for mutating-table errors?

A compound trigger allows you to capture information during row processing and access it later in AFTER STATEMENT.

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

    AFTER STATEMENT IS
        v_avg_salary NUMBER;
    BEGIN
        SELECT AVG(salary)
        INTO v_avg_salary
        FROM employees;

        DBMS_OUTPUT.PUT_LINE(
            'Average salary: ' || v_avg_salary
        );
    END AFTER STATEMENT;

END;
/

11. How can a compound trigger collect changed employee IDs?

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

```
TYPE emp_id_list IS
    TABLE OF employees.employee_id%TYPE;

g_emp_ids emp_id_list := emp_id_list();

AFTER EACH ROW IS
BEGIN
    g_emp_ids.EXTEND;
    g_emp_ids(g_emp_ids.COUNT) := :NEW.employee_id;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
    FOR i IN 1 .. g_emp_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Updated Employee: ' || g_emp_ids(i)
        );
    END LOOP;
END AFTER STATEMENT;
```

END;
/

12. How does this solve the mutating-table problem?

During AFTER EACH ROW, the trigger only collects data and does not query employees. After the update completes, AFTER STATEMENT runs and can safely query the table.

Solution 3 — Use a Collection

13. Why use a collection?

A collection allows the row-level trigger section to store information without querying the mutating table.

CREATE OR REPLACE TRIGGER trg_emp_changes
FOR UPDATE ON employees
COMPOUND TRIGGER

    TYPE emp_list IS
        TABLE OF employees.employee_id%TYPE;

    g_ids emp_list := emp_list();

    AFTER EACH ROW IS
    BEGIN
        g_ids.EXTEND;
        g_ids(g_ids.COUNT) := :NEW.employee_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        FOR i IN 1 .. g_ids.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(
                'Employee: ' || g_ids(i)
            );
        END LOOP;
    END AFTER STATEMENT;

END;
/

Solution 4 — Use an Autonomous Transaction?

No. It should not be treated as a general solution. For mutating-table problems, prefer compound triggers, statement-level processing, collections, or redesign.

14. Can a package help solve a mutating-table problem?

Historically, one solution was to store values in a package collection and process them later in a statement-level trigger. A compound trigger is usually cleaner.

15. Classic Interview Question

Question: Why does this trigger cause ORA-04091?

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_total NUMBER;
BEGIN
    SELECT SUM(salary)
    INTO v_total
    FROM employees;
END;
/

Answer: Because employees is being modified and the row-level trigger attempts to query the same table while it is mutating.

16. How would you fix that trigger?

Move the query to statement-level processing.

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
DECLARE
    v_total NUMBER;
BEGIN
    SELECT SUM(salary)
    INTO v_total
    FROM employees;

    DBMS_OUTPUT.PUT_LINE(
        'Total salary: ' || v_total
    );
END;
/

17. What if I need :NEW values as well?

Use a compound trigger.

CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER

    TYPE id_list IS
        TABLE OF employees.employee_id%TYPE;

    g_ids id_list := id_list();

    AFTER EACH ROW IS
    BEGIN
        g_ids.EXTEND;
        g_ids(g_ids.COUNT) := :NEW.employee_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_total NUMBER;
    BEGIN
        SELECT SUM(salary)
        INTO v_total
        FROM employees;

        DBMS_OUTPUT.PUT_LINE(
            'Total salary: ' || v_total
        );

        FOR i IN 1 .. g_ids.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(
                'Changed employee: ' || g_ids(i)
            );
        END LOOP;
    END AFTER STATEMENT;

END;
/

18. Is a mutating-table error a compilation error?

Usually no. The trigger can compile successfully, but the error occurs at runtime when the triggering DML statement executes.

19. What is ORA-04091?

The key Oracle error is ORA-04091: table string.string is mutating, trigger/function may not see it. It indicates that a row-level trigger or function called from it is attempting to access a table that is currently undergoing the triggering DML operation.

20. What is ORA-04088?

You may also see ORA-04088: error during execution of trigger, which commonly appears along with an underlying error such as ORA-04091.

21. Does a SELECT from another table cause a mutating-table error?

Normally no. The mutating-table issue is specifically about accessing the table currently undergoing the triggering operation.

22. Does a foreign-key relationship automatically cause a mutating-table error?

No. The issue is typically caused by a row-level trigger or function attempting to access the table that is currently being modified.

23. Can a function called by a trigger cause a mutating-table error?

Yes. If the function queries the same table that is being modified, the same mutating-table problem can occur.

Quick Interview Table

Question	Answer
What is a mutating table?	A table currently being modified by the triggering DML statement
Common error?	ORA-04091
Usually associated with?	Row-level triggers
Can the trigger query another table?	Yes
Can the trigger safely query the triggering table row-by-row?	Generally no
Can AFTER STATEMENT query the table?	Yes
Best modern solution?	Compound trigger when row + statement processing is needed
Can collections help?	Yes
Does :OLD/:NEW cause the problem?	No
Can a called function cause it?	Yes

Key Interview Point

A mutating-table error (ORA-04091) occurs when row-level trigger processing attempts to access the table that is currently being modified. The usual solutions are to move the table access to statement-level processing or use a compound trigger to collect row-level information and process it after the statement completes.