Ejemplo de desencadenante de transición

Hay tres desencadenantes en cada tabla de transición para anotar las acciones INSERT, UPDATE y DELETE que se produzcan en ellas. Los desencadenantes graban el archivo de anotaciones en la tabla STAGLOG.

Ejemplo de desencadenante INSERT

Este desencandenante anota las acciones INSERT en la tabla STAGLOG después de que se produzca una acción INSERT en la tabla 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;
/

Ejemplo de desencadenante UPDATE

Este desencandenante anota una acción UPDATE en la tabla STAGLOG después de que se produzca una acción UPDATE en la tabla 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;
/

Ejemplo de desencadenante DELETE

Este desencandenante anota las acciones DELETE en al tabla STAGLOG después de que se produzca una acción DELETE en la tabla 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;
/