Row / Statement / Compound Triggers, Mutating Tables, Ordering, and Audit Patterns

Learning outcomes

ServiceHub has three applications updating work orders. A team wants one invariant enforced regardless of caller and adds a row trigger that queries the same table to calculate an average. The first multirow update fails with ORA-04091. Another team uses two AFTER-row triggers whose relative order is assumed but never declared. Triggers are powerful precisely because they are implicit: that also makes them a high-governance feature.

1. Statement and row timing answer different questions

A statement trigger fires once for the triggering DML statement. A row trigger fires once for every affected row. For a table, normal DML timing proceeds through BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT. Transition values :OLD and :NEW exist at row timing points; statement sections do not represent one particular changed row.

CREATE OR REPLACE TRIGGER sh12_case_audit_trgAFTER INSERT OR UPDATE OF amount,status_code OR DELETEON servicehub_trigger_caseFOR EACH ROWBEGIN  INSERT INTO servicehub_trigger_audit(    audit_id,event_ts,event_type,case_id,old_status,new_status,    old_amount,new_amount,db_user  ) VALUES (    servicehub_trigger_audit_seq.NEXTVAL,    SYSTIMESTAMP,    CASE WHEN INSERTING THEN 'INSERT'         WHEN UPDATING THEN 'UPDATE'         ELSE 'DELETE' END,    COALESCE(:NEW.case_id,:OLD.case_id),    :OLD.status_code,:NEW.status_code,    :OLD.amount,:NEW.amount,    SYS_CONTEXT('USERENV','SESSION_USER')  );END;/

The audit insert participates in the same transaction as the triggering DML. If the caller rolls back, this audit row rolls back too. That is often the correct business-event semantics; it is not a tamper-resistant security audit trail.

2. The mutating-table restriction protects statement consistency

While a row-level trigger is processing a DML statement, the triggering table is mutating. Querying or modifying that same table from a simple row trigger can observe a logically incomplete statement state, so Oracle rejects the access with ORA-04091 and rolls back the trigger/triggering statement.

CREATE OR REPLACE TRIGGER sh12_bad_average_trgBEFORE UPDATE OF amount ON servicehub_trigger_caseFOR EACH ROWDECLARE  l_avg NUMBER;BEGIN  SELECT AVG(amount)  INTO l_avg  FROM servicehub_trigger_case;  IF :NEW.amount > l_avg * 2 THEN    RAISE_APPLICATION_ERROR(-20120,'amount exceeds statement baseline');  END IF;END;/UPDATE servicehub_trigger_caseSET amount = amount + 10WHERE status_code = 'OPEN';-- ORA-04091: table ... is mutating, trigger/function may not see it

The correct diagnosis is not “SELECT is forbidden in triggers.” The restriction is specifically about a row-level simple DML trigger reading/modifying its mutating table.

3. Compound triggers provide statement-lifetime shared state

A compound DML trigger can contain multiple timing-point sections and one declarative area shared for the duration of the triggering statement. Oracle explicitly documents avoiding mutating-table errors and batching secondary-table work as common compound-trigger uses.

DROP TRIGGER sh12_bad_average_trg;CREATE OR REPLACE TRIGGER sh12_case_guard_ctFOR UPDATE OF amount ON servicehub_trigger_caseCOMPOUND TRIGGER  g_avg_amount NUMBER;  BEFORE STATEMENT IS  BEGIN    SELECT AVG(amount)    INTO g_avg_amount    FROM servicehub_trigger_case;  END BEFORE STATEMENT;  BEFORE EACH ROW IS  BEGIN    IF g_avg_amount IS NOT NULL       AND :NEW.amount > g_avg_amount * 2 THEN      RAISE_APPLICATION_ERROR(-20120,        'amount exceeds pre-statement average threshold');    END IF;  END BEFORE EACH ROW;END sh12_case_guard_ct;/

This rule intentionally compares each new amount with the table average before the statement begins. If the business rule requires a post-statement aggregate instead, collect identifiers/values during row sections and query/validate in AFTER STATEMENT. Define that temporal rule explicitly rather than accidentally changing it while fixing ORA-04091.

4. Ordering is guaranteed only when you make it part of the design

Different timing points have a documented sequence. For two or more triggers at the same timing point, use FOLLOWS to declare an ordering dependency when it is unavoidable. Oracle documents PRECEDES for reverse crossedition trigger scenarios; it is not a general replacement for FOLLOWS. When only some same-point triggers are related with FOLLOWS, only that related order is guaranteed.

