PL/SQL Triggers Explained — Types, Syntax, and When to Use Them

-- Basic trigger structure
CREATE OR REPLACE TRIGGER trigger_name
 {BEFORE | AFTER | INSTEAD OF}
 {INSERT | UPDATE | DELETE}
 [OF column_list]
 ON table_or_view_name
 [FOR EACH ROW]
 [WHEN (condition)]
DECLARE
 -- local variables
BEGIN
 -- PL/SQL code
EXCEPTION
 -- error handling
END trigger_name;
/

-- BEFORE INSERT row-level trigger: auto-populate audit columns
CREATE OR REPLACE TRIGGER trg_employees_bi
 BEFORE INSERT ON employees
 FOR EACH ROW
BEGIN
 IF :NEW.employee_id IS NULL THEN
 :NEW.employee_id := employees_seq.NEXTVAL;
 END IF;
 :NEW.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
 :NEW.created_date := SYSDATE;
 :NEW.updated_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
 :NEW.updated_date := SYSDATE;
END;
/

-- AFTER UPDATE row-level: write to audit table
CREATE OR REPLACE TRIGGER trg_employees_au
 AFTER UPDATE ON employees
 FOR EACH ROW
BEGIN
 INSERT INTO employees_audit (
 audit_id, employee_id, old_salary, new_salary,
 changed_by, changed_at
 ) VALUES (
 employees_audit_seq.NEXTVAL,
 :OLD.employee_id, :OLD.salary, :NEW.salary,
 SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSTIMESTAMP
 );
END;
/

-- Using :NEW and :OLD to enforce a business rule
CREATE OR REPLACE TRIGGER trg_salary_check
 BEFORE UPDATE OF salary ON employees
 FOR EACH ROW
 WHEN (NEW.salary < OLD.salary)
BEGIN
 IF :NEW.manager_approved != 'Y' THEN
 RAISE_APPLICATION_ERROR(
 -20001,
 'Salary decrease for employee ' || :OLD.employee_id ||
 ' requires manager approval. Current: ' || :OLD.salary ||
 ', Requested: ' || :NEW.salary
 );
 END IF;
END;
/

-- Normalise email on insert or update
CREATE OR REPLACE TRIGGER trg_normalise_email
 BEFORE INSERT OR UPDATE OF email ON users
 FOR EACH ROW
BEGIN
 :NEW.email := LOWER(TRIM(:NEW.email));
 :NEW.updated_at := SYSTIMESTAMP;
END;
/

-- Compound trigger solves ORA-04091
CREATE OR REPLACE TRIGGER trg_check_dept_budget
 FOR UPDATE OF salary ON employees
 COMPOUND TRIGGER

 TYPE t_id_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 v_affected_depts t_id_list;
 v_idx PLS_INTEGER := 0;

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

 AFTER STATEMENT IS
 v_total_salary NUMBER;
 v_budget NUMBER;
 BEGIN
 FOR i IN 1 .. v_affected_depts.COUNT LOOP
 SELECT SUM(salary) INTO v_total_salary
 FROM employees
 WHERE department_id = v_affected_depts(i);

 SELECT budget INTO v_budget
 FROM departments
 WHERE department_id = v_affected_depts(i);

 IF v_total_salary > v_budget THEN
 RAISE_APPLICATION_ERROR(-20002,
 'Salary total exceeds budget for dept ' || v_affected_depts(i));
 END IF;
 END LOOP;
 END AFTER STATEMENT;

END trg_check_dept_budget;
/

-- A join view that is not directly updatable
CREATE OR REPLACE VIEW v_emp_dept AS
 SELECT e.employee_id, e.first_name, e.last_name,
 e.salary, d.department_name, d.location_id
 FROM employees e
 JOIN departments d ON e.department_id = d.department_id;

-- INSTEAD OF INSERT trigger
CREATE OR REPLACE TRIGGER trg_v_emp_dept_ins
 INSTEAD OF INSERT ON v_emp_dept
 FOR EACH ROW
DECLARE
 v_dept_id departments.department_id%TYPE;
