Skip to Main Content
Return to Navigation

Using DB2 UDB for z/OS Trigger Information

This section provides an overview of DB2 z/OS trigger information and discusses:

Understanding DB2 z/OS Trigger Information

The following topics describe the syntax, and commands, involved with DB2 z/OS triggers.

Before the Trigger Audit can be implemented on DB2 z/OS, the trigger statement must be defined.

DB2 z/OS Trigger Syntax

A trigger for each SQL operation type, as in INSERT, UPDATE and DELETE, needs to be defined separately with a different trigger name for a given triggering table. The allowable trigger name length is eight characters long. The following SQL is a sample of the trigger syntax.

--For INSERT operation:
CREATE TRIGGER PSO112 AFTER  INSERT ON PS_ABSENCE_HIST
 REFERENCING NEW AS C_ROW
 FOR EACH ROW MODE DB2SQL
 INSERT INTO PS_AUDIT_ABSENCE
 VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),
CURRENT TIMESTAMP,'A',
C_ROW.EMPLID,
C_ROW.ABSENCE_TYPE,
C_ROW.BEGIN_DT,
C_ROW.RETURN_DT,
C_ROW.DURATION_DAYS,
C_ROW.DURATION_HOURS,
C_ROW.REASON,
C_ROW.PAID_UNPAID,
C_ROW.EMPLOYER_APPROVED);

--For DELETE operation
CREATE TRIGGER PSP112 AFTER DELETE ON PS_ABSENCE_HIST
 REFERENCING OLD AS C_ROW
 FOR EACH ROW MODE DB2SQL
 INSERT INTO PS_AUDIT_ABSENCE
 VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),
CURRENT TIMESTAMP,'D',
C_ROW.EMPLID,
C_ROW.ABSENCE_TYPE,
C_ROW.BEGIN_DT,
C_ROW.RETURN_DT,
C_ROW.DURATION_DAYS,
C_ROW.DURATION_HOURS,
C_ROW.REASON,
C_ROW.PAID_UNPAID,
C_ROW.EMPLOYER_APPROVED);

--For UPDATE operation
CREATE TRIGGER PSQ112 AFTER UPDATE ON PS_ABSENCE_HIST
 REFERENCING NEW AS C_ROW
 FOR EACH ROW  MODE DB2SQL
 INSERT INTO PS_AUDIT_ABSENCE
 VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),
CURRENT TIMESTAMP,'N',
C_ROW.EMPLID,
C_ROW.ABSENCE_TYPE,
C_ROW.BEGIN_DT,
C_ROW.RETURN_DT,
C_ROW.DURATION_DAYS,
C_ROW.DURATION_HOURS,
C_ROW.REASON,
C_ROW.PAID_UNPAID,
C_ROW.EMPLOYER_APPROVED);


CREATE TRIGGER PSR112 AFTER UPDATE ON PS_ABSENCE_HIST
 REFERENCING OLD AS C_ROW
 FOR EACH ROW MODE DB2SQL
 INSERT INTO PS_AUDIT_ABSENCE
 VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),
CURRENT TIMESTAMP,'K',
C_ROW.EMPLID,
C_ROW.ABSENCE_TYPE,
C_ROW.BEGIN_DT,
C_ROW.RETURN_DT,
C_ROW.DURATION_DAYS,
C_ROW.DURATION_HOURS,
C_ROW.REASON,
C_ROW.PAID_UNPAID,
C_ROW.EMPLOYER_APPROVED);

DB2 z/OS Trigger Maintenance

These commands might be useful for administering triggers.

List All Triggers in a Database

To list all triggers:

SELECT name  FROM SYSIBM.SYSTRIGGERS 

List the Trigger Definition

To list the trigger definition:

SELECT text FROM SYSIBM.SYSTRIGGERS WHERE NAME = trigger_name

List Trigger Information

To list the trigger information:

SELECT text FROM SYSIBM.SYSTRIGGERS WHERE NAME = trigger_name

To Remove a Trigger

To remove a trigger:

DROP trigger TRIGGERNAME  restrict

To Modify an Existing Trigger

You can't alter a trigger. You must drop it and recreate it.

See DB2 for z/OS SQL Reference.

See DB2 for z/OS Application Programming and SQL Guide.