log table changes with a trigger...

[John Hopfield]

Hi to all,

Can you make a (simple) example about logging table changes into a
"log" table?
It is possible using triggers?

thank you
JH

[Robo]

Re: log table changes with a trigger...

Hi,

There is actually a number of ways to accomplish this.

Triggers are a very valid way. Using that appraoch here is a possible
design:

For the sake of the example, let's assume you had a table called
dbadmin.person and that table had the following columns with the assumption
that a process couldn't change the primary key of the table (in this
example, the PK is person_id):

dbadmin.person (person_id integer not null, person_ssn char(9) not null with
default, person_last_nam e char(50) not null w/ default, dob date nullable);

Build a 'log' table to keep track of before and after data.

new 'log table' dbadmin.person_ audit (person_id integer not null,
b_person_ssn char(9) nullable, a_person_ssn char(9) nullable,
b_person_last_n ame
char(50) nullable, a_person_last_n ame char(50) nullable,
b_dob date
nullable, a_dob date nullable);


Create a trigger that performs following steps(see SQL guide or application
guide for code examples)

- CREATE TRIGGER DBADMIN.LOGDATA
AFTER update
REFERENCING OLD AS OLD_ROW, NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO DBADMIN.PERSON_ LOG
(person_id, b_person_ssn,
a_person_ssn,b_ person_last_nam e, a_person_last_n ame, b_dob, a_dob)
VALUES(OLD_ROW. person_id, OLD_ROW.b_perso n_ssn,
NEW_ROW.a_perso n_ssn,
OLD_ROW.b_perso n_last_name,
NEW_ROW.a_perso n_last_name,
OLD_ROW.b_dob, NEW_ROW.a_dob);


- Roland
"John Hopfield" <Hopfield@freem ail.itwrote in message
news:1183735567 .782403.63910@c 77g2000hse.goog legroups.com...

Hi to all,
>
Can you make a (simple) example about logging table changes into a
"log" table?
It is possible using triggers?
>
thank you
JH
>