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 is a trigger that will log the INSERT action in the STAGLOG table after an INSERT action happens on the CATALOG table.

Create trigger STAG0110
AFTER INSERT ON catalog REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE keys SET counter=counter+1 where
tablename='staglog'; INSERT INTO staglog
(stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
SELECT
counter
, CURRENT TIMESTAMP
, 'catalog'
, 'I'
, 1
, 'catalog_id'
, N.catalog_id
, N.catalog_id
FROM keys WHERE tablename='staglog' ;
END#

 

UPDATE trigger example

This is a trigger that will log the UPDATE action in the STAGLOG table after an UPDATE action happens on the CATALOG table.

Create trigger STAG0111
AFTER UPDATE ON catalog REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE keys SET counter=counter+1 where
tablename='staglog'; INSERT INTO staglog
(stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
SELECT
counter
, CURRENT TIMESTAMP
, 'catalog'
, 'U'
, 1
, 'catalog_id'
, O.catalog_id
, N.catalog_id
FROM keys WHERE tablename='staglog' ;
END#

 

DELETE trigger example

This is a trigger that will log the DELETE action in the STAGLOG table after an DELETE action happens on the CATALOG table.

Create trigger STAG0112
AFTER DELETE ON catalog REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE keys SET counter=counter+1 where
tablename='staglog'; INSERT INTO staglog
(stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
)
SELECT
counter
, CURRENT TIMESTAMP
, 'catalog'
, 'D'
, 1
, 'catalog_id'
, O.catalog_id
FROM keys WHERE tablename='staglog' ;
END#

Related concepts

Staging server

Related tasks

Create triggers for custom tables