1. Statement vs. Row-Level Triggers

A DML trigger executes automatically when an INSERT, UPDATE, or DELETE occurs on a table.

Statement-Level Triggers (The Default)

A statement-level trigger fires exactly once per SQL statement, regardless of how many rows are affected. Even if a massive UPDATE affects 10 million rows, the trigger fires once.

• Use case: Security checks (e.g., preventing DML outside business hours), logging global batch events.
• Limitation: Cannot access the specific data being changed (no :NEW or :OLD variables).

Row-Level Triggers (FOR EACH ROW)

A row-level trigger fires once for every single row affected by the statement.

CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    -- Log the change to an audit table
    INSERT INTO salary_audit (emp_id, old_sal, new_sal, changed_by, change_dt)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, USER, SYSDATE);
END;
/

• Use case: Data validation, generating primary keys via sequences (pre-12c), audit logging.
• Cost: If you update a million rows, this trigger fires a million times. Context switching can severely degrade performance.

2. The Mutating Table Error (ORA-04091)

This is the most famous error in PL/SQL programming.

Why does Oracle prevent this? Imagine you write a BEFORE UPDATE FOR EACH ROW trigger on the EMPLOYEES table. Inside that trigger, you run SELECT MAX(salary) FROM employees. Since the UPDATE is currently mid-flight (maybe it's updating 50 rows, and it's currently on row 3), the database is in an inconsistent state. The MAX(salary) is ambiguous. To protect data integrity, Oracle hard-stops you.

3. The Modern Fix: Compound Triggers

Before Oracle 11g, fixing a mutating table required creating a package to hold PL/SQL collections, a statement-level BEFORE trigger to clear the collection, a row-level trigger to capture :NEW values into the collection, and a statement-level AFTER trigger to process the collection. It was a nightmare.

Compound Triggers (Oracle 11g+) consolidate all these timing points into a single, elegant object that shares a global state.

CREATE OR REPLACE TRIGGER trg_emp_compound
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
    
    -- 1. Global Declaration Section
    -- State variables here persist across all timing points for the statement
    TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
    v_changed_emps t_emp_ids := t_emp_ids();

    -- 2. Before Statement
    BEFORE STATEMENT IS BEGIN
        -- Setup code
        NULL;
    END BEFORE STATEMENT;

    -- 3. Before Each Row
    BEFORE EACH ROW IS BEGIN
        -- Capture the changing IDs into our state variable
        -- We DO NOT query the table here!
        v_changed_emps.EXTEND;
        v_changed_emps(v_changed_emps.LAST) := :NEW.employee_id;
    END BEFORE EACH ROW;

    -- 4. After Statement
    AFTER STATEMENT IS BEGIN
        -- The table is no longer mutating here!
        -- We can now safely query the table using the captured IDs
        FOR i IN 1 .. v_changed_emps.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Processed: ' || v_changed_emps(i));
            -- Perform complex validation requiring full table access
        END LOOP;
    END AFTER STATEMENT;

END trg_emp_compound;
/

By capturing the row data during the EACH ROW phase and deferring the complex queries until the AFTER STATEMENT phase (when the table is no longer mutating), the compound trigger elegantly solves ORA-04091.