Staging trigger example

There are three triggers on each staging table to log the INSERT, UPDATE, and DELETE actions that happens on it. The log is written in the STAGLOG table by the triggers.

INSERT trigger example

This trigger logs INSERT actions in the STAGLOG table after an INSERT action happens on the CATGROUP table.

Create  trigger STAG0098
               AFTER INSERT ON catgroup REFERENCING NEW AS N
               FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
     INSERT INTO staglog
     (  stgrfnbr
     ,  stgstmp
     ,  stgtable
     ,  stgop
     ,  stgmenbr
     ,  stgkey1name
     ,  stgokey1
     ,  stgnkey1
     )
     VALUES (
         NEXTVAL FOR STAGESEQ
      ,  CURRENT TIMESTAMP
      ,  'catgroup'
      ,  'I'
      ,  1
      ,  'catgroup_id'
      ,  N.catgroup_id
      ,  N.catgroup_id
     );
               END#

Create or replace trigger ISTG_catgroup
               AFTER INSERT ON catgroup REFERENCING NEW AS N
               FOR EACH ROW

               BEGIN 
                

                INSERT INTO staglog   
                   (stgrfnbr, stgstmp, stgtable, stgop, 
                    stgmenbrname, stgmenbr, 
                    stgpkeyname, stgpkey, 
                    stgkey1name, stgkey2name, stgkey3name, stgkey4name, 
                    stgokey1, stgokey2, stgokey3, stgokey4, 
                    stgnkey1, stgnkey2, stgnkey3 , stgnkey4,
                    stgprocessed, stgreserved1)    
                    VALUES ( STAGESEQ.NEXTVAL,
                   sysdate, 'catgroup', 'I',
                   NULL, 1,
                   NULL, NULL,
                   'catgroup_id', NULL, NULL, NULL,
                   :N.catgroup_id, NULL, NULL, NULL,
                   :N.catgroup_id, NULL, NULL, NULL,
                   0, 0 );
               END;
/

UPDATE trigger example

This trigger logs UPDATE actions in the STAGLOG table after an UPDATE action happens on the CATGROUP table.

Create  trigger STAG0099
               AFTER UPDATE ON catgroup REFERENCING NEW AS N OLD AS O
               FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
     INSERT INTO staglog
     (  stgrfnbr
     ,  stgstmp
     ,  stgtable
     ,  stgop
     ,  stgmenbr
     ,  stgkey1name
     ,  stgokey1
     ,  stgnkey1
     )
     VALUES (
         NEXTVAL FOR STAGESEQ
      ,  CURRENT TIMESTAMP
      ,  'catgroup'
      ,  'U'
      ,  1
      ,  'catgroup_id'
      ,  O.catgroup_id
      ,  N.catgroup_id
     );
               END#

Create or replace trigger USTG_catgroup
               AFTER UPDATE ON catgroup REFERENCING NEW AS N OLD AS O
               FOR EACH ROW

               BEGIN 
                
                
                INSERT INTO staglog   
                   (stgrfnbr, stgstmp, stgtable, stgop, 
                    stgmenbrname, stgmenbr, 
                    stgpkeyname, stgpkey, 
                    stgkey1name, stgkey2name, stgkey3name, stgkey4name, 
                    stgokey1, stgokey2, stgokey3, stgokey4, 
                    stgnkey1, stgnkey2, stgnkey3 , stgnkey4,
                    stgprocessed, stgreserved1)    
                    VALUES (STAGESEQ.NEXTVAL,
                   sysdate, 'catgroup', 'U',
                   NULL, 1,
                   NULL, NULL,
                   'catgroup_id', NULL, NULL, NULL,
                   :O.catgroup_id, NULL, NULL, NULL,
                   :N.catgroup_id, NULL, NULL, NULL,
                   0, 0 );
               END;
/

DELETE trigger example

This trigger logs DELETE actions in the STAGLOG table after a DELETE action happens on the CATGROUP table.

Create  trigger STAG0100
               AFTER DELETE ON catgroup REFERENCING OLD AS O
               FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
     INSERT INTO staglog
     (  stgrfnbr
     ,  stgstmp
     ,  stgtable
     ,  stgop
     ,  stgmenbr
     ,  stgkey1name
     ,  stgokey1
     )
     VALUES (
         NEXTVAL FOR STAGESEQ
      ,  CURRENT TIMESTAMP
      ,  'catgroup'
      ,  'D'
      ,  1
      ,  'catgroup_id'
      ,  O.catgroup_id
     );
               END#

Create or replace trigger DSTG_catgroup
               AFTER DELETE ON catgroup REFERENCING OLD AS O
               FOR EACH ROW
 
               BEGIN 
                
                
                INSERT INTO staglog   
                   (stgrfnbr, stgstmp, stgtable, stgop, 
                    stgmenbrname, stgmenbr, 
                    stgpkeyname, stgpkey, 
                    stgkey1name, stgkey2name, stgkey3name, stgkey4name, 
                    stgokey1, stgokey2, stgokey3, stgokey4, 
                    stgnkey1, stgnkey2, stgnkey3 , stgnkey4,
                    stgprocessed, stgreserved1)    
                    VALUES ( STAGESEQ.NEXTVAL,
                   sysdate, 'catgroup', 'D',
                   NULL, 1,
                   NULL, NULL,
                   'catgroup_id', NULL, NULL, NULL,
                   :O.catgroup_id, NULL, NULL, NULL,
                   NULL, NULL, NULL, NULL,
                   0, 0 );
               END;
/