DB2 trigger events and transition variables

INSERT triggers

An INSERT trigger activates when a row is inserted into the subject table (or when INSTEAD OF INSERT runs for a view). MERGE insert operations count as inserts. Referential SET NULL is an update, not an insert; CASCADE DELETE is a delete. INSERT SELECT fires the trigger once per inserted row for FOR EACH ROW.

1
2
3
4
5
6
7
8
CREATE TRIGGER HR.INS_EMP
  AFTER INSERT ON HR.EMPLOYEE
  REFERENCING NEW AS N
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    INSERT INTO HR.EMP_LOG (EMPNO, ACTION, LOG_TS)
      VALUES (N.EMPNO, 'INSERT', CURRENT TIMESTAMP);
  END;

Only NEW makes sense on INSERT: there is no old row. You may not specify OLD or OLD TABLE on an INSERT trigger.

UPDATE triggers

An UPDATE trigger activates when a row’s columns are updated. You can limit the event to columns: UPDATE OF SALARY, BONUS. An UPDATE that only changes WORKDEPT then does not fire that trigger. SET NULL from RI is an update of the foreign-key columns and can fire UPDATE triggers on the dependent table.

1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER HR.SAL_CHG
  AFTER UPDATE OF SALARY ON HR.EMPLOYEE
  REFERENCING OLD AS O NEW AS N
  FOR EACH ROW MODE DB2SQL
  WHEN (N.SALARY <> O.SALARY
     OR (N.SALARY IS NULL AND O.SALARY IS NOT NULL)
     OR (N.SALARY IS NOT NULL AND O.SALARY IS NULL))
  BEGIN ATOMIC
    INSERT INTO HR.SAL_HIST (EMPNO, OLD_SAL, NEW_SAL, CHG_TS)
      VALUES (N.EMPNO, O.SALARY, N.SALARY, CURRENT TIMESTAMP);
  END;

Compare OLD and NEW carefully with nulls. N.SALARY <> O.SALARY is UNKNOWN if either is null, so the WHEN would skip. The extra IS NULL tests make salary-clearing visible. BEFORE UPDATE can SET N.SALARY to a rounded value before it is stored.

DELETE triggers

A DELETE trigger activates when a row is deleted, including many CASCADE deletes from a parent. You have OLD values only. AFTER DELETE is the usual audit “who left” pattern. BEFORE DELETE can SIGNAL to veto a delete (for example “cannot delete the last admin”), with the caveat that RESTRICT RI may already have failed first.

1
2
3
4
5
6
7
8
CREATE TRIGGER HR.DEL_EMP
  AFTER DELETE ON HR.EMPLOYEE
  REFERENCING OLD AS O
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    INSERT INTO HR.EMP_LOG (EMPNO, ACTION, LOG_TS)
      VALUES (O.EMPNO, 'DELETE', CURRENT TIMESTAMP);
  END;

Searched DELETE of 1,000 rows fires a FOR EACH ROW trigger 1,000 times. That is a performance topic on the next page. TRUNCATE is not the same as DELETE; do not expect DELETE triggers on TRUNCATE.

REFERENCING, OLD, and NEW transition variables

A transition variable is one column of one affected row, qualified by a correlation name you invent in REFERENCING. It has the same name, type, and nullability as the table column. Qualify always when both OLD and NEW exist: O.SALARY versus N.SALARY.

1
2
REFERENCING OLD AS O NEW AS N
REFERENCING NEW TABLE AS N_TAB OLD TABLE AS O_TAB

In a BEFORE INSERT or BEFORE UPDATE trigger you can assign to NEW variables. That is how you fill generated business fields without a second UPDATE. Assigning to OLD is not meaningful. XML columns cannot be referenced as transition variables.

If a name could be a column, an SQL variable, or a transition variable, qualify it. Ambiguous names are a common bind-time headache in advanced SQL PL triggers.

OLD TABLE and NEW TABLE

Transition tables name the set of affected rows, not one row. OLD TABLE is the before image of every row the statement touched; NEW TABLE is the after image. They are valid for row triggers and statement triggers. They are the natural fit for FOR EACH STATEMENT: run once, SELECT COUNT(*) FROM N_TAB.

1
2
3
4
5
6
7
8
9
10
CREATE TRIGGER HR.LRG_ORDR
  AFTER INSERT ON HR.INVOICE
  REFERENCING NEW TABLE AS N_TABLE
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
    INSERT INTO HR.ALERT (CUST_NO, TOTAL_PRICE, DELIVERY_DATE)
      SELECT CUST_NO, TOTAL_PRICE, DELIVERY_DATE
        FROM N_TABLE
       WHERE TOTAL_PRICE > 10000;
  END;

You do not UPDATE a transition table. It is a read-only snapshot of the statement’s effect. IBM documents transition tables primarily with AFTER triggers. BEFORE triggers work with row NEW/OLD variables, not with rewriting a whole NEW TABLE.

