Compound triggers and the mutating-table error

A row trigger that keeps each customer’s accounts at or below 50,000 rupees by summing their balances fails on the first UPDATE it meets: 10 % interest on the 3 savings accounts stops with ORA-04091, the mutating-tablemutating table: A table that a DML statement is in the middle of changing. A row-level trigger fired by that statement may neither query nor change it; trying raises ORA-04091. error, although every customer would stay under the cap. The trigger read the table its own statement was changing, which Oracle forbids.

You will see why Oracle raises the mutating-table error for that read, rewrite the rule as a compound trigger that records rows as they change and checks the finished result once, and meet the triggers that fire for schema changes and logons.

A row trigger cannot read the table its statement is changing

The cap spans rows: a CHECK constraint sees one row, but the rule is about the sum of a customer’s accounts. The first attempt is a row trigger on accounts that runs SELECT SUM(balance) for the customer of each changed row and raises an error above 50,000:

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- A cross-row rule written as a row trigger: a customer's accounts together may hold at most 50000 rupees. The
-- trigger sums the customer's balances in the very table its statement is changing, so Oracle raises ORA-04091
-- and rolls back both the trigger's work and the UPDATE. examples/model/mutating_model.py prints the totals and
-- outcomes described below.
SET SERVEROUTPUT ON

CREATE OR REPLACE TRIGGER accounts_cap_row
  AFTER INSERT OR UPDATE OF balance ON accounts
  FOR EACH ROW
DECLARE
  v_total accounts.balance%TYPE;
BEGIN
  SELECT SUM(balance) INTO v_total     -- reads the table this statement is changing
    FROM accounts
   WHERE customer_id = :NEW.customer_id;
  IF v_total > 50000 THEN
    RAISE_APPLICATION_ERROR(-20040, 'Customer ' || :NEW.customer_id || ' would hold ' || v_total);
  END IF;
END;
/

-- 10 % interest on the 3 savings accounts: every customer stays under the cap, yet the statement fails.
UPDATE accounts SET balance = balance * 1.10 WHERE kind = 'savings';
-- Expected:
-- ORA-04091: table LAB.ACCOUNTS is mutating, trigger/function may not see it
-- (followed by ORA-06512 and ORA-04088 lines that name LAB.ACCOUNTS_CAP_ROW)

SELECT account_id, balance FROM accounts ORDER BY account_id;
-- Expected: 2500, 18000, 760.25 and 41250.5: the UPDATE was rolled back along with the trigger's work

DROP TRIGGER accounts_cap_row;

"""mutating_table.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("mutating/mutating_table.sql", errors=1)

$ sqlplus -s lab@//localhost/FREEPDB1 @mutating/mutating_table.sql
Trigger created.

UPDATE accounts SET balance = balance * 1.10 WHERE kind = 'savings'
       *
ERROR at line 1:
ORA-04091: table LAB.ACCOUNTS is mutating, trigger/function may not see it
ORA-06512: at "LAB.ACCOUNTS_CAP_ROW", line 4
ORA-04088: error during execution of trigger 'LAB.ACCOUNTS_CAP_ROW'
Help: https://docs.oracle.com/error-help/db/ora-04091/



ACCOUNT_ID    BALANCE
---------- ----------
         1       2500
         2      18000
         3     760.25
         4    41250.5


Trigger dropped.

Oracle’s trigger restrictions page calls a table mutating while a DML statement is changing it, and a row-level trigger fired by that statement may neither query nor change it. The attempt raises ORA-04091, and the database rolls back both the trigger’s work and the triggering statement, so the 3 savings balances stay as they were.

The reason is the half-changed table. Step through customer 1’s sweep, one UPDATE that empties the savings account and adds 32,000 to the current account:

Read halfway, the same statement shows 18,000 in one row order and 52,500 in the other, while its result is 50,000. SQL does not fix the order in which one statement changes its rows, so a check made between two rows could pass or fail by chance. Oracle refuses the read instead.

A compound trigger records per row and checks once

A compound triggercompound trigger: One trigger with up to four timing-point sections, before and after the statement and before and after each row, that share variables living only for the triggering statement. is one trigger with up to four timing-point sections, BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW and AFTER STATEMENT, whose variables live for one triggering statement. Oracle’s DML triggers page names avoiding the mutating-table error as one of two common uses of compound triggers. The fix splits the work: the row section records which customers changed, and the statement section, which the restriction does not cover, sums those customers once the table is whole again:

The model runs the row trigger that fails, the half-changed reads of the sweep and the compound approach, which passes the interest credit (20,750.00, 836.28 and 45,375.55) and the sweep (50,000.00) and rejects the deposit that would take customer 4 to 51,250.50. Press Edit, change an amount in STATEMENTS, and run it again:

"""Why a row trigger may not read its own table mid-statement, and how a compound trigger checks once.