BEGIN
 SELECT department_id INTO v_dept_id
 FROM departments
 WHERE department_name = :NEW.department_name;

 INSERT INTO employees (
 employee_id, first_name, last_name, salary, department_id
 ) VALUES (
 employees_seq.NEXTVAL,
 :NEW.first_name, :NEW.last_name,
 :NEW.salary, v_dept_id
 );
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 RAISE_APPLICATION_ERROR(-20003,
 'Department not found: ' || :NEW.department_name);
END;
/

-- INSTEAD OF UPDATE trigger
CREATE OR REPLACE TRIGGER trg_v_emp_dept_upd
 INSTEAD OF UPDATE ON v_emp_dept
 FOR EACH ROW
BEGIN
 UPDATE employees
 SET first_name = :NEW.first_name,
 last_name = :NEW.last_name,
 salary = :NEW.salary
 WHERE employee_id = :OLD.employee_id;
END;
/

-- INSTEAD OF DELETE trigger
CREATE OR REPLACE TRIGGER trg_v_emp_dept_del
 INSTEAD OF DELETE ON v_emp_dept
 FOR EACH ROW
BEGIN
 DELETE FROM employees
 WHERE employee_id = :OLD.employee_id;
END;
/

-- Schema change audit table
CREATE TABLE schema_change_log (
 log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 log_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP,
 ora_login_user VARCHAR2(128),
 ora_sysevent VARCHAR2(30),
 ora_dict_obj_type VARCHAR2(30),
 ora_dict_obj_name VARCHAR2(128),
 ora_dict_obj_owner VARCHAR2(128),
 sql_text CLOB
);

-- DDL trigger: log every schema change
CREATE OR REPLACE TRIGGER trg_ddl_audit
 AFTER CREATE OR ALTER OR DROP OR GRANT OR REVOKE ON SCHEMA
BEGIN
 INSERT INTO schema_change_log (
 ora_login_user, ora_sysevent,
 ora_dict_obj_type, ora_dict_obj_name,
 ora_dict_obj_owner, sql_text
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SYSEVENT,
 ORA_DICT_OBJ_TYPE,
 ORA_DICT_OBJ_NAME,
 ORA_DICT_OBJ_OWNER,
 ORA_SQL_TXT
 );
END;
/

-- SERVERERROR trigger: capture every error in production
CREATE OR REPLACE TRIGGER trg_capture_errors
 AFTER SERVERERROR ON SCHEMA
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 v_sql CLOB;
BEGIN
 -- Get the SQL that caused the error
 BEGIN
 SELECT sql_text INTO v_sql
 FROM v$sql
 WHERE sql_id = SYS_CONTEXT('USERENV', 'CURRENT_SQL_ID')
 AND ROWNUM = 1;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN v_sql := NULL;
 END;

 INSERT INTO error_log (
 error_timestamp, ora_login_user,
 error_number, error_message,
 error_stack, sql_text
 ) VALUES (
 SYSTIMESTAMP,
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SERVER_ERROR(1),
 ORA_SERVER_ERROR_MSG(1),
 DBMS_UTILITY.FORMAT_ERROR_STACK,
 v_sql
 );
 COMMIT;
END;
/

-- LOGON trigger with safety
CREATE OR REPLACE TRIGGER trg_logon_control
 AFTER LOGON ON SCHEMA
BEGIN
 -- Allow DBA users regardless of any checks
 IF SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('SYS', 'SYSTEM', 'DBA_USER') THEN
 RETURN;
 END IF;

 -- Reject connections from untrusted IP ranges
 IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT LIKE '192.168.1.%' THEN
 RAISE_APPLICATION_ERROR(-20010,
 'Access denied from IP: ' || SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
 END IF;

 -- Set session context for auditing
 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'MODULE'));
 DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('USERENV', 'SESSION_USER'));
