Oracle PL/SQL – Triggers

Subject: Information Technology – Database Systems
Grade: 12th Grade – HTL Computer Science
Prerequisites: Procedures, Functions, Packages, Exceptions
Author: HTL Pinkafeld – IF/IT

1. What are Triggers?

1.1 Definition

A trigger is a stored PL/SQL block that automatically executes when a specific database event occurs. Unlike procedures, a trigger is never explicitly called.

Event (INSERT / UPDATE / DELETE)
    ↓
Oracle detects: trigger is defined
    ↓
Trigger code executes automatically
    ↓
Normal processing continues (or is aborted)

1.2 Trigger Types

1.3 Use Cases

• Auditing: Log every change to critical tables
• Validation: Enforce business rules (not expressible as constraints)
• Derivation: Auto-populate calculated fields
• Replication: Propagate changes to other tables
• Security: Prevent operations at certain times

2. Creating DML Triggers

2.1 Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER | INSTEAD OF}
    {INSERT | UPDATE [OF column] | DELETE} [OR {INSERT | UPDATE | DELETE}] ...
    ON table_name
    [FOR EACH ROW]
    [WHEN (condition)]
DECLARE
    -- Variable declarations (optional)
BEGIN
    -- Trigger code
EXCEPTION
    -- Error handling (optional)
END [trigger_name];
/

2.2 Simple AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
    INSERT INTO emp_audit (action, empno, ename, sal, ts, usr)
    VALUES ('INSERT', :NEW.empno, :NEW.ename, :NEW.sal, SYSTIMESTAMP, USER);
END trg_emp_insert;
/

-- Test:
INSERT INTO emp (empno, ename, job, deptno, sal, hiredate)
VALUES (9001, 'NEWUSER', 'CLERK', 10, 2000, SYSDATE);
COMMIT;

2.3 BEFORE INSERT – Setting a Value

-- Automatically populate primary key from sequence:
CREATE OR REPLACE TRIGGER trg_emp_pk
BEFORE INSERT ON emp
FOR EACH ROW
WHEN (NEW.empno IS NULL)
BEGIN
    :NEW.empno := emp_seq.NEXTVAL;
END;
/

-- Test (empno is set automatically):
INSERT INTO emp (ename, job, deptno, sal, hiredate)
VALUES ('AUTOUSER', 'ANALYST', 20, 3500, SYSDATE);
COMMIT;

3. Row-Level vs. Statement-Level Triggers

3.1 Statement Trigger (Default)

A statement trigger fires once per DML statement – regardless of how many rows are affected. No FOR EACH ROW:

CREATE OR REPLACE TRIGGER trg_dept_access
BEFORE INSERT OR UPDATE OR DELETE ON dept
BEGIN
    -- Deny changes outside business hours:
    IF TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) NOT BETWEEN 8 AND 17 THEN
        RAISE_APPLICATION_ERROR(
            -20100,
            'Changes to DEPT only allowed between 08:00 and 17:00!'
        );
    END IF;
    IF TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(-20101, 'No changes on weekends!');
    END IF;
END trg_dept_access;
/

3.2 Row-Level Trigger with FOR EACH ROW

A row-level trigger fires for each affected row. Updating 14 employees triggers 14 executions:

CREATE OR REPLACE TRIGGER trg_sal_check
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    IF :NEW.sal < :OLD.sal * 0.80 THEN
        RAISE_APPLICATION_ERROR(
            -20200,
            'Pay cut of more than 20% is not allowed! (' ||
            :OLD.sal || ' → ' || :NEW.sal || ')'
        );
    END IF;
END trg_sal_check;
/

3.3 Firing Order

DML statement (e.g., UPDATE on 5 rows)
    ↓
BEFORE Statement trigger  (1×)
    ↓
    For each affected row:
    ├─ BEFORE Row trigger  (5×)
    ├─ Row is changed
    └─ AFTER Row trigger   (5×)
    ↓
AFTER Statement trigger   (1×)

4. :NEW and :OLD Pseudo-Records

4.1 Availability

Important: :NEW can only be modified in BEFORE triggers!

4.2 Example: Complete Audit

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF    INSERTING THEN v_action := 'INSERT';
    ELSIF UPDATING  THEN v_action := 'UPDATE';
    ELSIF DELETING  THEN v_action := 'DELETE';
    END IF;

    INSERT INTO emp_audit
        (action, empno, old_ename, new_ename, old_sal, new_sal, old_deptno, new_deptno)
    VALUES
        (v_action,
         COALESCE(:OLD.empno, :NEW.empno),
         :OLD.ename,  :NEW.ename,
         :OLD.sal,    :NEW.sal,
         :OLD.deptno, :NEW.deptno);
END trg_emp_audit;
/

4.3 INSERTING, UPDATING, DELETING

When a trigger covers multiple events, these predicates distinguish them:

CREATE OR REPLACE TRIGGER trg_multi
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.hiredate := NVL(:NEW.hiredate, SYSDATE);
    ELSIF UPDATING('SAL') THEN
        DBMS_OUTPUT.PUT_LINE('Salary: ' || :OLD.sal || ' → ' || :NEW.sal);
    ELSIF DELETING THEN
        IF :OLD.job = 'PRESIDENT' THEN
            RAISE_APPLICATION_ERROR(-20300, 'President cannot be deleted!');
        END IF;
    END IF;
