SQLMentor // learn db2

Triggers

Difficulty: Advanced · ~10 min read

Overview

A trigger is a piece of SQL PL that DB2 runs automatically when a specific event occurs on a table — typically an INSERT, UPDATE, or DELETE. Unlike a stored procedure, you never call a trigger directly: DB2 fires it for you.

Common uses:

  • Audit logs — record who changed what and when
  • Derived columns — keep a denormalized value in sync
  • Validation — enforce rules CHECK constraints can't express
  • Cascading updates — propagate changes to related tables

Trigger timing × scope gives you four combinations:

Timing Scope Use case
BEFORE INSERT/UPDATE FOR EACH ROW Set defaults, validate incoming rows
AFTER INSERT/UPDATE/DELETE FOR EACH ROW Audit log, sync to another table
BEFORE INSERT/UPDATE/DELETE FOR EACH STATEMENT Pre-checks for the whole statement
AFTER INSERT/UPDATE/DELETE FOR EACH STATEMENT Post-checks, summary counts

Triggers fire inside the same transaction as the statement that triggered them. If the trigger fails, the original statement is rolled back.

Syntax

CREATE [OR REPLACE] TRIGGER trg_name
  { BEFORE | AFTER } { INSERT | UPDATE [OF cols] | DELETE }
  ON table_name
  [REFERENCING { NEW AS n | OLD AS o | NEW TABLE AS nt | OLD TABLE AS ot }]
  { FOR EACH ROW | FOR EACH STATEMENT }
  [WHEN (condition)]
  BEGIN [ATOMIC]
    -- SQL PL statements
  END@

Examples

We'll use these tables throughout:

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name       VARCHAR(80),
  price      DECIMAL(9,2),
  updated_at TIMESTAMP
);

CREATE TABLE product_audit (
  audit_id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INTEGER,
  action     CHAR(1),                     -- I, U, D
  old_price  DECIMAL(9,2),
  new_price  DECIMAL(9,2),
  changed_by VARCHAR(40),
  changed_at TIMESTAMP
);

Example 1: BEFORE INSERT — auto-fill a timestamp

CREATE OR REPLACE TRIGGER trg_set_updated_at_ins
  BEFORE INSERT ON products
  REFERENCING NEW AS n
  FOR EACH ROW
  BEGIN ATOMIC
    SET n.updated_at = CURRENT_TIMESTAMP;
  END@

INSERT INTO products (product_id, name, price) VALUES (1, 'Widget', 9.99)@

SELECT * FROM products WHERE product_id = 1@

Output:

PRODUCT_ID  NAME    PRICE  UPDATED_AT
----------  ------  -----  --------------------------
         1  Widget   9.99  2026-05-14-09.42.11.293045

updated_at was filled even though the INSERT didn't provide it.

Example 2: BEFORE UPDATE — refresh the same timestamp

CREATE OR REPLACE TRIGGER trg_set_updated_at_upd
  BEFORE UPDATE ON products
  REFERENCING NEW AS n
  FOR EACH ROW
  BEGIN ATOMIC
    SET n.updated_at = CURRENT_TIMESTAMP;
  END@

UPDATE products SET price = 12.99 WHERE product_id = 1@

updated_at automatically rolls forward.

Example 3: AFTER UPDATE — audit log

CREATE OR REPLACE TRIGGER trg_audit_product_price
  AFTER UPDATE OF price ON products
  REFERENCING OLD AS o NEW AS n
  FOR EACH ROW
  WHEN (o.price <> n.price)            -- only log real changes
  BEGIN ATOMIC
    INSERT INTO product_audit
           (product_id, action, old_price, new_price, changed_by, changed_at)
    VALUES (n.product_id, 'U', o.price, n.price, CURRENT_USER, CURRENT_TIMESTAMP);
  END@

UPDATE products SET price = 14.99 WHERE product_id = 1@
SELECT * FROM product_audit@

UPDATE OF price restricts firing to changes on that column only. The WHEN clause adds another layer — no audit row if price was set to its existing value.

Example 4: AFTER DELETE — audit

CREATE OR REPLACE TRIGGER trg_audit_product_del
  AFTER DELETE ON products
  REFERENCING OLD AS o
  FOR EACH ROW
  BEGIN ATOMIC
    INSERT INTO product_audit
           (product_id, action, old_price, new_price, changed_by, changed_at)
    VALUES (o.product_id, 'D', o.price, NULL, CURRENT_USER, CURRENT_TIMESTAMP);
  END@