IDENTITY_VAL_LOCAL() inside a BEFORE INSERT trigger is defined to return null—do not copy identity values that way. Use NEW.identity-column after Db2 has assigned it, as in IBM’s identity trigger examples, or SELECT FROM FINAL TABLE on the original INSERT.

WHEN conditions

WHEN (predicate) is evaluated per row for row triggers and once per statement for statement triggers. If it is FALSE or UNKNOWN, the body is skipped. Keep WHEN cheap: it runs even when you later no-op. Putting the same predicate only in the body’s IF is an advanced-trigger style; basic triggers rely on WHEN.

A BEFORE trigger WHEN fullselect must not reference the subject table in ways the manual forbids (the table is mid-change). Prefer validating against OTHER tables or against NEW/OLD variables only.

Events from RI and MERGE

ON DELETE CASCADE on a parent DELETE fires DELETE triggers on dependents. ON DELETE SET NULL fires UPDATE triggers on dependents. MERGE can fire INSERT and UPDATE triggers in one statement for different rows. Design trigger bodies to be safe when the “user” was really RI or MERGE, not a COBOL INSERT.

Utilities: LOAD typically does not fire INSERT triggers. If an audit table must capture loads, the load job must write the audit, or you accept that triggers are SQL-path only.

Column lists on UPDATE OF interact with INCLUDE columns and with SET COL = COL (no real change). If the UPDATE statement mentions the column, the trigger can fire even when the value did not change—use WHEN (N.COL IS DISTINCT FROM O.COL) to ignore no-ops.

Transition variable types match the table column, including CCSID and nullability. You cannot REFERENCING a column that is XML. LOB columns have size and locator limits inside a trigger body—copying a 2 GB BLOB from OLD to an audit table on every UPDATE is a denial-of-service you wrote yourself. Audit the business columns; leave the document body in the subject table unless the requirement is a true document history table with sized LOBs and a plan for DASD.

Correlation names in REFERENCING must not clash with SQL variables in an advanced trigger, or with table names you FROM in the body. O and N are conventional. O_TAB and N_TAB (or OLD_TAB / NEW_TAB) mark transition tables. Qualifying N.SALARY versus EMPLOYEE.SALARY matters: inside a trigger, unqualified SALARY can be ambiguous once you JOIN another table. Always qualify transition variables with the REFERENCING name.

Positioned UPDATE and DELETE (WHERE CURRENT OF) still fire UPDATE and DELETE triggers. A cursor loop that updates 10,000 rows is 10,000 row-trigger executions if you used FOR EACH ROW. Host-variable values are not transition variables; only OLD/NEW images of the subject row are. If the COBOL program needs to know what the BEFORE trigger stored in NEW.LASTNAME, it must SELECT the row after the INSERT, or use SELECT FROM FINAL TABLE on the INSERT when that form is available for the statement.

MERGE is one SQL statement that can insert some rows and update others. INSERT triggers fire for the insert branch; UPDATE triggers fire for the update branch. A statement-level AFTER INSERT trigger sees only the inserted set in NEW TABLE, not the updated rows. If you need one audit of “everything MERGE touched,” use both an INSERT and an UPDATE trigger, or a pair of statement triggers, rather than assuming one NEW TABLE holds the whole MERGE.

WHEN clauses can compare OLD and NEW, test special registers (CURRENT SQLID, CURRENT TIMESTAMP), and, with restrictions, run a scalar subquery. Keep the subquery on a small lookup table with a unique index. A WHEN that tablespaces-scans HR.EMPLOYEE from inside a BEFORE INSERT on HR.EMPLOYEE is both illegal in many forms and a performance cliff when it is not. Prefer NEW values and a tiny code table.

Debug transition data with a dedicated HR.TRIG_DEBUG table the trigger INSERTs into (AFTER only). Select from it after a one-row test. Do not leave debug triggers in production: they double the write path. WHEN (N.EMPNO = '000010') can limit a temporary debug trigger to one employee while you learn OLD versus NEW on UPDATE.

Explain It Like I'm Five

INSERT is “a new toy arrives”—you only see the new toy (NEW). DELETE is “a toy leaves”—you only see the old toy (OLD). UPDATE is “someone paints the toy”—you see the before color and the after color. REFERENCING is naming those snapshots “O” and “N” so you can say N.color. OLD TABLE / NEW TABLE is dumping every toy that moved today into a temporary box so you can count them once instead of looking at each toy by itself.

Exercises

1. Write REFERENCING for an AFTER UPDATE trigger that needs old and new SALARY.
2. Write UPDATE OF so the trigger fires only when SALARY or BONUS is updated.
3. Explain why WHEN (N.SALARY <> O.SALARY) misses null-to-value changes.
4. Write a statement-level AFTER INSERT that copies high-value rows from NEW TABLE.
5. Which transition names are illegal on a DELETE trigger?