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;
Title | Under | Posted on |
---|---|---|
Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
Recent comments
2 years 23 weeks ago
2 years 28 weeks ago
2 years 28 weeks ago
2 years 29 weeks ago
2 years 30 weeks ago
2 years 30 weeks ago
2 years 32 weeks ago
2 years 34 weeks ago
2 years 37 weeks ago
2 years 43 weeks ago