DELETE FROM products WHERE product_id = 1@
SELECT * FROM product_audit@

OLD AS o is the only valid reference for DELETE — there is no NEW.

Example 5: Validation that CHECK can't express

CREATE OR REPLACE TRIGGER trg_no_price_drop
  BEFORE UPDATE OF price ON products
  REFERENCING OLD AS o NEW AS n
  FOR EACH ROW
  BEGIN ATOMIC
    IF n.price < o.price * 0.5 THEN
      SIGNAL SQLSTATE '75001'
        SET MESSAGE_TEXT = 'Price cannot drop by more than 50% in a single update';
    END IF;
  END@

-- This now fails:
UPDATE products SET price = 1 WHERE product_id = 2@

A CHECK constraint can compare columns within a row but not before vs. after values — that's a job for a BEFORE UPDATE trigger.

Example 6: Statement-level trigger

CREATE OR REPLACE TRIGGER trg_summary_after_bulk
  AFTER UPDATE ON products
  REFERENCING NEW TABLE AS nt
  FOR EACH STATEMENT
  BEGIN ATOMIC
    INSERT INTO daily_summary (run_at, n_updated)
    SELECT CURRENT_TIMESTAMP, COUNT(*) FROM nt;
  END@

NEW TABLE AS nt exposes the entire set of updated rows as a transition table — perfect for batch summaries.

Example 7: Dropping and inspecting

DROP TRIGGER trg_audit_product_price@

SELECT trigname, tabname, triggertime, eventupdate
FROM   syscat.triggers
WHERE  tabschema = CURRENT_USER@

SYSCAT.TRIGGERS shows every trigger in the database.

Notes & Tips

  • Triggers run inside the same transaction as the triggering statement. If a trigger raises an error or its statements fail, the original statement is rolled back.
  • Triggers can cascade — an UPDATE may fire a trigger that issues another UPDATE on a different table, which fires yet another trigger. Watch for trigger loops.
  • DB2 limits trigger recursion. A trigger on table T cannot directly modify T in a way that re-fires the same trigger (a row-level cascade on the same table → error).
  • Performance: triggers run on every affected row (for FOR EACH ROW). On bulk updates, this can be significant. Prefer set-based logic in statement-level triggers when possible.
  • For audit / event-sourcing systems at scale, consider logical replication or DB2's CDC (Change Data Capture) instead of triggers — triggers slow down OLTP writes.
  • A common anti-pattern: putting business logic in triggers makes systems opaque. Stored procedures are usually clearer because the call is explicit.

Practice Exercises

  1. Build an audit_orders trigger that logs every INSERT/UPDATE/DELETE on orders into order_audit, using CURRENT_USER and CURRENT_TIMESTAMP.
  2. Add a BEFORE UPDATE trigger that prevents setting quantity to a negative value (reject with SIGNAL).
  3. Write a statement-level AFTER INSERT trigger that adds a row to bulk_load_summary recording the total inserted rows.
  4. Add WHEN (n.amount > 10000) so a trigger only fires for large orders.
  5. Inspect SYSCAT.TRIGGERS to count how many triggers exist on each table in your schema.

Quick Quiz

Q1. What's the difference between BEFORE and AFTER triggers?

Show answer

BEFORE triggers run before the row change is applied — they can modify the NEW row or reject the operation with SIGNAL. AFTER triggers run after the change is applied — they can read the final state but can't change it. Use BEFORE to validate and rewrite; use AFTER to log and react.

Q2. Why is REFERENCING OLD AS o NEW AS n only partly meaningful for INSERT and DELETE?

Show answer

For INSERT, there's no previous version of the row — OLD doesn't exist. For DELETE, there's no new version — NEW doesn't exist. So an INSERT trigger can reference only NEW, and a DELETE trigger can reference only OLD. UPDATE triggers can reference both.

Q3. What does FOR EACH STATEMENT change vs. FOR EACH ROW?

Show answer

FOR EACH ROW fires the trigger body once per row affected. FOR EACH STATEMENT fires the body once for the whole statement, no matter how many rows are touched. Use FOR EACH ROW for per-row logic (audit, row-level validation). Use FOR EACH STATEMENT (plus NEW TABLE/OLD TABLE) for set-based logic — much faster for bulk operations.

Next Up

Triggers run set-based logic. Next we look at cursors — DB2's row-by-row iteration tool.