EXCEPTION
 WHEN OTHERS THEN
 -- Log the error (autonomous transaction to survive rollback)
 DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
 INSERT INTO logon_errors (username, ip_address, error_msg, attempted_at)
 VALUES (SYS_CONTEXT('USERENV', 'SESSION_USER'),
 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
 SQLERRM, SYSTIMESTAMP);
 COMMIT;
 END;
 RAISE;
END;
/

-- The procedure: independently testable
CREATE OR REPLACE PACKAGE io_thecodeforge_order_triggers AS
 PROCEDURE validate_order(
 p_new_order_date IN DATE,
 p_new_ship_date IN DATE,
 p_new_customer_id IN NUMBER
 );
 PROCEDURE audit_order_change(
 p_order_id IN NUMBER,
 p_old_status IN VARCHAR2,
 p_new_status IN VARCHAR2,
 p_changed_by IN VARCHAR2
 );
END io_thecodeforge_order_triggers;
/

CREATE OR REPLACE PACKAGE BODY io_thecodeforge_order_triggers AS
 PROCEDURE validate_order(
 p_new_order_date IN DATE,
 p_new_ship_date IN DATE,
 p_new_customer_id IN NUMBER
 ) IS
 v_customer_exists NUMBER;
 BEGIN
 IF p_new_ship_date < p_new_order_date THEN
 RAISE_APPLICATION_ERROR(-20040,
 'Ship date cannot precede order date');
 END IF;
 SELECT COUNT(*) INTO v_customer_exists
 FROM customers WHERE customer_id = p_new_customer_id;
 IF v_customer_exists = 0 THEN
 RAISE_APPLICATION_ERROR(-20041,
 'Customer does not exist: ' || p_new_customer_id);
 END IF;
 END validate_order;

 PROCEDURE audit_order_change(
 p_order_id IN NUMBER,
 p_old_status IN VARCHAR2,
 p_new_status IN VARCHAR2,
 p_changed_by IN VARCHAR2
 ) IS
 BEGIN
 INSERT INTO order_audit (
 audit_id, order_id, old_status, new_status,
 changed_by, changed_at
 ) VALUES (
 order_audit_seq.NEXTVAL, p_order_id,
 p_old_status, p_new_status,
 p_changed_by, SYSTIMESTAMP
 );
 END audit_order_change;
END io_thecodeforge_order_triggers;
/

-- The trigger: just wires the event to the procedure
CREATE OR REPLACE TRIGGER trg_orders_bi
 BEFORE INSERT ON orders
 FOR EACH ROW
 CALL io_thecodeforge_order_triggers.validate_order(
 :NEW.order_date, :NEW.ship_date, :NEW.customer_id)
/

CREATE OR REPLACE TRIGGER trg_orders_au
 AFTER UPDATE OF status ON orders
 FOR EACH ROW
 CALL io_thecodeforge_order_triggers.audit_order_change(
 :OLD.order_id, :OLD.status, :NEW.status,
 SYS_CONTEXT('USERENV', 'SESSION_USER'))
/

-- Now you can test the logic without the trigger
BEGIN
 io_thecodeforge_order_triggers.validate_order(
 SYSDATE, SYSDATE - 1, 999);
END;
/

-- BAD: Cascading triggers that are hard to debug
-- Trigger on orders updates inventory
CREATE OR REPLACE TRIGGER trg_orders_ai
 AFTER INSERT ON orders
 FOR EACH ROW
BEGIN
 UPDATE inventory
 SET quantity = quantity - :NEW.quantity
 WHERE product_id = :NEW.product_id;
END;
/

-- Trigger on inventory writes to stock_log
CREATE OR REPLACE TRIGGER trg_inventory_au
 AFTER UPDATE ON inventory
 FOR EACH ROW
BEGIN
 INSERT INTO stock_log (product_id, old_qty, new_qty, log_time)
 VALUES (:OLD.product_id, :OLD.quantity, :NEW.quantity, SYSTIMESTAMP);
END;
/

-- Trigger on stock_log sends alert if quantity is low
CREATE OR REPLACE TRIGGER trg_stock_log_ai
 AFTER INSERT ON stock_log
 FOR EACH ROW
