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. (DB2)

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#

(Oracle)

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. (DB2)

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#

(Oracle)

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. (DB2)

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#

(Oracle)

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;
/

Related tasks
Creating triggers for custom tables