DB2 trigger design: nesting, granularity, and performance

Trigger granularity: FOR EACH ROW and FOR EACH STATEMENT

FOR EACH ROW runs the WHEN clause and body once per affected row. A searched UPDATE of 50,000 salaries runs the row trigger 50,000 times. That is correct for per-row audit lines and BEFORE SET N.COL = … It is expensive if the body does a singleton SELECT against another table every time.

FOR EACH STATEMENT runs once per triggering statement, regardless of row count. Use NEW TABLE / OLD TABLE to process the set. Increment a counter by COUNT(*) FROM N_TAB instead of +1 fifty thousand times.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Row: 50,000 updates → 50,000 body executions
CREATE TRIGGER HR.SAL_ROW
  AFTER UPDATE OF SALARY ON HR.EMPLOYEE
  REFERENCING NEW AS N
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    UPDATE HR.COMPANY_STATS SET CHG_CNT = CHG_CNT + 1;
  END;
 
-- Statement: 50,000 updates → 1 body execution
CREATE TRIGGER HR.SAL_STMT
  AFTER UPDATE OF SALARY ON HR.EMPLOYEE
  REFERENCING NEW TABLE AS N_TAB
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
    UPDATE HR.COMPANY_STATS
       SET CHG_CNT = CHG_CNT + (SELECT COUNT(*) FROM N_TAB);
  END;

BEFORE triggers cannot be FOR EACH STATEMENT. INSTEAD OF is typically FOR EACH ROW so you can map each view row to base tables.

Trigger ordering

Several triggers can share the same activation time and event. Db2 activates them in creation order (the trigger that was created first runs first). DROP and CREATE moves a trigger to the end of the queue. Advanced OR REPLACE can change identity and therefore order—test after replace.

Do not rely on two AFTER INSERT triggers secretly communicating unless you document the create order in source control. If order matters, use one trigger body, or have the second trigger’s WHEN depend on a column the first BEFORE trigger set.

Trigger nesting and recursive triggers

Cascading happens when an AFTER trigger issues INSERT/UPDATE/DELETE that activates more triggers. The chain is limited to 16 levels. Past that, the statement fails with SQLCODE -724 and the triggering operation plus triggered actions roll back.

A recursive trigger is one that, directly or through a cycle of tables, fires itself. Example: AFTER UPDATE ON T updates T again. Without a WHEN (N.COL IS DISTINCT FROM O.COL) or a guard column, you recurse until -724.

1
2
-- Guard: only run when salary actually changed
WHEN (N.SALARY IS DISTINCT FROM O.SALARY)

RI CASCADE is another source of nested deletes. Count CASCADE depth plus AFTER DELETE triggers that delete from a third table. Draw the graph. Sixteen levels is enough for honest designs and too few for accidental ping-pong.

BEFORE triggers do not cascade data-change SQL; they are the safe place to adjust NEW values without starting a nest.

Trigger SQL, packages, and dependencies

The trigger body is SQL (and SQL PL for advanced triggers). It is bound as a trigger package. EXPLAIN and accounting traces can show that package. If the body references HR.EMP_AUDIT and someone DROP TABLEs it, the trigger package becomes invalid. The next INSERT into EMPLOYEE may autobind (CPU spike, possible failure) or fail until you recreate the audit table and rebind.

Dependencies are recorded in the catalog (SYSPACKDEP and trigger catalog tables). DROP TABLE of the subject table drops its triggers. DROP of an object the body uses does not always DROP the trigger—it invalidates it. Source-control the CREATE TRIGGER text next to the tables it needs.

Keep bodies small. Call a native SQL procedure for heavy logic if you must, but remember that call still counts toward nesting and elapsed time. Avoid SELECT FROM the subject table in BEFORE WHEN clauses where the manual forbids it.

Trigger security

The owner of the trigger needs the privileges the body uses (INSERT on the audit table, UPDATE on the counter). The end user needs INSERT/UPDATE/DELETE on the subject table (or view). They do not need the audit table privilege. That is powerful and dangerous: a trigger can write to a table the user cannot see. Review trigger owners like you review stored-procedure owners. Do not CREATE TRIGGER under a personal TSO ID that will be revoked.

REFERENCING requires SELECT on the subject table for the definer. Row permissions and column masks still apply to the triggering user’s statement; the trigger body runs as the owner and can see more. Document that split for auditors.

Trigger performance

• Prefer FOR EACH STATEMENT when the work is a set aggregate
• Narrow UPDATE OF columns so unrelated updates skip the trigger
• Keep WHEN selective and cheap
• Index tables the body touches (the audit table’s insert path, the counter’s primary key)
• Do not SELECT the subject table row-by-row if NEW already has the columns
• Remember LOAD does not fire triggers—do not “optimize” by putting RI only in a trigger
• Measure with accounting (trigger package CPU) not folklore

