Create a DB2 trigger

Prerequisites and trigger choices

Confirm CREATEIN authority on the schema, privileges on the subject object and referenced objects, and ownership of the business rule. Define whether the rule belongs in the database rather than in an application, and identify every statement that might activate it, including utilities, batch loads, and replication apply.

BEFORE and AFTER triggers apply to base tables. BEFORE executes before Db2 changes the row and cannot use changes that activate other triggers; AFTER runs after the change and can trigger further work. INSTEAD OF triggers apply to views and replace the requested change against that view.

Row, statement, and transition data

FOR EACH ROW runs once per affected row and uses transition variables such as OLD AS O and NEW AS N. Use it for row validation, derived values, or audit rows. FOR EACH STATEMENT runs once for a set of changed rows; it is not available for BEFORE or INSTEAD OF triggers.

Transition tables expose the complete old or new affected row set and are read-only. They are useful for set-based AFTER work and avoid accidentally writing one audit operation per row. Test multi-row INSERT, UPDATE, and DELETE, not just a single-row demo.

Create and verify

Use a descriptive name and explicitly name columns in the event when only selected UPDATE columns should activate the rule. Keep action SQL deterministic and short; application users need a clear SQLSTATE or message when validation fails.

1
2
3
4
5
6
7
8
CREATE TRIGGER SALES.ORDER_AUDIT
  AFTER UPDATE OF STATUS ON SALES.ORDERS
  REFERENCING OLD AS O NEW AS N
  FOR EACH ROW
  MODE DB2SQL
  INSERT INTO SALES.ORDER_AUDIT
    (ORDER_ID, OLD_STATUS, NEW_STATUS, CHANGED_AT)
  VALUES (O.ORDER_ID, O.STATUS, N.STATUS, CURRENT TIMESTAMP);

Verification and errors

Verify the catalog definition, run the triggering operation, and compare subject and audit rows in one unit of work. Test a multi-row statement, rollback, constraint failure, nested trigger behavior, and the intended authorization role. Check trigger ordering when multiple triggers share table, event, and activation time: Db2 activates them in creation order.

Common errors include insufficient authority, invalid references to OLD or NEW, forbidden actions in a BEFORE trigger, recursion, incorrect view rules for INSTEAD OF, and unexpected replication or bulk-load effects. Avoid triggers that silently change broad business state; use procedures or explicit services for complex workflows.

Operational guidance

Document each trigger’s event, action, owner, dependent objects, error states, and performance expectation. Monitor added CPU and log volume. Trigger code is production code even though it is stored in the catalog; version it, review it, test rollback, and include it in disaster-recovery and schema-promotion tooling.

When row or column access control is enabled, check secure-trigger requirements for your Db2 release. Security rules and definer/invoker behavior are part of the design, not a post-deployment fix.