BEGIN
 IF :NEW.new_qty < 10 THEN
 INSERT INTO alerts (alert_type, message, created_at)
 VALUES ('LOW_STOCK',
 'Product ' || :NEW.product_id || ' has only ' ||
 :NEW.new_qty || ' units left', SYSTIMESTAMP);
 END IF;
END;
/

-- Map the dependency chain before deploying
SELECT t.table_name, t.trigger_name, t.triggering_event, t.status
FROM user_triggers t
WHERE t.table_name IN ('ORDERS', 'INVENTORY', 'STOCK_LOG', 'ALERTS')
ORDER BY t.table_name, t.trigger_name;

-- Find what a trigger modifies (dependency chain)
SELECT d.referenced_name, d.referenced_type
FROM user_dependencies d
WHERE d.name = 'TRG_ORDERS_AI'
AND d.type = 'TRIGGER';

-- GOOD: Error logging with autonomous transaction
CREATE OR REPLACE TRIGGER trg_server_error
 AFTER SERVERERROR ON SCHEMA
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 v_sql CLOB;
 v_stack CLOB;
BEGIN
 v_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;
 BEGIN
 SELECT sql_text INTO v_sql
 FROM v$sql
 WHERE sql_id = SYS_CONTEXT('USERENV', 'CURRENT_SQL_ID')
 AND ROWNUM = 1;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN v_sql := NULL;
 END;
 INSERT INTO error_log (
 ora_login_user, error_number, error_message,
 error_stack, sql_text
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SERVER_ERROR(1),
 ORA_SERVER_ERROR_MSG(1),
 v_stack, v_sql
 );
 COMMIT;
END;
/

-- GOOD: Connection log that survives logout failure
CREATE OR REPLACE TRIGGER trg_logon_audit
 AFTER LOGON ON SCHEMA
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO connection_log (
 username, logon_time, os_user, machine
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 SYSTIMESTAMP,
 SYS_CONTEXT('USERENV', 'OS_USER'),
 SYS_CONTEXT('USERENV', 'HOST')
 );
 COMMIT;
END;
/

-- BAD: Don't use autonomous transactions for normal audit
-- This creates phantom records when the triggering transaction rolls back
CREATE OR REPLACE TRIGGER trg_bad_autonomous_audit
 AFTER UPDATE ON employees
 FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO employees_audit (...)
 VALUES (...);
 COMMIT;
END;
/

-- Demonstrate: DELETE fires triggers, TRUNCATE does not
CREATE OR REPLACE TRIGGER trg_test_delete
 AFTER DELETE ON test_data
 FOR EACH ROW
BEGIN
 INSERT INTO test_audit (operation, row_data)
 VALUES ('DELETE', 'ID=' || :OLD.id);
END;
/

-- DELETE fires the trigger
DELETE FROM test_data WHERE id = 1;
SELECT * FROM test_audit;
-- Shows: DELETE, ID=1

-- TRUNCATE does NOT fire the trigger
TRUNCATE TABLE test_data;
SELECT * FROM test_audit;
-- No new audit records — TRUNCATE bypassed the trigger entirely

-- Solution: DDL trigger to catch TRUNCATE
CREATE OR REPLACE TRIGGER trg_prevent_truncate
 BEFORE TRUNCATE ON SCHEMA
BEGIN
 IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
 INSERT INTO schema_change_log (
 ora_login_user, ora_sysevent,
 ora_dict_obj_name, ora_dict_obj_type
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SYSEVENT,
 ORA_DICT_OBJ_NAME,
 ORA_DICT_OBJ_TYPE
 );
 IF SYS_CONTEXT('USERENV', 'CLIENT_INFO') != 'ALLOW_TRUNCATE' THEN
 RAISE_APPLICATION_ERROR(-20050,
 'TRUNCATE blocked on ' || ORA_DICT_OBJ_NAME ||
 '. Use DELETE or set ALLOW_TRUNCATE context.');
 END IF;
 END IF;
END;
/

-- Trigger A: set defaults
CREATE OR REPLACE TRIGGER trg_orders_defaults
 BEFORE INSERT ON orders
 FOR EACH ROW