Row triggers that INSERT into an unindexed audit table can serialize on a hot last page. Cluster or append-friendly design of the audit table matters as much as the trigger text. Disable or drop triggers during a bulk UPDATE only with a written window and a plan to catch up audit rows.

Trigger errors and rollback

If the body SIGNALs, hits -803, or otherwise fails, Db2 backs out the triggering statement and the triggered actions. You do not keep the EMPLOYEE insert if the audit insert failed (unless you wrote a design that catches errors in advanced SQL PL—and you should still think in “all or nothing” for the user-visible change).

Operations that COMMITTED before the triggering statement stay committed. A trigger failure is not ROLLBACK WORK of the whole batch unless the application treats the negative SQLCODE that way. COBOL should check SQLCODE after INSERT and not assume the row is there when the trigger failed.

SQLCODE -724 is nesting. SQLCODE -723 and related codes report trigger failures; the message text names the trigger. Read it before you “fix” the application. Adding a trigger never validates old rows—pair with CHECK DATA / a cleanup job.

Debug with a quiet table and one row first. Nested triggers are hard to trace; add an audit column or a debug table at each level temporarily. Production triggers should not PRINT to SYSOUT as their error strategy.

Advanced trigger versions let you keep V1 active while you bind V2 in a test path, then ACTIVATE VERSION. That reduces the DROP/CREATE order surprise of basic triggers, but you still test activation order when two advanced triggers share an event.

Recursive designs that are intentional still need an escape hatch. A common pattern is a “depth” or “already_processed” column the trigger sets on NEW in a BEFORE trigger, and an AFTER trigger WHEN (N.FLAG = 'N') that does extra work and UPDATEs FLAG to 'Y'. Without that, the AFTER UPDATE fires the AFTER UPDATE. IS DISTINCT FROM on the business column is simpler when the recursion was accidental (salary rounded to the same value, trigger fires again because you SET SALARY = ROUND(SALARY) every time).

SQLCODE -723 is a triggered-SQL-statement failure; the message text identifies the trigger. SQLCODE -724 is nesting overflow. SQLCODE -746 and related codes appear when the trigger body is invalid or the package will not autobind. Teach operators to read the trigger name from the message before they restart the COBOL job. Restarting without fixing the audit table’s -803 will fail forever and leave the user-visible row unapplied, which is correct but looks like “Db2 ate my insert.”

Security reviews should list every table each trigger writes. A trigger owner with INSERT on PAYROLL_AUDIT and SELECT on EMPLOYEE can leak salary history into an audit table that a wider group can SELECT. Grant SELECT on audit tables as tightly as the subject table. Do not CREATE TRIGGER under SYSADM “so it always works”; use a role-like owner (HRTRIG) with the minimum GRANT set, and revoke when the trigger is dropped.

Dependencies cut both ways. DROP INDEX on a table the trigger SELECTs can invalidate the trigger package and change the access path after autobind. EXPLAIN the trigger package after a RUNSTATS on those tables. A trigger that did a matching unique index lookup can fall back to a scan after someone dropped a “redundant” index. Treat trigger SQL with the same index discipline as COBOL SQL.

Performance testing must use production-like row counts. A FOR EACH ROW trigger that looks instant on 20 SPUFI rows can dominate a 2-million-row year-end UPDATE. Compare elapsed time with the trigger dropped (in a copy of the table) versus present. If most of the cost is the audit INSERT, buffer-pool and index design on the audit table will beat rewriting the WHEN clause. If most of the cost is a correlated subquery in WHEN, rewrite to NEW columns or a join inside a statement-level INSERT SELECT from NEW TABLE.

Rollback of the triggering statement does not ROLLBACK the whole unit of work unless the application issues ROLLBACK after the negative SQLCODE. A COBOL program that ignores SQLCODE after INSERT can COMMIT later and persist earlier successful statements in the same UOW while believing the last insert happened. Always check SQLCODE. Savepoints are an application technique; a failed trigger is not a savepoint. The insert and its trigger INSERTs undo together; previous inserts in the UOW remain until COMMIT or ROLLBACK.

Explain It Like I'm Five

FOR EACH ROW is clapping once for every kid who walks through the door. FOR EACH STATEMENT is clapping once for the whole class. Nesting is kids opening more doors that open more doors; after sixteen doors Db2 yells stop (-724) and everyone walks back out. The teacher (owner) is allowed to write on the private clipboard even when the kid is not. If the clipboard pencil breaks, the kid’s homework is undone too. Fast designs clap once for the class and keep the WHEN note short.

Exercises

1. Rewrite a row trigger that does SET CNT = CNT + 1 as a statement trigger using NEW TABLE.
2. Explain how DROP and CREATE of a basic trigger can change activation order.
3. What happens at cascade depth 17?
4. Who needs INSERT privilege on the audit table: the end user or the trigger owner?
5. If the audit INSERT hits -803, is the original EMPLOYEE INSERT kept?