Triggers

25.1 DML Triggers

Timing: BEFORE / AFTER. Level: statement (default) vs row (FOR EACH ROW).

Pseudo-records: :NEW, :OLD (and REFERENCING to rename them). Predicates: INSERTING, UPDATING('col'), DELETING.

Firing order: BEFORE statement → BEFORE row → AFTER row → AFTER statement. FOLLOWS / PRECEDES set the order between triggers of the same type.

-- Row-level BEFORE trigger
CREATE OR REPLACE TRIGGER trg_products_bu
BEFORE UPDATE OF unit_price ON products
FOR EACH ROW
WHEN (NEW.unit_price < 0)
BEGIN
    RAISE_APPLICATION_ERROR(-20020, 'Price cannot be negative');
END;
/

-- Audit trigger
CREATE OR REPLACE TRIGGER trg_products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
DECLARE
    v_action CHAR(1);
BEGIN
    v_action := CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END;
    INSERT INTO product_audit (product_id, action, old_price, new_price, changed_by, changed_at)
    VALUES (NVL(:NEW.product_id, :OLD.product_id), v_action, :OLD.unit_price, :NEW.unit_price, USER, SYSTIMESTAMP);
END;
/

25.2 Compound Triggers (11g): Avoiding Mutating Tables

ORA-04091 (mutating table): a row-level trigger can't query or modify the table that fired it. Use compound triggers or statement-level logic.

CREATE OR REPLACE TRIGGER trg_emp_salary_cap
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
    TYPE id_tab IS TABLE OF employees.department_id%TYPE;
    g_depts id_tab := id_tab();

    AFTER EACH ROW IS
    BEGIN
        g_depts.EXTEND; g_depts(g_depts.LAST) := :NEW.department_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_total NUMBER;
    BEGIN
        FOR i IN 1 .. g_depts.COUNT LOOP
            SELECT SUM(salary) INTO v_total FROM employees WHERE department_id = g_depts(i);
            IF v_total > 1000000 THEN RAISE_APPLICATION_ERROR(-20030, 'Department budget exceeded'); END IF;
        END LOOP;
    END AFTER STATEMENT;
END;
/

25.3 INSTEAD OF Triggers

On views: CREATE TRIGGER ... INSTEAD OF INSERT ON v_emp_dept FOR EACH ROW BEGIN ... END;

25.4 System and DDL Triggers

Events: LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVERERROR, CREATE, ALTER, DROP, TRUNCATE, GRANT, DDL, AFTER CLONE, BEFORE UNPLUG.

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON SCHEMA
BEGIN
    INSERT INTO ddl_log (event, obj_type, obj_name, who, at)
    VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, ORA_LOGIN_USER, SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER trg_logon AFTER LOGON ON DATABASE
BEGIN
    DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('USERENV', 'OS_USER'));
END;
/

25.5 Trigger Management

Best practice: keep triggers minimal. Avoid business logic in triggers and avoid cascading triggers.

ALTER TRIGGER trg_products_audit DISABLE;
ALTER TABLE products ENABLE ALL TRIGGERS;
SELECT trigger_name, status, triggering_event FROM user_triggers;