Skip to Main Content
Return to Navigation

Using DB2 UDB for Linux, Unix, and Windows (LUW) Trigger Information

This section discusses :

DB2 LUW 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 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);

DB2 LUW Trigger Maintenance

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.