Triggers (DML, Compound, System)

A trigger is a named PL/SQL block that Oracle executes automatically in response to a specific event — DML on a table, DDL commands, or database/schema events. Triggers enforce business rules, maintain audit trails, and automate cascading actions.

DML Triggers

The most common type: fires on INSERT, UPDATE, or DELETE on a table.

Syntax

CREATE OR REPLACE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE [OF col,...] | DELETE }
[OR INSERT | OR UPDATE | OR DELETE ...]
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
    -- trigger body
END trigger_name;
/

:NEW and :OLD

Row-level triggers have access to pseudo-records for the affected row:

Pseudo-record	INSERT	UPDATE	DELETE
`:NEW`	New row being inserted	Row after update	NULL
`:OLD`	NULL	Row before update	Row being deleted

Example 1 — Auto-Stamp Updated_At

-- Add the column first (if not present)
-- ALTER TABLE employees ADD updated_at DATE;

CREATE OR REPLACE TRIGGER trg_emp_updated_at
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END trg_emp_updated_at;
/

-- Test
UPDATE employees SET salary = salary + 100 WHERE employee_id = 100;
SELECT employee_id, salary, updated_at FROM employees WHERE employee_id = 100;

Example 2 — Audit Log Trigger

CREATE TABLE emp_audit (
    audit_id     NUMBER GENERATED ALWAYS AS IDENTITY,
    operation    CHAR(1),          -- I, U, D
    changed_at   DATE DEFAULT SYSDATE,
    changed_by   VARCHAR2(50) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),
    emp_id       NUMBER,
    old_salary   NUMBER,
    new_salary   NUMBER
);

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO emp_audit (operation, emp_id, old_salary, new_salary)
    VALUES (
        CASE
            WHEN INSERTING THEN 'I'
            WHEN UPDATING  THEN 'U'
            ELSE                'D'
        END,
        NVL(:NEW.employee_id, :OLD.employee_id),
        :OLD.salary,
        :NEW.salary
    );
END trg_emp_audit;
/

INSERTING / UPDATING / DELETING Predicates

Use these Boolean predicates inside a combined trigger to determine which operation fired it:

CREATE OR REPLACE TRIGGER trg_emp_salary_check
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > 0)   -- WHEN clause: trigger body skipped if FALSE
BEGIN
    IF INSERTING THEN
        IF :NEW.salary < 2000 THEN
            RAISE_APPLICATION_ERROR(-20101, 'Starting salary must be >= $2,000');
        END IF;
    ELSIF UPDATING('SALARY') THEN
        IF :NEW.salary > :OLD.salary * 1.25 THEN
            RAISE_APPLICATION_ERROR(-20102,
                'Salary increase > 25% requires VP approval');
        END IF;
    END IF;
END trg_emp_salary_check;
/

ℹ

In the WHEN clause, do not use the colon prefix: write WHEN (NEW.salary > 0), not WHEN (:NEW.salary > 0). Inside the trigger body, use the colon: :NEW.salary.

Mutating Table Error (ORA-04091)

A row-level trigger on table T cannot query or modify T — T is "mutating" during the DML:

-- THIS FAILS with ORA-04091:
CREATE OR REPLACE TRIGGER trg_salary_cap
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    DECLARE v_avg NUMBER;
    BEGIN
        -- Cannot query EMPLOYEES from a row-level trigger on EMPLOYEES
        SELECT AVG(salary) INTO v_avg FROM employees;  -- ORA-04091!
        ...
    END;
END;
/

Solution: Compound Trigger (see below).

Compound Triggers (Oracle 11g+)

A compound trigger has up to four timing points in one body, sharing state between them. This solves the mutating-table problem:

