How to create Trigger in DB2

Your rating: None Average: 4.9 (25 votes)

Follow the below steps to create the trigger for the tables
1.Create the Sequence

Syntax
Create Sequence G02491P.TRIGGER_SEQ1 as INTEGER 
 Start With 1 
 Increment by 1 
 MinValue 1 
 MaxValue 2147483647  
 Cache 24

2.Create the function

Syntax
Create Function G02491P.get_next () 
	Returns INTEGER 
	Specific get_next 
	Language SQL 
	Deterministic  
	External Action 
	Reads SQL Data     
	
BEGIN ATOMIC DECLARE I INTEGER; SET I = (NEXT VALUE FOR TRIGGER_SEQ1);
RETURN I;END;

3.Create the Trigger table where the values need to be saved

Eg:
Create Table G02491P.TG_BEN_PROG_PARTIC(
TG_ID                         INTEGER                       NOT NULL,
TG_ACTION_DT                  TIMESTAMP                	    NOT NULL,
TG_STATUS_CD                  CHARACTER(10)       	    NOT NULL,
TG_USER_ID                    CHARACTER(30)		    NOT NULL,
TG_ACTION_TYPE		      CHARACTER(10)		    NOT NULL,

EMPLID                        CHARACTER(11)                 NOT NULL,
EMPL_RCD                      SMALLINT                      NOT NULL,
COBRA_EVENT_ID                SMALLINT                      NOT NULL,
EFFDT                         DATE                          NOT NULL,
BENEFIT_PROGRAM               CHARACTER(3)                  NOT NULL)
;

4.Create Index for the Trigger table

Syntax
CREATE  INDEX "G02491P"."TG_BEN_PROG_PARTIC_01"
	ON "G02491P"."TG_BEN_PROG_PARTIC" 
	("TG_ID");

5.Create Constraints for the trigger table

Syntax
ALTER TABLE "G02491P"."TG_BEN_PROG_PARTIC"
	ADD CONSTRAINT "TG_BEN_PROG_PARTIC_1" PRIMARY KEY
   ("TG_ID",
    "TG_ACTION_DT",
    "TG_STATUS_CD",
    "TG_USER_ID",
    "TG_ACTION_TYPE"
   );

6.Create Trigger

Syntax
------------------------------------------------------------------------
-- Create Trigger G02491P.H_IN_TG_BEN_PROG_PARTIC
--Trigger for Insert
------------------------------------------------------------------------
Create Trigger G02491P.H_IN_TG_BEN_PROG_PARTIC 
	After Insert on G02491P.PS_BEN_PROG_PARTIC -->Main table name
	Referencing new as NEW 
	for each Row mode db2sql 
	BEGIN ATOMIC
declare v_id integer default 0;
set v_id = g02491p.get_next( );
insert into G02491P.TG_BEN_PROG_PARTIC (
TG_ID, TG_ACTION_DT, TG_ACTION_TYPE, TG_STATUS_CD, TG_USER_ID,
EMPLID, EMPL_RCD, COBRA_EVENT_ID, EFFDT, BENEFIT_PROGRAM
)
VALUES     (
v_id, CURRENT TIMESTAMP, 'INSERT', 'NEW', USER,
NEW.EMPLID, NEW.EMPL_RCD, NEW.COBRA_EVENT_ID, NEW.EFFDT, NEW.BENEFIT_PROGRAM
); 
END;

------------------------------------------------------------------------
-- Create Trigger G02491P.H_UP_TG_BEN_PROG_PARTIC
--Trigger for Update
------------------------------------------------------------------------
Create Trigger G02491P.H_UP_TG_BEN_PROG_PARTIC 
	After Update on G02491P.PS_BEN_PROG_PARTIC 
	Referencing old as OLD new as NEW 
	for each Row mode db2sql 
BEGIN ATOMIC
declare v_id integer default 0;
set v_id = g02491p.get_next( );
insert into G02491P.TG_BEN_PROG_PARTIC (
TG_ID, TG_ACTION_DT, TG_ACTION_TYPE, TG_STATUS_CD, TG_USER_ID,
EMPLID, EMPL_RCD, COBRA_EVENT_ID, EFFDT, BENEFIT_PROGRAM
)
VALUES     (
v_id, CURRENT TIMESTAMP, 'Update1', 'NEW', USER,
OLD.EMPLID, OLD.EMPL_RCD, OLD.COBRA_EVENT_ID, OLD.EFFDT, OLD.BENEFIT_PROGRAM
); 
insert into G02491P.TG_BEN_PROG_PARTIC (
TG_ID, TG_ACTION_DT, TG_ACTION_TYPE, TG_STATUS_CD, TG_USER_ID,
EMPLID, EMPL_RCD, COBRA_EVENT_ID, EFFDT, BENEFIT_PROGRAM
)
VALUES     (
v_id, CURRENT TIMESTAMP, 'Update2', 'NEW', USER,
NEW.EMPLID, NEW.EMPL_RCD, NEW.COBRA_EVENT_ID, NEW.EFFDT, NEW.BENEFIT_PROGRAM
);
END;

-------------------------------------------------------------------------
-- Create Trigger G02491P.H_DL_TG_BEN_PROG_PARTIC
--Trigger for Delete
-------------------------------------------------------------------------
Create Trigger G02491P.H_DL_TG_BEN_PROG_PARTIC 
	After Delete on G02491P.PS_BEN_PROG_PARTIC 
	Referencing old as OLD 
	for each Row mode db2sql 
BEGIN ATOMIC
declare v_id integer default 0;
set v_id = g02491p.get_next( );
insert into G02491P.TG_BEN_PROG_PARTIC (
TG_ID, TG_ACTION_DT, TG_ACTION_TYPE, TG_STATUS_CD, TG_USER_ID,
EMPLID, EMPL_RCD, COBRA_EVENT_ID, EFFDT, BENEFIT_PROGRAM
)
VALUES     (
v_id, CURRENT TIMESTAMP, 'DELETE', 'NEW', USER,
OLD.EMPLID, OLD.EMPL_RCD, OLD.COBRA_EVENT_ID, OLD.EFFDT, OLD.BENEFIT_PROGRAM
); 
END;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
4 + 8 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.