Triggers

A trigger is a set of SQL statements that DB2 runs automatically when a specific event happens on a table, such as an INSERT, UPDATE, or DELETE.

What is a trigger

• It fires automatically. No program has to call it, so rules are enforced for every application touching the table.
• It is commonly used for audit logging, data validation, and keeping related tables in sync.
• It is created once with CREATE TRIGGER and stays active until it is dropped.
• The trigger body can read the OLD row values (before the change) and NEW row values (after the change).

Types of triggers

• BEFORE trigger :- Runs before the row is changed. Used to validate or modify the NEW values before they are written.
• AFTER trigger :- Runs after the row is changed. Used for audit logging and updating other tables.
• INSTEAD OF trigger :- Defined on a view; runs in place of the INSERT, UPDATE, or DELETE so views can be updatable.
• Each trigger fires either FOR EACH ROW or FOR EACH STATEMENT, and BEFORE triggers cannot modify the database except the NEW transition values.

Example: audit trigger on salary changes

• Example that logs every big salary raise into an audit table:-
  CREATE TRIGGER EMP_SAL_AUDIT AFTER UPDATE OF SALARY ON EMPLOYEE REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.SALARY > O.SALARY * 1.20) BEGIN ATOMIC INSERT INTO SALARY_AUDIT (EMPNO, OLD_SAL, NEW_SAL, CHANGED_AT) VALUES (N.EMPNO, O.SALARY, N.SALARY, CURRENT TIMESTAMP); END

Points to remember

• BEFORE triggers can change NEW column values, but AFTER triggers cannot change the row that fired them.
• Triggers add overhead to every INSERT, UPDATE, and DELETE on the table, so keep the trigger body small.
• Use WHEN to filter rows so the trigger body runs only when it is really needed.
• Too many triggers on one table make debugging hard; document what each trigger does.