END;
/

5. WHEN Clause in Triggers

The WHEN clause filters rows for which the trigger body executes. Inside WHEN, :NEW and :OLD are written without a colon:

CREATE OR REPLACE TRIGGER trg_high_salary
AFTER INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
WHEN (NEW.sal > 5000)   -- No : before NEW/OLD in WHEN!
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Warning: ' || :NEW.ename || ' has a salary of ' || :NEW.sal
    );
END;
/

6. Compound Triggers

6.1 Motivation

The mutating table problem occurs when a row trigger accesses the same table being modified. Compound triggers solve this:

CREATE OR REPLACE TRIGGER trg_emp_compound
FOR INSERT OR UPDATE ON emp
COMPOUND TRIGGER

    TYPE t_sal_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_salaries t_sal_tab;
    v_idx      PLS_INTEGER := 0;

    BEFORE STATEMENT IS
    BEGIN
        v_idx := 0;
        v_salaries.DELETE;
    END BEFORE STATEMENT;

    AFTER EACH ROW IS
    BEGIN
        v_idx := v_idx + 1;
        v_salaries(v_idx) := :NEW.sal;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_total NUMBER := 0;
    BEGIN
        FOR i IN 1..v_idx LOOP
            v_total := v_total + v_salaries(i);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Total salary changed/inserted: ' || v_total);
    END AFTER STATEMENT;

END trg_emp_compound;
/

7. INSTEAD OF Triggers on Views

Complex views (with JOINs, GROUP BY, DISTINCT) are normally not directly updatable. An INSTEAD OF trigger intercepts DML on the view:

CREATE OR REPLACE VIEW vw_emp_dept AS
    SELECT e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
    FROM   emp e JOIN dept d ON e.deptno = d.deptno;

CREATE OR REPLACE TRIGGER trg_empdet_insert
INSTEAD OF INSERT ON vw_emp_dept
FOR EACH ROW
DECLARE
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM dept WHERE deptno = :NEW.deptno;
    IF v_cnt = 0 THEN
        RAISE_APPLICATION_ERROR(-20400, 'Department ' || :NEW.deptno || ' does not exist.');
    END IF;

    INSERT INTO emp (empno, ename, sal, deptno, job, hiredate)
    VALUES (:NEW.empno, :NEW.ename, :NEW.sal, :NEW.deptno, 'CLERK', SYSDATE);
END;
/

-- INSERT on the view now works:
INSERT INTO vw_emp_dept (empno, ename, sal, deptno)
VALUES (9100, 'VIEWUSER', 2500, 20);
COMMIT;

8. DDL and System Triggers

8.1 DDL Trigger

CREATE OR REPLACE TRIGGER trg_schema_protect
BEFORE DROP ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        INSERT INTO ddl_log (action, obj, usr, ts)
        VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_NAME, ORA_LOGIN_USER, SYSTIMESTAMP);
        COMMIT;

        IF ORA_DICT_OBJ_NAME IN ('EMP', 'DEPT', 'SALGRADE') THEN
            RAISE_APPLICATION_ERROR(-20500,
                'Protection: Table ' || ORA_DICT_OBJ_NAME || ' cannot be dropped!');
        END IF;
    END IF;
END;
/

8.2 System Trigger (LOGON/LOGOFF)

CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO session_log (usr, ts, ip_addr, event)
    VALUES (
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYSTIMESTAMP,
        SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
        'LOGIN'
    );
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN NULL;  -- Never block login due to trigger error!
END;
/

8.3 Event Functions in DDL/System Triggers

9. Managing Triggers

-- Show all own triggers:
SELECT trigger_name, trigger_type, triggering_event, table_name, status
FROM   user_triggers
ORDER  BY table_name, trigger_name;

-- Disable a single trigger (e.g., for bulk load):
ALTER TRIGGER trg_emp_audit DISABLE;

-- Enable a single trigger:
ALTER TRIGGER trg_emp_audit ENABLE;

-- Disable all triggers on a table:
ALTER TABLE emp DISABLE ALL TRIGGERS;

-- Enable all triggers on a table:
ALTER TABLE emp ENABLE ALL TRIGGERS;

-- Compile a trigger:
ALTER TRIGGER trg_emp_audit COMPILE;

-- Drop a trigger:
DROP TRIGGER trg_emp_audit;

10. Summary and Outlook

10.1 Trigger Types and Timing

10.2 Best Practices

• ✅ Keep triggers as small as possible – outsource logic to packages
• ✅ No long computations or loops inside triggers
• ✅ Never propagate errors in LOGON triggers (blocks login)
• ✅ Use WHEN clause to avoid unnecessary trigger executions
• ✅ Temporarily disable triggers for bulk operations
• ✅ Use INSTEAD OF instead of complex logic in base table triggers

10.3 Course Completion

This completes the Oracle PL/SQL course. Topics covered:

Further reading: Oracle Advanced PL/SQL – Collections, Object Types, Native Dynamic SQL (EXECUTE IMMEDIATE), Fine-Grained Auditing, Virtual Private Database