Using DB2 UDB for Linux, Unix, and Windows (LUW) Trigger Information
This section discusses using the DB2 LUW trigger information.
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 following SQL is a sample of the trigger syntax:
CREATE TRIGGER PSO1
AFTER INSERT ON PS_ABSENCE_HIST
REFERENCING NEW AS C_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO PS_AUDIT_ABSENCE_HIST
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);
CREATE TRIGGER PSP1 AFTER DELETE ON PS_ABSENCE_HIST
REFERENCING OLD AS C_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO PS_AUDIT_ABSENCE_HIST
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);
CREATE TRIGGER PSQ1
AFTER UPDATE ON PS_ABSENCE_HIST
REFERENCING NEW AS C_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO PS_AUDIT_ABSENCE_HIST
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 PSR1 AFTER UPDATE ON PS_ABSENCE_HIST
REFERENCING OLD AS C_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO PS_AUDIT_ABSENCE_HIST
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);
These commands might be useful for administering triggers.
List All Triggers in a Database
To list all triggers:
SELECT trigname, trigevent, tabname FROM syscat.triggers
List the Trigger Definition
To list the trigger definition:
SELECT trigname, text FROM syscat.triggers
To Remove a Trigger
To remove a trigger:
DROP trigger TRIGGERNAME
See DB2 Universal Database for Linux, Unix, and Windows SQL Reference.
See DB2 Universal Database for Linux, Unix, and Windows Application Programming and SQL Guide.