BEGIN
 IF :NEW.order_date IS NULL THEN
 :NEW.order_date := SYSDATE;
 END IF;
 IF :NEW.status IS NULL THEN
 :NEW.status := 'PENDING';
 END IF;
END;
/

-- Trigger B: validate (must run AFTER defaults are set)
CREATE OR REPLACE TRIGGER trg_orders_validate
 BEFORE INSERT ON orders
 FOR EACH ROW
 FOLLOWS trg_orders_defaults
BEGIN
 IF :NEW.ship_date < :NEW.order_date THEN
 RAISE_APPLICATION_ERROR(-20060,
 'Ship date cannot precede order date');
 END IF;
 IF :NEW.status NOT IN ('PENDING', 'CONFIRMED', 'SHIPPED') THEN
 RAISE_APPLICATION_ERROR(-20061,
 'Invalid status: ' || :NEW.status);
 END IF;
END;
/

-- View the ordering
SELECT trigger_name, trigger_type, triggering_event, status
FROM user_triggers
WHERE table_name = 'ORDERS'
ORDER BY trigger_name;

-- Proper error handling in a trigger
CREATE OR REPLACE TRIGGER trg_validate_salary
 BEFORE INSERT OR UPDATE OF salary ON employees
 FOR EACH ROW
BEGIN
 IF :NEW.salary <= 0 THEN
 RAISE_APPLICATION_ERROR(-20070,
 'Salary must be positive. Received: ' || :NEW.salary ||
 ' for employee ' || :NEW.employee_id);
 END IF;
 IF :NEW.salary > 500000 THEN
 RAISE_APPLICATION_ERROR(-20071,
 'Salary exceeds maximum allowed (500000). Received: ' || :NEW.salary);
 END IF;
END;
/

-- Re-raising exceptions with error logging
CREATE OR REPLACE TRIGGER trg_orders_with_logging
 BEFORE INSERT ON orders
 FOR EACH ROW
BEGIN
 IF :NEW.ship_date < :NEW.order_date THEN
 RAISE_APPLICATION_ERROR(-20072,
 'Ship date cannot precede order date');
 END IF;
EXCEPTION
 WHEN OTHERS THEN
 DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
 INSERT INTO error_log (
 error_number, error_message, error_stack
 ) VALUES (
 SQLCODE, SQLERRM,
 DBMS_UTILITY.FORMAT_ERROR_STACK
 );
 COMMIT;
 END;
 RAISE;
END;
/

-- Create a trigger log table for debugging
CREATE TABLE trigger_debug_log (
 log_id NUMBER GENERATED ALWAYS AS IDENTITY,
 log_time TIMESTAMP DEFAULT SYSTIMESTAMP,
 trigger_name VARCHAR2(128),
 session_id NUMBER,
 module VARCHAR2(64),
 action VARCHAR2(64),
 info VARCHAR2(4000)
);

-- Add debug logging to your trigger
CREATE OR REPLACE TRIGGER trg_employees_bi_debug
 BEFORE INSERT ON employees
 FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO trigger_debug_log (trigger_name, session_id, module, action, info)
 VALUES (
 'TRG_EMPLOYEES_BI_DEBUG',
 SYS_CONTEXT('USERENV', 'SESSIONID'),
 SYS_CONTEXT('USERENV', 'MODULE'),
 SYS_CONTEXT('USERENV', 'ACTION'),
 'Employee insert: ' || :NEW.first_name || ' ' || :NEW.last_name
 );
 COMMIT;
END;
/

-- Find all triggers on a table
SELECT trigger_name, trigger_type, triggering_event, status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

-- Disable all triggers on a table (for testing)
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- Re-enable triggers
ALTER TABLE employees ENABLE ALL TRIGGERS;

-- Disable a specific trigger
ALTER TRIGGER trg_employees_bi DISABLE;

-- Find triggers that are invalid
SELECT trigger_name, status
FROM user_triggers
WHERE status = 'INVALID';