Exemple de déclencheur de transfert

Trois déclencheurs sur chaque table de transfert consignent les actions INSERT, UPDATE et DELETE qui s'y produisent. Le journal est écrit dans la table STAGLOG par les déclencheurs.

Exemple de déclencheur INSERT

Ce déclencheur consigne les actions INSERT dans la table STAGLOG après une action INSERT sur la table CATGROUP.


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

Exemple de déclencheur UPDATE

Ce déclencheur consigne les actions UPDATE dans la table STAGLOG après une action UPDATE sur la table CATGROUP.


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

Exemple de déclencheur DELETE

Ce déclencheur consigne les actions DELETE dans la table STAGLOG après une action DELETE sur la table CATGROUP.


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