Oracle Error ORA-04091

Error code

ORA-04091

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (same wording in the error reference)

Category

PL/SQL & Triggers

Last reviewed

24 October 2026

What it means

ORA-04091 means a row-level trigger, or a PL/SQL function called from a DML statement, tried to query or change the table that the statement is in the middle of modifying.

While an INSERT, UPDATE, DELETE or MERGE is running, its table is "mutating": some rows are changed and others not yet, so a query on it would see a half-finished state. Oracle therefore stops a row-level trigger from querying or modifying that table. When it tries, Oracle raises ORA-04091, rolls back the effects of the trigger and of the triggering statement, and returns control to the caller. A table changed by a DELETE CASCADE from the statement counts as mutating too.

The same rule applies to a function called from the statement: if a function queries or modifies a table and a DML statement on that table invokes it, ORA-04091 occurs. Oracle documents one exception: a single-row INSERT ... VALUES, not inside FORALL, that calls the function in its VALUES clause. The restriction applies to row-level triggers; statement-level triggers and the statement-level sections of a compound trigger can query the table.

Under the error you will normally see ORA-06512 with the trigger's line and ORA-04088 naming the trigger. The error reference prints "may not see it"; one of Oracle's PL/SQL examples prints "might not see it", so both forms are shown above.

Common causes

• A FOR EACH ROW trigger on a table runs SELECT on the same table, for example to total, count or check other rows.
• A row trigger updates or deletes other rows of its own table, such as keeping a "current" flag on only one row.
• A function used in an UPDATE or DELETE statement (in SET or WHERE) queries the table being changed.
• A row trigger on a child table queries a parent that is being changed by the same statement through ON DELETE CASCADE, or the other way round.
• A trigger calls a procedure that, several calls deep, reads the table the trigger fires on.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT trigger_name, trigger_type, triggering_event, status
FROM all_triggers
WHERE table_owner = 'SHOP'
  AND table_name = 'ORDER_LINES'
ORDER BY trigger_name;

SELECT name, type, line, text
FROM all_source
WHERE owner = 'SHOP'
  AND name = 'ORDER_LINES_AIUD'
  AND type = 'TRIGGER'
ORDER BY line;

SELECT owner, name, type
FROM all_dependencies
WHERE referenced_owner = 'SHOP'
  AND referenced_name = 'ORDER_LINES'
  AND type IN ('TRIGGER', 'FUNCTION', 'PROCEDURE', 'PACKAGE BODY')
ORDER BY type, name;

SELECT c.owner, c.table_name, c.constraint_name, c.delete_rule,
       p.table_name AS parent_table
FROM all_constraints c
JOIN all_constraints p
  ON p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
WHERE c.constraint_type = 'R'
  AND c.delete_rule = 'CASCADE'
  AND (c.table_name = 'ORDER_LINES' OR p.table_name = 'ORDER_LINES')
  AND c.owner = 'SHOP';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

CREATE OR REPLACE TRIGGER shop.order_lines_total_ct
FOR INSERT OR UPDATE OR DELETE ON shop.order_lines
COMPOUND TRIGGER
  TYPE id_set IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
  g_orders id_set;

  AFTER EACH ROW IS
  BEGIN
    g_orders(NVL(:NEW.order_id, :OLD.order_id)) := TRUE;   -- remember the order only
  END AFTER EACH ROW;

  AFTER STATEMENT IS
    v_id PLS_INTEGER := g_orders.FIRST;
  BEGIN
    WHILE v_id IS NOT NULL LOOP
      UPDATE shop.orders o
      SET o.total = (SELECT NVL(SUM(l.amount), 0)
                     FROM shop.order_lines l WHERE l.order_id = v_id)
      WHERE o.id = v_id;
      v_id := g_orders.NEXT(v_id);
    END LOOP;
  END AFTER STATEMENT;
END order_lines_total_ct;
/

CREATE OR REPLACE TRIGGER shop.order_lines_aiud
AFTER INSERT OR UPDATE OR DELETE ON shop.order_lines
FOR EACH ROW
BEGIN
  UPDATE shop.orders
  SET total = total + NVL(:NEW.amount, 0) - NVL(:OLD.amount, 0)
  WHERE id = NVL(:NEW.order_id, :OLD.order_id);
END;
/

Examples

A row trigger that totals its own table

Not reproduced here: no Oracle Database is installed for this site. The stack layout (ORA-04091, ORA-06512 in the trigger, ORA-04088) follows the mutating-table example in Oracle's PL/SQL Language Reference; names and the line number are illustrative. The whole INSERT is rolled back.

CREATE OR REPLACE TRIGGER order_lines_aiud
AFTER INSERT OR UPDATE OR DELETE ON order_lines
FOR EACH ROW
BEGIN
  UPDATE orders
  SET total = (SELECT SUM(amount) FROM order_lines WHERE order_id = :NEW.order_id)
  WHERE id = :NEW.order_id;
END;
/

INSERT INTO order_lines (order_id, product_id, amount)
SELECT 1001, id, price FROM products WHERE category = 'BOOKS';

ORA-04091: table SHOP.ORDER_LINES is mutating, trigger/function may not see it
ORA-06512: at "SHOP.ORDER_LINES_AIUD", line 2
ORA-04088: error during execution of trigger 'SHOP.ORDER_LINES_AIUD'

A function that reads the table being updated

Not reproduced here: no Oracle Database is installed for this site. Oracle's PL/SQL reference documents that a function which queries a table raises ORA-04091 when a DML statement on that table invokes it; the message format is from the error reference and the names are illustrative. Compute the averages first (for example in a MERGE source query) instead.

CREATE OR REPLACE FUNCTION avg_price (p_category VARCHAR2) RETURN NUMBER IS
  v NUMBER;
BEGIN
  SELECT AVG(price) INTO v FROM products WHERE category = p_category;
  RETURN v;
END;
/

UPDATE products
SET price = avg_price(category)
WHERE price IS NULL;

ORA-04091: table SHOP.PRODUCTS is mutating, trigger/function may not see it
ORA-06512: at "SHOP.AVG_PRICE", line 4

How to prevent it

Keep row-level triggers to work on :NEW and :OLD and on other tables. When a rule needs to look at several rows of the same table, write it as a compound trigger from the start, or enforce it with a constraint or unique index if one can express it.

Do not call functions that query a table from DML on that same table. Review triggers whenever you add ON DELETE CASCADE, since cascades make more tables mutating.

Common questions

Learn more on CodeWithSQL

• Advanced triggers
• Triggers and views

Official documentation

• ORA-04091 (Oracle Database Error Messages)
• Trigger restrictions, mutating-table restriction (Oracle PL/SQL Language Reference 19c)
• DML triggers, compound DML triggers (Oracle PL/SQL Language Reference 19c)
• PL/SQL functions that SQL statements can invoke (Oracle PL/SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.