Using DB2 UDB for Linux, Unix, and Windows (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.

SELECT trigname, trigevent, tabname FROM syscat.triggers

SELECT trigname, text FROM syscat.triggers

DROP trigger TRIGGERNAME