How to create Trigger in DB2

Your rating: None Average: 4.5 (4 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

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.