DB2 Triggers: CREATE TRIGGER, BEFORE, AFTER, and INSTEAD OF Examples
DB2 Triggers: Complete Guide with Examples
A trigger is a named database object that automatically executes a block of SQL logic in response to a data change event — an INSERT, UPDATE, or DELETE on a specific table. Unlike stored procedures, triggers are not called explicitly. DB2 fires them automatically whenever the triggering event occurs, making them ideal for enforcing data integrity rules, maintaining audit trails, and propagating changes to related tables.
This guide covers every aspect of DB2 trigger creation and management, from simple audit logging to complex business rule enforcement.
Trigger Anatomy
Every DB2 trigger has four key attributes:
Timing: BEFORE (fires before the change), AFTER (fires after the change), or INSTEAD OF (replaces the change on views).
Event: INSERT, UPDATE, DELETE, or a combination using OR.
Granularity: FOR EACH ROW (once per affected row) or FOR EACH STATEMENT (once per SQL statement).
Body: The SQL PL logic to execute when the trigger fires.
AFTER Trigger — Audit Logging
The most common trigger use case is writing an audit log whenever a sensitive table changes. This AFTER trigger records every salary change to an audit table:
First, create the audit table:
CREATE TABLE SALARY_AUDIT (
AUDIT_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
EMPNO CHAR(6) NOT NULL,
OLD_SALARY DECIMAL(9,2),
NEW_SALARY DECIMAL(9,2),
CHANGED_BY VARCHAR(50),
CHANGED_AT TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_AUDIT PRIMARY KEY (AUDIT_ID)
);Now create the trigger:
CREATE OR REPLACE TRIGGER AUDIT_SALARY_CHANGE
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING OLD ROW AS old_emp
NEW ROW AS new_emp
FOR EACH ROW
WHEN (old_emp.SALARY <> new_emp.SALARY)
BEGIN ATOMIC
INSERT INTO SALARY_AUDIT (EMPNO, OLD_SALARY, NEW_SALARY, CHANGED_BY)
VALUES (new_emp.EMPNO,
old_emp.SALARY,
new_emp.SALARY,
USER);
END@Key points:
AFTER UPDATE OF SALARY— only fires when the SALARY column is specifically updated, not on any column changeREFERENCING OLD ROW AS old_emp NEW ROW AS new_emp— gives you named access to pre- and post-change valuesWHEN (old_emp.SALARY <> new_emp.SALARY)— the optional WHEN clause prevents the trigger body from running if salary didn't actually changeBEGIN ATOMIC— all statements in the trigger succeed or roll back together
BEFORE Trigger — Validation and Value Setting
BEFORE triggers run before the data change is applied. They are used to validate input or automatically set column values.
Preventing Invalid Data
CREATE OR REPLACE TRIGGER VALIDATE_SALARY
BEFORE INSERT OR UPDATE ON EMPLOYEE
REFERENCING NEW ROW AS new_emp
FOR EACH ROW
BEGIN ATOMIC
IF new_emp.SALARY < 0 THEN
SIGNAL SQLSTATE '75001'
SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
IF new_emp.SALARY > 500000 THEN
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = 'Salary exceeds maximum allowed value of 500,000';
END IF;
END@The SIGNAL statement raises a custom error that stops the triggering statement and rolls back the change. You choose any SQLSTATE in the range 70000–79999 for user-defined errors.
Auto-Setting a Column Value
CREATE OR REPLACE TRIGGER SET_HIRE_DATE
BEFORE INSERT ON EMPLOYEE
REFERENCING NEW ROW AS new_emp
FOR EACH ROW
WHEN (new_emp.HIREDATE IS NULL)
BEGIN ATOMIC
SET new_emp.HIREDATE = CURRENT DATE;
END@In a BEFORE trigger you can modify the NEW ROW values — the modified value is what actually gets written to the table.
AFTER INSERT Trigger — Cascading Logic
This trigger automatically creates a related record in another table whenever a new employee is inserted:
CREATE OR REPLACE TRIGGER NEW_EMPLOYEE_SETUP
AFTER INSERT ON EMPLOYEE
REFERENCING NEW ROW AS new_emp
FOR EACH ROW
BEGIN ATOMIC
-- Create a default benefits record
INSERT INTO EMPLOYEE_BENEFITS (EMPNO, HEALTH_PLAN, DENTAL_PLAN, ENROL_DATE)
VALUES (new_emp.EMPNO, 'STANDARD', 'BASIC', CURRENT DATE);
-- Log the new hire
INSERT INTO HR_EVENT_LOG (EVENT_TYPE, EMPNO, EVENT_DATE, NOTES)
VALUES ('NEW_HIRE', new_emp.EMPNO, CURRENT DATE,
'Auto-enrolled in standard benefits plan');
END@AFTER DELETE Trigger — Soft Delete / Archive
Before deleting records, archive them to a history table:
CREATE OR REPLACE TRIGGER ARCHIVE_DELETED_EMPLOYEE
AFTER DELETE ON EMPLOYEE
REFERENCING OLD ROW AS old_emp
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO EMPLOYEE_ARCHIVE
SELECT *, CURRENT TIMESTAMP, USER
FROM EMPLOYEE
WHERE EMPNO = old_emp.EMPNO;
-- Note: in AFTER DELETE the row is already gone from EMPLOYEE
-- so insert the OLD ROW values directly:
INSERT INTO EMPLOYEE_ARCHIVE
(EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SALARY, HIREDATE,
ARCHIVED_AT, ARCHIVED_BY)
VALUES
(old_emp.EMPNO, old_emp.FIRSTNME, old_emp.LASTNAME,
old_emp.WORKDEPT, old_emp.SALARY, old_emp.HIREDATE,
CURRENT TIMESTAMP, USER);
END@Statement-Level Triggers
A statement-level trigger fires once per SQL statement, not once per row. Use it when you want to react to a bulk operation as a whole:
CREATE OR REPLACE TRIGGER LOG_BULK_SALARY_UPDATE
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING OLD TABLE AS old_data
NEW TABLE AS new_data
FOR EACH STATEMENT
BEGIN ATOMIC
INSERT INTO BULK_CHANGE_LOG (TABLE_NAME, CHANGE_TYPE, ROW_COUNT, CHANGED_AT, CHANGED_BY)
SELECT 'EMPLOYEE',
'SALARY_UPDATE',
COUNT(*),
CURRENT TIMESTAMP,
USER
FROM new_data;
END@The OLD TABLE and NEW TABLE transition tables are only available in statement-level triggers.
INSTEAD OF Trigger — DML on Views
INSTEAD OF triggers allow INSERT, UPDATE, and DELETE through a view that would otherwise be read-only:
-- A view joining EMPLOYEE and DEPARTMENT
CREATE VIEW EMP_DEPT_VIEW AS
SELECT E.EMPNO, E.LASTNAME, E.SALARY, D.DEPTNAME
FROM EMPLOYEE E
JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO;
-- INSTEAD OF trigger to handle UPDATE through the view
CREATE OR REPLACE TRIGGER EMP_DEPT_UPDATE
INSTEAD OF UPDATE ON EMP_DEPT_VIEW
REFERENCING OLD ROW AS old_row
NEW ROW AS new_row
FOR EACH ROW
BEGIN ATOMIC
-- Only update the base table columns we support changing
UPDATE EMPLOYEE
SET SALARY = new_row.SALARY,
LASTNAME = new_row.LASTNAME
WHERE EMPNO = old_row.EMPNO;
END@Now application code can UPDATE EMP_DEPT_VIEW SET SALARY = 75000 WHERE EMPNO = '000010' and the trigger routes it to the correct base table.
UPDATE OF Specific Columns
You can restrict a trigger to fire only when specific columns are updated:
CREATE OR REPLACE TRIGGER TRACK_DEPT_CHANGE
AFTER UPDATE OF WORKDEPT ON EMPLOYEE
REFERENCING OLD ROW AS old_emp
NEW ROW AS new_emp
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO DEPT_TRANSFER_LOG (EMPNO, FROM_DEPT, TO_DEPT, TRANSFER_DATE)
VALUES (new_emp.EMPNO, old_emp.WORKDEPT, new_emp.WORKDEPT, CURRENT DATE);
END@This fires only when WORKDEPT changes, not when SALARY or any other column is updated.
Trigger Execution Order
When multiple triggers exist on the same table and event, DB2 fires them in the order they were created. You can check the order from the catalog:
SELECT TRIGNAME, TRIGEVENT, TRIGTIME, CREATE_TIME
FROM SYSCAT.TRIGGERS
WHERE TABNAME = 'EMPLOYEE'
AND TABSCHEMA = 'DB2ADMIN'
ORDER BY TRIGTIME, CREATE_TIME;If order matters, consider combining logic into fewer triggers.
Viewing and Dropping Triggers
-- List all triggers on a table
SELECT TRIGNAME, TRIGEVENT, TRIGTIME, GRANULARITY
FROM SYSCAT.TRIGGERS
WHERE TABNAME = 'EMPLOYEE';
-- View trigger source
SELECT TEXT
FROM SYSCAT.TRIGGERS
WHERE TRIGNAME = 'AUDIT_SALARY_CHANGE';
-- Drop a trigger
DROP TRIGGER AUDIT_SALARY_CHANGE;
-- Disable a trigger (DB2 LUW)
ALTER TRIGGER AUDIT_SALARY_CHANGE DISABLE;
-- Re-enable
ALTER TRIGGER AUDIT_SALARY_CHANGE ENABLE;Common Trigger Pitfalls
Mutating table errors: A trigger on EMPLOYEE cannot SELECT from EMPLOYEE in some scenarios without careful cursor management. If you need to re-read the triggering table, use the transition variables (OLD ROW / NEW ROW) instead of a new SELECT.
Infinite loops: If a trigger on table A updates table B, and a trigger on table B updates table A, you get a recursive trigger loop. DB2 limits trigger nesting depth (default 16 levels) and will raise SQLSTATE 54038 when the limit is exceeded. Design triggers to avoid circular dependencies.
Performance impact: Triggers add overhead to every DML statement on the table. Heavy trigger logic on high-volume OLTP tables can become a bottleneck. Profile before and after adding triggers to busy tables.
NOT ATOMIC vs ATOMIC: Using BEGIN ATOMIC means all statements in the trigger body roll back if any one fails. BEGIN NOT ATOMIC allows partial success. Choose based on your requirements.
Practical Example: Complete Audit System
-- Generic audit log table
CREATE TABLE DML_AUDIT_LOG (
LOG_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
TABLE_NAME VARCHAR(50),
OPERATION CHAR(6), -- INSERT, UPDATE, DELETE
KEY_VALUE VARCHAR(50),
USER_ID VARCHAR(50),
TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
);
-- Audit trigger for EMPLOYEE inserts
CREATE OR REPLACE TRIGGER EMP_INSERT_AUDIT
AFTER INSERT ON EMPLOYEE
REFERENCING NEW ROW AS n
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO DML_AUDIT_LOG (TABLE_NAME, OPERATION, KEY_VALUE, USER_ID)
VALUES ('EMPLOYEE', 'INSERT', n.EMPNO, USER);
END@
-- Audit trigger for EMPLOYEE deletes
CREATE OR REPLACE TRIGGER EMP_DELETE_AUDIT
AFTER DELETE ON EMPLOYEE
REFERENCING OLD ROW AS o
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO DML_AUDIT_LOG (TABLE_NAME, OPERATION, KEY_VALUE, USER_ID)
VALUES ('EMPLOYEE', 'DELETE', o.EMPNO, USER);
END@Next Steps
Triggers and stored procedures together provide complete server-side programmability in DB2. For reusable read-only result sets, explore DB2 Views and Materialized Query Tables. For the full DB2 learning path, visit the DB2 Mastery course hub and keep the DB2 Cheat Sheet handy for quick reference.