CREATE OR REPLACE TRIGGER trg_salary_cap_compound
FOR INSERT OR UPDATE OF salary ON employees
COMPOUND TRIGGER

    -- Package-like declaration area (shared across all timing points)
    TYPE t_salary_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    g_new_salaries  t_salary_list;
    g_count         PLS_INTEGER := 0;
    g_avg_salary    NUMBER;

    -- Fires once before the entire DML statement
    BEFORE STATEMENT IS
    BEGIN
        SELECT AVG(salary)
        INTO   g_avg_salary
        FROM   employees;  -- Safe here — statement hasn't started modifying rows
    END BEFORE STATEMENT;

    -- Fires once per row being changed
    BEFORE EACH ROW IS
    BEGIN
        -- Validate raise against pre-statement average
        IF :NEW.salary > g_avg_salary * 3 THEN
            RAISE_APPLICATION_ERROR(-20201,
                'Salary $' || :NEW.salary ||
                ' is more than 3x company average ($' ||
                ROUND(g_avg_salary) || ')');
        END IF;

        g_count := g_count + 1;
        g_new_salaries(g_count) := :NEW.salary;
    END BEFORE EACH ROW;

    -- Fires once after the entire DML statement
    AFTER STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(
            'Processed ' || g_count || ' salary changes. ' ||
            'Pre-statement average: $' || ROUND(g_avg_salary)
        );
    END AFTER STATEMENT;

END trg_salary_cap_compound;
/

Four Timing Points

Timing point	When it fires
`BEFORE STATEMENT`	Once before any rows are touched
`BEFORE EACH ROW`	Once per row, before the change
`AFTER EACH ROW`	Once per row, after the change
`AFTER STATEMENT`	Once after all rows are processed

INSTEAD OF Triggers (on Views)

Views are generally not directly updatable when they involve joins or aggregations. An INSTEAD OF trigger intercepts the DML and translates it to the base tables:

CREATE OR REPLACE VIEW emp_dept_view AS
    SELECT e.employee_id, e.first_name, e.last_name,
           e.salary, d.department_name
    FROM   employees e
    JOIN   departments d ON d.department_id = e.department_id;

CREATE OR REPLACE TRIGGER trg_emp_dept_view_upd
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
    -- Translate UPDATE on the view to the underlying table
    UPDATE employees
    SET    first_name = :NEW.first_name,
           last_name  = :NEW.last_name,
           salary     = :NEW.salary
    WHERE  employee_id = :OLD.employee_id;
END trg_emp_dept_view_upd;
/

-- Now this works even though emp_dept_view is a join view:
UPDATE emp_dept_view SET salary = 25000 WHERE employee_id = 100;

System/Schema Triggers

Fire on database events — useful for auditing DDL and connection events:

-- Audit every DDL operation in the schema
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON SCHEMA
BEGIN
    INSERT INTO ddl_audit_log (
        event_type, object_type, object_name, occurred_at, db_user
    ) VALUES (
        ORA_SYSEVENT,           -- e.g. 'CREATE', 'DROP', 'ALTER'
        ORA_DICT_OBJ_TYPE,      -- e.g. 'TABLE', 'INDEX'
        ORA_DICT_OBJ_NAME,      -- e.g. 'EMPLOYEES'
        SYSDATE,
        SYS_CONTEXT('USERENV', 'SESSION_USER')
    );
END trg_ddl_audit;
/

-- Fire whenever a user logs on
CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON SCHEMA
BEGIN
    INSERT INTO logon_log (username, logon_time, ip_address)
    VALUES (
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYSDATE,
        SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    );
END trg_logon_audit;
/

Managing Triggers

-- Disable a trigger
ALTER TRIGGER trg_emp_audit DISABLE;

-- Enable a trigger
ALTER TRIGGER trg_emp_audit ENABLE;

-- Disable ALL triggers on a table
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- Drop a trigger
DROP TRIGGER trg_emp_audit;

-- List triggers on a table
SELECT trigger_name, trigger_type, triggering_event, status
FROM   user_triggers
WHERE  table_name = 'EMPLOYEES'
ORDER BY trigger_name;

Summary

DML triggers fire BEFORE or AFTER INSERT, UPDATE, or DELETE.
FOR EACH ROW makes it a row-level trigger with access to :NEW and :OLD.
INSERTING, UPDATING, DELETING predicates distinguish operations inside a combined trigger.
Mutating table error (ORA-04091): a row-level trigger cannot query/modify the trigger table.
Compound triggers share state across timing points and solve the mutating-table problem.
INSTEAD OF triggers enable DML on non-updatable views.
System triggers (AFTER LOGON, AFTER DDL) respond to database-level events.
Disable triggers with ALTER TRIGGER ... DISABLE; drop with DROP TRIGGER.