Mutating Table Error in Oracle (ORA-04091): How to Fix It

The mutating table error in Oracle, ORA-04091: table ... is mutating, trigger/function might not see it, happens when a row-level trigger reads or changes the same table that fired it. Oracle blocks this because, while the statement is still running, the table is half changed. You fix it by moving the query out of the row-level code, usually into a statement-level trigger or the AFTER STATEMENT section of a compound trigger.

This tutorial reproduces the error on a small table, explains why Oracle raises it, and shows two fixes that work. It also covers the fix you should avoid, which is adding PRAGMA AUTONOMOUS_TRANSACTION to the trigger.

What is a mutating table in Oracle?

The Oracle documentation defines a mutating table as a table that is being modified by a DML statement (INSERT, UPDATE, DELETE), possibly through the effects of a DELETE CASCADE constraint. The mutating-table restriction stops a row-level trigger from querying or modifying that table while the statement is running.

The restriction applies only to row-level triggers, the ones with FOR EACH ROW. A statement-level trigger fires once, before or after the whole statement, so it never sees the table in a half-finished state. That difference is the basis of every fix in this article.

Why does Oracle care? Think about an UPDATE that changes 100 rows. A row trigger fires 100 times, once per row. If the trigger ran a COUNT(*) on the same table, the answer would depend on how many rows Oracle had already processed, and Oracle does not promise any processing order. The same statement could give different results on different days. Oracle refuses to run the trigger rather than give you an answer that depends on timing.

When the error happens, the trigger's changes and the triggering statement are both rolled back, and the error goes back to the application. Other work in your transaction is not affected.

Sample setup

Create a small employees table and fill it with seven rows across three departments. The business rule for this tutorial: a department may have at most 3 employees. Checking that rule means counting rows in the same table that is being changed, which is exactly what triggers the error.

CREATE TABLE employees (
  emp_id    NUMBER        PRIMARY KEY,
  emp_name  VARCHAR2(50)  NOT NULL,
  dept_id   NUMBER        NOT NULL,
  salary    NUMBER(10,2)
);

INSERT INTO employees VALUES (101, 'Asha',  10, 65000);
INSERT INTO employees VALUES (102, 'Ravi',  10, 58000);
INSERT INTO employees VALUES (103, 'Meena', 20, 72000);
INSERT INTO employees VALUES (104, 'Karan', 20, 49000);
INSERT INTO employees VALUES (105, 'Priya', 20, 81000);
INSERT INTO employees VALUES (106, 'Imran', 30, 54000);
INSERT INTO employees VALUES (107, 'Sneha', 30, 60000);
COMMIT;

SELECT dept_id, COUNT(*) AS headcount
  FROM employees
 GROUP BY dept_id
 ORDER BY dept_id;

Department 20 is full. Departments 10 and 30 each have room for one more person.

Reproduce ORA-04091 with a row-level trigger

Here is the trigger most people write first. It fires after each inserted or updated row, counts the employees in the new department and raises an error if the count goes above 3.

CREATE OR REPLACE TRIGGER trg_dept_limit
  AFTER INSERT OR UPDATE OF dept_id ON employees
  FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_count
    FROM employees
   WHERE dept_id = :NEW.dept_id;

  IF v_count > 3 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Department ' || :NEW.dept_id || ' would have ' || v_count || ' employees (max 3)');
  END IF;
END;
/

The trigger compiles without any warning. The problem only shows up at run time. Move Imran (106) into department 10, which is a perfectly valid change because department 10 has room:

UPDATE employees SET dept_id = 10 WHERE emp_id = 106;

UPDATE employees SET dept_id = 10 WHERE emp_id = 106
       *
ERROR at line 1:
ORA-04091: table SQLTIPS.EMPLOYEES is mutating, trigger/function might not see it
ORA-06512: at "SQLTIPS.TRG_DEPT_LIMIT", line 4
ORA-04088: error during execution of trigger 'SQLTIPS.TRG_DEPT_LIMIT'

Read the error stack from the top. ORA-04091 names the mutating table. ORA-06512 gives the line inside the trigger body (line 4 is the SELECT COUNT(*); trigger line numbers start at DECLARE). ORA-04088 tells you which trigger failed. Even a one-row UPDATE fails, because the row trigger queries its own table.

Imran is still in department 30, because the whole UPDATE was rolled back.

Fix 1: use a statement-level trigger

If your check does not need the values of individual rows, drop FOR EACH ROW. A statement-level trigger fires once after the whole statement has finished, and it is allowed to query the table freely.

CREATE OR REPLACE TRIGGER trg_dept_limit
  AFTER INSERT OR UPDATE OF dept_id ON employees
