Db2 Trigger Guide: BEFORE, AFTER, INSTEAD OF, OLD and NEW Values



Triggers run automatically when table data changes.

A COBOL program can update one order row and accidentally fire more SQL than the programmer sees in the source. That extra work may be a Db2 trigger. A trigger is stored in Db2 and runs automatically when an INSERT, UPDATE, or DELETE event occurs on its subject table or view.

Triggers are useful for small database-side rules such as audit rows, derived values, and controlled view updates. They are risky when they hide large business processes, call unexpected logic, or make a simple update hard to explain during a production incident.

What a Db2 trigger does

A trigger belongs to a specific table or view. When the triggering SQL event happens, Db2 checks the trigger definition and runs the triggered action. The application does not call the trigger directly.

Trigger part	Meaning
Activation time	BEFORE, AFTER, or INSTEAD OF
Event	INSERT, UPDATE, or DELETE
Subject	The table or view where the event occurs
Transition values	Old and new row values available to trigger logic
Triggered action	The SQL action Db2 performs when the trigger fires

Trigger types by event

INSERT trigger

An insert trigger fires when a row is inserted. It is often used to write an audit row, populate a related control table, or enforce a database-side rule that must apply no matter which program inserts the row.

UPDATE trigger

An update trigger fires when rows are changed. For high-volume tables, be careful with update triggers because one batch update statement can affect thousands of rows and run trigger logic for each qualifying row.

DELETE trigger

A delete trigger fires when rows are deleted. It can record the deleted key values or prevent accidental loss through a controlled rule. Do not use it as a substitute for a tested recovery plan.

INSTEAD OF trigger

An INSTEAD OF trigger is commonly associated with a view. It lets Db2 run trigger logic instead of the original insert, update, or delete against the view. This can make a view updateable in a controlled way.

BEFORE, AFTER, and INSTEAD OF

Activation time	Typical use
BEFORE	Check or adjust values before the row change is completed.
AFTER	Write audit rows or maintain related summary information after the row change.
INSTEAD OF	Replace an operation against a view with controlled SQL logic.

Example: audit an order status change

This example records an audit row when an order status changes. It uses old and new transition variables so the trigger can compare the previous value with the new value.

CREATE TRIGGER APP.TRG_ORDER_STATUS_AUD
  AFTER UPDATE OF ORDER_STATUS ON APP.ORDERS
  REFERENCING OLD AS O NEW AS N
  FOR EACH ROW MODE DB2SQL
  WHEN (O.ORDER_STATUS <> N.ORDER_STATUS)
    INSERT INTO APP.ORDER_STATUS_AUDIT
      (ORDER_NO, OLD_STATUS, NEW_STATUS, CHANGE_TS)
    VALUES
      (N.ORDER_NO, O.ORDER_STATUS, N.ORDER_STATUS, CURRENT TIMESTAMP);

A COBOL program that updates APP.ORDERS will not call this trigger by name. Db2 fires it because the update matches the trigger event and the WHEN condition is true.

Trigger versus stored procedure

Feature	Trigger	Stored procedure
How it starts	Automatically from a table or view event	Explicitly called with CALL
Attached to	One subject table or view	A procedure name and schema
Best fit	Small automatic rule tied to data change	Named business operation or service routine
Visibility in COBOL	Not visible as a direct call in program source	Visible when the program issues CALL procedure

Production cautions

• Document triggers near the table definition so application teams know they exist.
• Keep trigger actions small; avoid turning one row update into a hidden batch process.
• Check trigger effects during LOAD, data repair, and conversion work.
• Review authorization for the trigger owner and objects touched by the triggered action.
• Use EXPLAIN and test data volume when trigger SQL touches large tables.
• Include triggers in incident checks when SQL row counts or audit rows look unexpected.

Related DB2 topics

Triggers connect to Db2 Stored Procedures, Db2 Catalog, Db2 SQLCODE and SQLSTATE, Db2 SQL Execution Validation, and Db2 Utilities.

FAQ

Can a COBOL program call a Db2 trigger directly?

No. A trigger fires automatically when its table or view event occurs. COBOL code can cause the event with SQL, but it does not call the trigger by name.

Are triggers good for audit tables?

They can be, especially when every application path must write the same audit row. Keep the audit trigger small and test it with realistic update volumes.

Can a trigger hurt performance?

Yes. Trigger SQL runs as part of the data change path. A trigger that reads or updates large tables can make a simple insert, update, or delete much slower.

Before adding a trigger, ask one hard question: should this rule be automatic for every data change, even when the change comes from a batch job, utility process, or emergency repair?