DB2 trigger types: BEFORE, AFTER, and INSTEAD OF

CREATE TRIGGER

CREATE TRIGGER names the trigger, the activation time, the event (INSERT, UPDATE, or DELETE), the subject table or view, optional REFERENCING names, granularity (FOR EACH ROW or FOR EACH STATEMENT), an optional WHEN condition, and a body.

1
2
3
4
5
6
7
CREATE TRIGGER HR.NEW_HIRE
  AFTER INSERT ON HR.EMPLOYEE
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    UPDATE HR.COMPANY_STATS
       SET NBEMP = NBEMP + 1;
  END;

MODE DB2SQL appears on basic triggers. The body runs with the owner’s privileges, not the end user’s. The user who INSERTs does not need authority on every object the trigger touches; the trigger owner does.

Creating a trigger on a table that already has rows does not fire the trigger for those rows. Old data can violate a rule the trigger would have enforced. Pair triggers with CHECK/RI for data that must always be true, or run a one-time cleanup UPDATE.

BEFORE triggers

BEFORE (often written NO CASCADE BEFORE for compatibility) means Db2 runs the trigger before it applies the insert, update, or delete to the subject table. BEFORE triggers are for:

• Assigning or fixing NEW column values (defaulting, uppercasing, computing a code)
• Rejecting a change with a SIGNAL SQLSTATE or a failing WHEN/VALUES call
• Reading other tables to validate (WHEN subquery)—with restrictions on selecting the subject table

1
2
3
4
5
6
7
CREATE TRIGGER HR.EMP_UPPER
  NO CASCADE BEFORE INSERT ON HR.EMPLOYEE
  REFERENCING NEW AS N
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET N.LASTNAME = UPPER(N.LASTNAME);
  END;

The triggered action of a BEFORE trigger must not contain INSERT, UPDATE, DELETE, MERGE, TRUNCATE, or REFRESH TABLE that would change data and activate other triggers. That is the NO CASCADE idea. You change the incoming row by assigning to NEW transition variables, not by UPDATE subject-table.

BEFORE triggers are FOR EACH ROW only. FOR EACH STATEMENT is not supported for BEFORE. They are defined on tables, not views.

AFTER triggers

AFTER means Db2 has already applied the change to the subject table. AFTER triggers maintain other tables, write audit rows, call UDFs, or increment counters. They can contain INSERT/UPDATE/DELETE, which can activate more triggers (cascading, next pages).

1
2
3
4
5
6
7
8
CREATE TRIGGER HR.EMP_AUDIT
  AFTER UPDATE ON HR.EMPLOYEE
  REFERENCING OLD AS O NEW AS N
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    INSERT INTO HR.EMP_AUDIT (EMPNO, OLD_SAL, NEW_SAL, CHG_TS)
      VALUES (N.EMPNO, O.SALARY, N.SALARY, CURRENT TIMESTAMP);
  END;

AFTER must not be specified with a view-name. Constraints (except RESTRICT delete) are effectively checked after the operation and associated triggers, per IBM’s trigger concepts. The triggering statement and its triggers all complete or all back out.

AFTER is the usual home for statement-level triggers (FOR EACH STATEMENT) that look at NEW TABLE / OLD TABLE as a set.

INSTEAD OF triggers

INSTEAD OF is defined on a view. The view might be read-only as a base object (join, DISTINCT). The trigger body replaces the insert, update, or delete against the view. You write the base-table DML yourself.

1
2
3
4
5
6
7
8
CREATE TRIGGER HR.V_EMP_INS
  INSTEAD OF INSERT ON HR.V_EMP_ACTIVE
  REFERENCING NEW AS N
  FOR EACH ROW
  BEGIN ATOMIC
    INSERT INTO HR.EMPLOYEE (EMPNO, LASTNAME, WORKDEPT, STATUS)
      VALUES (N.EMPNO, N.LASTNAME, N.WORKDEPT, 'A');
  END;

Only one INSTEAD OF trigger is allowed for each operation type on a given view (one for INSERT, one for UPDATE, one for DELETE). AFTER/BEFORE are not used on that view for the same purpose. INSTEAD OF is how shops present a simple view to applications while keeping a more complex physical design.

Basic triggers and advanced triggers

Db2 for z/OS supports basic and advanced triggers. Basic triggers are the original CREATE TRIGGER with BEGIN ATOMIC and MODE DB2SQL. Advanced triggers are SQL PL objects: they have versions (default V1), can be issued with OR REPLACE, and are maintained with ALTER TRIGGER ADD VERSION, ACTIVATE VERSION, and related clauses. Advanced bodies can use richer control statements.

CREATE TRIGGER (basic) can be embedded in an application program. Advanced CREATE TRIGGER is typically issued interactively or from a script; both have DYNAMICRULES restrictions when prepared dynamically. Assignment to transition variables and how errors surface can differ—read IBM’s “behavioral differences” table when you convert a basic trigger to advanced.