BEGIN
  FOR r IN (SELECT dept_id, COUNT(*) AS cnt
              FROM employees
             GROUP BY dept_id
            HAVING COUNT(*) > 3)
  LOOP
    RAISE_APPLICATION_ERROR(-20001,
      'Department ' || r.dept_id || ' would have ' || r.cnt || ' employees (max 3)');
  END LOOP;
END;
/

Test a valid change

UPDATE employees SET dept_id = 10 WHERE emp_id = 106;
COMMIT;

1 row updated.

Commit complete.

The same statement that failed before now works. When the trigger runs, the update is complete, so the count is stable and correct. Department 10 now has 3 employees.

Test a change that breaks the rule

UPDATE employees SET dept_id = 20 WHERE emp_id = 107;

ORA-20001: Department 20 would have 4 employees (max 3)
ORA-06512: at "SQLTIPS.TRG_DEPT_LIMIT", ...
ORA-04088: error during execution of trigger 'SQLTIPS.TRG_DEPT_LIMIT'

This time you get your own business error instead of ORA-04091. Raising an error in an AFTER statement trigger rolls back the triggering statement, so Sneha stays in department 30.

The trade-off: this trigger counts every department on every insert or update. On a table with a few thousand rows that does not matter. On a large table, you want to check only the departments the statement touched, and for that you need the row values. That is what a compound trigger is for.

Fix 2: use a compound trigger (Oracle 11g and later)

A compound trigger is a single trigger with up to four timing-point sections: BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW and AFTER STATEMENT. Variables declared at the top are shared by all sections and live for the duration of the triggering statement. Oracle's documentation lists the compound trigger as the main way to avoid the mutating-table error.

The pattern is simple:

• In AFTER EACH ROW, do not query the table. Just remember which department each row moved into.
• In AFTER STATEMENT, when the table is no longer mutating, count only those departments.

DROP TRIGGER trg_dept_limit;

CREATE OR REPLACE TRIGGER trg_dept_limit
  FOR INSERT OR UPDATE OF dept_id ON employees
  COMPOUND TRIGGER

  -- Set of department ids touched by this statement
  TYPE t_dept_set IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  g_depts   t_dept_set;
  v_dept    PLS_INTEGER;
  v_count   PLS_INTEGER;

  AFTER EACH ROW IS
  BEGIN
    g_depts(:NEW.dept_id) := TRUE;   -- no query here
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    v_dept := g_depts.FIRST;
    WHILE v_dept IS NOT NULL LOOP
      SELECT COUNT(*)
        INTO v_count
        FROM employees
       WHERE dept_id = v_dept;

      IF v_count > 3 THEN
        RAISE_APPLICATION_ERROR(-20001,
          'Department ' || v_dept || ' would have ' || v_count || ' employees (max 3)');
      END IF;

      v_dept := g_depts.NEXT(v_dept);
    END LOOP;
  END AFTER STATEMENT;

END trg_dept_limit;
/

Using the department id as the collection index gives you a set for free. If a statement moves 500 employees into department 30, the collection still holds one entry, and the trigger runs one COUNT(*) instead of 500. Department ids here are whole numbers, which is why PLS_INTEGER works as the index type.

You do not need to clear the collection yourself. The documentation states that the state variables of a compound trigger are initialized each time the trigger fires, even when the triggering statement is interrupted and restarted.

Test a single-row insert

INSERT INTO employees VALUES (108, 'Farah', 30, 52000);
COMMIT;

1 row created.

Commit complete.

Test a multi-row update

Now try to move all of department 10 (Asha, Ravi and Imran) into department 30, which already has Sneha and Farah:

UPDATE employees SET dept_id = 30 WHERE dept_id = 10;

ORA-20001: Department 30 would have 5 employees (max 3)
ORA-06512: at "SQLTIPS.TRG_DEPT_LIMIT", ...
ORA-04088: error during execution of trigger 'SQLTIPS.TRG_DEPT_LIMIT'

The row section ran three times and stored department 30 once. The statement section counted department 30 after all three rows had moved, found 5 and rejected the whole statement. Run the headcount query again and you will see the data unchanged:

Which fix should you use?

For new code on any supported Oracle release, the compound trigger is the standard answer. Before 11g, people used a package with a collection, a row trigger to fill it and a statement trigger to process it. The compound trigger does the same job in one object, without package state that can survive a failed statement.

Why PRAGMA AUTONOMOUS_TRANSACTION is the wrong fix

Search forums for this error and you will find advice to add PRAGMA AUTONOMOUS_TRANSACTION to the row trigger. The error disappears, but the trigger now gives wrong answers.