The rule: a customer's accounts together may hold at most 50000 rupees. mutating_table.sql enforces it
with a row trigger that sums the customer's balances, and Oracle refuses that read with ORA-04091. This
model shows why the read is refused: it applies one UPDATE row by row, in both orders a database might
use, and prints the total a row trigger would see after each row. Then it runs the compound-trigger
approach: record each touched customer per row, and sum their balances once, after the statement.
Edit STATEMENTS (other amounts, other accounts) and run it again.
"""

from decimal import Decimal, ROUND_HALF_UP

CAP = Decimal("50000.00")
ACCOUNTS = {  # account_id: (customer_id, kind, balance), the practice data
    1: (1, "savings", Decimal("2500.00")),
    2: (1, "current", Decimal("18000.00")),
    3: (2, "savings", Decimal("760.25")),
    4: (4, "savings", Decimal("41250.50")),
}


def interest(balance):
    return (balance * Decimal("1.10")).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)


# (what the UPDATE does, {account_id: new balance from old balance}) in the order the rows are listed
STATEMENTS = [
    ("10 % interest on the 3 savings accounts", {1: interest, 3: interest, 4: interest}),
    ("customer 1: sweep savings into current, plus a 29500 salary",
     {1: lambda b: Decimal("0.00"), 2: lambda b: b + Decimal("32000.00")}),
    ("deposit 10000 into account 4", {4: lambda b: b + Decimal("10000.00")}),
]


def total(table, customer):
    return sum((bal for cust, _, bal in table.values() if cust == customer), Decimal("0.00"))


print("1. A row trigger that sums the customer's balances")
for text, changes in STATEMENTS:
    print(f"  {text}: ORA-04091 at the first row; the UPDATE is rolled back")

print("2. If a row trigger could read the half-changed table: the sweep, in two row orders")
text, changes = STATEMENTS[1]
for order in ([1, 2], [2, 1]):
    table = dict(ACCOUNTS)
    seen = []
    for account in order:
        cust, kind, bal = table[account]
        table[account] = (cust, kind, changes[account](bal))
        seen.append(total(table, cust))
    verdicts = ["over the cap" if t > CAP else "within" for t in seen]
    steps = ", then ".join(f"{t} ({v})" for t, v in zip(seen, verdicts))
    print(f"  rows {order[0]} then {order[1]}: the trigger sees {steps}")

print("3. A compound trigger: record customers in AFTER EACH ROW, sum them in AFTER STATEMENT")
for text, changes in STATEMENTS:
    table = dict(ACCOUNTS)
    touched = []
    for account, change in changes.items():  # AFTER EACH ROW
        cust, kind, bal = table[account]
        table[account] = (cust, kind, change(bal))
        if cust not in touched:
            touched.append(cust)
    totals = {c: total(table, c) for c in touched}  # AFTER STATEMENT: the table is whole again
    over = [c for c, t in totals.items() if t > CAP]
    shown = ", ".join(f"customer {c} {t}" for c, t in totals.items())
    outcome = f"ORA-20040 for customer {over[0]}; the UPDATE is rolled back" if over else "passes"
    print(f"  {text}: {shown} -> {outcome}")

1. A row trigger that sums the customer's balances
  10 % interest on the 3 savings accounts: ORA-04091 at the first row; the UPDATE is rolled back
  customer 1: sweep savings into current, plus a 29500 salary: ORA-04091 at the first row; the UPDATE is rolled back
  deposit 10000 into account 4: ORA-04091 at the first row; the UPDATE is rolled back
2. If a row trigger could read the half-changed table: the sweep, in two row orders
  rows 1 then 2: the trigger sees 18000.00 (within), then 50000.00 (within)
  rows 2 then 1: the trigger sees 52500.00 (over the cap), then 50000.00 (within)
3. A compound trigger: record customers in AFTER EACH ROW, sum them in AFTER STATEMENT
  10 % interest on the 3 savings accounts: customer 1 20750.00, customer 2 836.28, customer 4 45375.55 -> passes
  customer 1: sweep savings into current, plus a 29500 salary: customer 1 50000.00 -> passes
  deposit 10000 into account 4: customer 4 51250.50 -> ORA-20040 for customer 4; the UPDATE is rolled back

Four related tools are worth knowing by name. A plain AFTER statement trigger may also read the finished table, but unlike compound triggers it cannot tell which customers changed, so it must check them all. A temporary table, written by a row trigger and read by a statement trigger, is the older documented route to the same result. System triggerssystem trigger: A trigger created on a schema or on the database that fires for a DDL statement, such as CREATE or DROP, or for a database event, such as a logon, rather than for changed rows. fire for DDL in a schema or for database events such as a logon, not for changed rows. An autonomous transactionautonomous transaction: A transaction of its own that a routine marked PRAGMA AUTONOMOUS_TRANSACTION runs while the main transaction waits; its COMMIT keeps its changes whatever the caller does afterwards. More in “Error logging, backtraces and autonomous logs” silences ORA-04091 in a row trigger, but its query reads only committed data, so the check works on the wrong totals.

The 50,000-rupee cap as a compound trigger, tested on six statements Pro: opens with a Pro, Premium or Ultimate pass

DDL and logon triggers, and why an autonomous transaction only hides ORA-04091 Pro: opens with a Pro, Premium or Ultimate pass

Practise: predict what each trigger does

Each question shows a trigger and a statement. Decide what the trigger may read and when, then read why each answer is right or wrong:

Interview questions

Warm-up (2–5 years): what is a mutating table, and which triggers does the rule apply to?

A table that a DML statement is in the middle of changing. The rule binds the row-level triggers fired by that statement: they may not query or change the table, and trying raises ORA-04091, which rolls back the trigger and the statement. Statement-level triggers and a compound trigger’s statement sections may read it, because no row is halfway through a change then.

Three longer questions follow, on a cap that fails in its first test, a fix that made the error disappear, and a logon trigger to add safely; work out your answer before you open each first step:

Key takeaways

• A row trigger may not query or change the table its statement is changing, and trying raises ORA-04091 and rolls the statement back.
• Read halfway, one statement shows totals that depend on row order, such as 18,000 or 52,500 for a result of 50,000.
• A compound trigger records what changed in its row sections and checks the finished table once, in AFTER STATEMENT.
• A compound trigger’s variables live for one triggering statement, so each statement starts with an empty record.
• System triggers fire for DDL and database events, and an autonomous transaction silences ORA-04091 without making the check right.