CREATE OR REPLACE TRIGGER sh12_case_metrics_trgAFTER UPDATE ON servicehub_trigger_caseFOR EACH ROWFOLLOWS sh12_case_audit_trgBEGIN  NULL; -- metric/event hook after the audit trigger for this rowEND;/

Do not depend on the order in which a DML statement happens to visit rows. Oracle can choose different access paths, and trigger design guidelines explicitly warn against row-order-dependent global state.

5. Recursion, cascading, and transaction control need boundaries

A trigger can execute DML that fires another trigger; Oracle calls this cascading. Current documentation allows up to 32 simultaneously cascading triggers. Hidden recursion is therefore an operational risk. Keep trigger bodies small, move larger logic into a named package, and document every table/event the trigger can touch.

Ordinary triggers cannot commit or roll back the caller's transaction, nor call a nonautonomous routine that does transaction control. Autonomous triggers exist, but making audit/business side effects independently durable can violate caller expectations and can introduce deadlocks. Use autonomy only for a deliberately independent event with explicit failure policy.

6. Prefer native database features over trigger replicas

Use NOT NULL, CHECK, primary/unique keys, foreign keys, defaults, generated columns, and other declarative features before building equivalent trigger logic. For security auditing, evaluate Oracle's auditing mechanisms rather than assuming a user-owned audit table is tamper-resistant. Triggers are strongest when the event must be enforced at the database boundary and no simpler declarative feature expresses it.

7. Reproducible lab: fail, repair, verify rollback semantics

BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_trigger_audit PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_trigger_case PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE servicehub_trigger_audit_seq';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -2289 THEN RAISE; END IF; END;/CREATE TABLE servicehub_trigger_case(  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL);CREATE TABLE servicehub_trigger_audit(  audit_id NUMBER PRIMARY KEY,  event_ts TIMESTAMP WITH TIME ZONE NOT NULL,  event_type VARCHAR2(10) NOT NULL,  case_id NUMBER,  old_status VARCHAR2(12), new_status VARCHAR2(12),  old_amount NUMBER(10,2), new_amount NUMBER(10,2),  db_user VARCHAR2(128));CREATE SEQUENCE servicehub_trigger_audit_seq;INSERT INTO servicehub_trigger_case VALUES(1,'OPEN',100);INSERT INTO servicehub_trigger_case VALUES(2,'OPEN',120);INSERT INTO servicehub_trigger_case VALUES(3,'CLOSED',80);COMMIT;

SAVEPOINT before_trigger_test;UPDATE servicehub_trigger_caseSET status_code='HOLD'WHERE case_id=1;SELECT COUNT(*) AS audit_rows_before_rollbackFROM servicehub_trigger_audit;ROLLBACK TO before_trigger_test;SELECT COUNT(*) AS audit_rows_after_rollbackFROM servicehub_trigger_audit;-- Audit row from the rolled-back UPDATE is gone as well.

SELECT trigger_name,trigger_type,triggering_event,statusFROM user_triggersWHERE table_name='SERVICEHUB_TRIGGER_CASE'ORDER BY trigger_name;BEGIN EXECUTE IMMEDIATE 'DROP TRIGGER sh12_case_metrics_trg'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP TRIGGER sh12_case_guard_ct'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP TRIGGER sh12_case_audit_trg'; EXCEPTION WHEN OTHERS THEN NULL; END;/DROP TABLE servicehub_trigger_audit PURGE;DROP TABLE servicehub_trigger_case PURGE;DROP SEQUENCE servicehub_trigger_audit_seq;

8. Production judgment

Use triggers when database-owned event behavior must occur regardless of caller and a declarative feature cannot express it. Keep timing and ordering explicit, make audit durability semantics clear, test multirow statements, and treat recursion/cascade graphs as dependencies. Avoid hidden autonomous commits and row-order assumptions.

No option, pack, restart, or COMPATIBLE change is required. Lesson 2 moves to runtime-generated SQL, where hidden text construction creates a different class of correctness and security risk.

Authoritative references

• DML Triggers — compound-trigger timing, state, bulk insertion and mutating-table use
• Trigger Restrictions — mutating-table and transaction-control restrictions
• Order in Which Triggers Fire — timing sequence, FOLLOWS/PRECEDES and cascading
• Trigger Design Guidelines — avoid duplicate database features and row-order dependence
• ORA-04091 — current mutating-table error meaning and action