CREATE OR REPLACE TRIGGER trg_dept_limit
  AFTER INSERT OR UPDATE OF dept_id ON employees
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;   -- do not do this
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
    FROM employees
   WHERE dept_id = :NEW.dept_id;

  IF v_count > 3 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Department full');
  END IF;
END;
/

An autonomous transaction is a separate transaction. It shares no locks or resources with your main transaction, and it cannot see changes your session has not committed yet. So when you move Sneha (107) into department 20, the trigger counts the committed rows in department 20: 3, without Sneha. 3 is not greater than 3, so the update is allowed and department 20 ends up with 4 employees.

The rule you wrote the trigger to enforce is silently broken. The Oracle documentation also warns that if an autonomous transaction tries to access a resource held by the main transaction, a deadlock can occur. Autonomous triggers are meant for things like audit logging that must survive a rollback, not for reading the table that is changing.

Common mistakes

• Adding FOR EACH ROW by habit. Many triggers do not use :NEW or :OLD at all. If yours does not, make it a statement-level trigger and the error cannot happen.
• Assuming a one-row statement is safe. The UPDATE in this tutorial changed one row and still failed. The restriction is about the trigger querying its own table, not about row counts.
• Testing only with INSERT ... VALUES. A trigger can pass a single-row INSERT test and then fail in production on an INSERT ... SELECT, an UPDATE or a DELETE. Test every statement type the trigger fires on, including multi-row ones.
• Querying the table in BEFORE STATEMENT and trusting it later. Values read in BEFORE STATEMENT are from before the change. They are fine for a starting point, like the department averages in Oracle's own example, but not for counting the final result.
• Forgetting cascades. A DELETE on a parent table with ON DELETE CASCADE makes the child table mutating too. A row trigger on the child that queries the child table can fail even though nobody ran a DELETE on it directly.

Performance tips and best practices

• Keep row sections tiny. Store keys in a collection in AFTER EACH ROW and do the SQL work once in AFTER STATEMENT. The documentation notes that compound triggers have a performance benefit when the statement affects many rows.
• Index the column you count by. The AFTER STATEMENT query in Fix 2 filters on dept_id, so an index on employees(dept_id) keeps it fast as the table grows.
• Deduplicate keys, as the INDEX BY trick does, so a 10,000-row update does not run 10,000 counts.
• Prefer declarative constraints (CHECK, UNIQUE, FOREIGN KEY) whenever the rule fits one. They are faster than triggers and are enforced correctly under concurrency.
• Consider whether the trigger should exist at all. If all changes go through a PL/SQL package, the rule can live in that package where it is easier to read and test.

Frequently asked questions

What is the mutating table error in Oracle?

It is ORA-04091. A row-level trigger, or a function called from a DML statement, tried to query or modify the table that the current statement is changing. Oracle rolls back the statement and returns the error.

How do you avoid the mutating table error in an Oracle trigger?

Do not query the table from FOR EACH ROW code. Use a statement-level trigger if you do not need row values, or a compound trigger that collects keys per row and runs the query in its AFTER STATEMENT section. Oracle's documentation also mentions a temporary table with a row trigger and a statement trigger.

Does a statement-level trigger raise ORA-04091?

No. The documentation says the mutating-table restriction applies only to row-level simple DML triggers. A statement-level trigger, or the statement sections of a compound trigger, can query the table.

Why does my trigger work for INSERT ... VALUES but fail for INSERT ... SELECT?

For a single-row INSERT ... VALUES, Oracle does not treat the table as mutating in a BEFORE row trigger, so a query on the same table runs. An INSERT ... SELECT can insert many rows, so the restriction applies, even when the SELECT returns only one row. This behaviour is widely observed but is not described in the Trigger Restrictions page, so do not design around it.

Can I use PRAGMA AUTONOMOUS_TRANSACTION to fix ORA-04091?

It removes the error but the trigger cannot see the uncommitted changes of the statement that fired it, so any count or total it reads is wrong. Use a compound trigger instead.

Summary

• ORA-04091 means a row-level trigger touched the table its own statement is changing.
• Oracle rolls back the statement and the trigger's changes when it happens.
• If you do not need :NEW or :OLD, remove FOR EACH ROW and use a statement-level trigger.
• If you do, use a compound trigger: collect keys in AFTER EACH ROW, query in AFTER STATEMENT.
• Do not use PRAGMA AUTONOMOUS_TRANSACTION to silence the error; it reads stale data.
• Test triggers with multi-row UPDATE, DELETE and INSERT ... SELECT, not just single-row inserts.