Prefer advanced triggers for new SQL PL work and versioned production changes. Prefer basic triggers when you are matching decades of shop samples and MODE DB2SQL is the local standard. Do not mix mental models: an advanced VERSION is not a basic DROP and CREATE, even if the SQL looks similar.

DROP TRIGGER and ALTER TRIGGER

1
2
3
4
5
6
DROP TRIGGER HR.NEW_HIRE;
 
-- Advanced: add or activate a version (syntax abbreviated)
ALTER TRIGGER HR.EMP_UPPER
  ADD VERSION V2
  ... ;

DROP TRIGGER removes the trigger. The table or view remains. Packages that referenced the trigger are invalidated. DROP TABLE also drops triggers on that table.

ALTER TRIGGER applies to advanced triggers (versions, comments, debug, activate). Basic triggers are typically dropped and recreated. Recreating a basic trigger changes its creation timestamp, which changes activation order when several triggers share the same event—covered on the design page.

Privilege: CREATE TRIGGER needs TRIGGER privilege (or ownership/SYSADM) on the subject table, plus privileges the body uses. REFERENCING also requires SELECT on the subject. The trigger owner should be a controlled ID, not each developer’s TSO user, so DROP and ALTER stay auditable.

Triggers fire for SQL INSERT/UPDATE/DELETE, including those caused by CASCADE and by other triggers. They do not fire for every utility the same way: LOAD, REPAIR, and some mass utilities have documented exceptions. Do not assume a LOAD will run your AFTER INSERT trigger.

Subject objects differ by type. BEFORE and AFTER attach to a base table (not a catalog table, not a declared global temporary table in the ways shops expect for production audit). INSTEAD OF attaches to a view. You cannot put a BEFORE INSERT on a view to “fix NEW values” and also expect Db2 to write the view; that is INSTEAD OF’s job. Clone tables, archive tables, and temporal history tables have extra rules: read the SQL Reference before you CREATE TRIGGER on a table that already has VERSIONING or a clone.

A basic trigger body is a single BEGIN ATOMIC compound statement. Allowed statements are a short list: assignment to NEW (BEFORE), INSERT/UPDATE/DELETE (AFTER), SIGNAL, GET DIAGNOSTICS in some contexts, and calls to functions. You do not DECLARE CURSOR and loop in a basic trigger. That is why shops moved heavy logic to advanced SQL PL triggers or to a native SQL procedure the trigger calls. Calling a procedure from a trigger still runs under the trigger owner’s privileges and still counts toward nesting if the procedure changes data that fires more triggers.

Multiple BEFORE INSERT triggers on the same table all see NEW, and they run in creation order. The second trigger sees assignments the first trigger already made. That is useful (one trigger uppercases, another fills a default code) and fragile (DROP/CREATE the first trigger and it now runs last). Document create order in the same DDL member. Advanced versions let you keep the object name stable while you change the body, which avoids some of that timestamp shuffle—but two different trigger names still order by create time.

Error handling: a SIGNAL SQLSTATE in the body fails the triggering statement. The application sees a negative SQLCODE (often -438 for SIGNAL, or a trigger-specific code that names the trigger). AFTER triggers that INSERT into an audit table can fail on -803 if the audit primary key collides; that failure undoes the original EMPLOYEE change too. Design audit keys (timestamp plus EMPNO, or IDENTITY) so the trigger is not a uniqueness trap. Do not catch errors by writing a second trigger that “fixes” the first; fix the keys.

When you DROP TRIGGER, packages of programs that never mentioned the trigger can still invalidate if Db2 associated the DML with that trigger. Plan DROP/CREATE of production triggers like a bind: a quiet window, a fallback member, and a test INSERT that proves the new body ran. ALTER TRIGGER ADD VERSION on advanced triggers is the less violent path when you have adopted advanced objects.

Explain It Like I'm Five

BEFORE is a teacher who checks your homework and fixes the spelling before it goes in the grade book. AFTER is a clerk who writes “one more student” on a wall chart after the grade is recorded. INSTEAD OF is a translator: you hand a note to a window (the view), and the translator writes the real letters into the real books instead of stuffing the note through the glass. Basic and advanced are two kinds of pencils; advanced pencils can have version numbers so you can switch to a new pencil without throwing the desk away.

Exercises

1. Write a BEFORE INSERT trigger that UPPER()s LASTNAME using NEW.
2. Write an AFTER INSERT trigger that adds 1 to a COUNTER table.
3. Why can that counter update not live in a BEFORE trigger?
4. Where is INSTEAD OF legal, and how many INSERT INSTEAD OF triggers per view?
5. DROP TRIGGER for HR.NEW_HIRE.