Oracle Mutating Table Error – Complete Interview Guide
Oracle's mutating table error is a common interview and real-time development topic related to row-level triggers. It usually occurs when a trigger tries to query or modify the same table that is currently being changed by an INSERT, UPDATE, or DELETE statement.
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 mutating2. 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 completes3. 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 once for the complete DML statement. At that point, the row-by-row processing has completed, so the table can be queried safely.
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 the AFTER STATEMENT section.
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. An autonomous transaction should not be treated as a general solution for mutating-table errors. For this problem, prefer compound triggers, statement-level processing, collections, or redesigning the business rule.
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 and easier to maintain.
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.
24. Can a DELETE statement cause a mutating-table error?
Yes. The issue is not limited to UPDATE statements. A row-level trigger fired by an INSERT, UPDATE, or DELETE can encounter the problem if it tries to access its triggering table.
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;
/25. Can an INSERT statement cause a mutating-table error?
Yes. A row-level INSERT trigger that queries the same table can also encounter a mutating-table error.
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;
/26. What is the difference between row-level and statement-level triggers?
| Feature | Row-Level Trigger | Statement-Level Trigger |
|---|---|---|
| Execution | Once for each affected row | Once for the complete statement |
| :OLD and :NEW | Available | Not available |
| Mutating-table risk | Possible | No row-by-row mutating-table problem |
| Best use | Row-specific processing | Statement-level processing |
27. Can a compound trigger contain both row-level and statement-level logic?
Yes. This is one of the major advantages of a compound trigger. It can contain sections such as BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT.
CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Statement started');
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Employee: ' || :NEW.employee_id
);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Statement completed');
END AFTER STATEMENT;
END;
/28. What is the main advantage of a compound trigger?
A compound trigger allows shared state between different timing sections of the same trigger and is particularly useful when row-level information must be collected and processed after the complete DML statement.
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 |
| Can INSERT cause it? | Yes |
| Can DELETE cause it? | Yes |
| Can a compound trigger solve it? | Yes, when processing is moved appropriately to AFTER STATEMENT |
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.
If an interviewer asks how to solve ORA-04091, a strong answer is: "Avoid querying the mutating table from the row-level trigger. Move the query to AFTER STATEMENT processing, or use a compound trigger to collect row-level information and process it after the DML statement completes."