467,923 Members | 1,891 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

log table changes with a trigger...

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

Jul 6 '07 #1
  • viewed: 3435
Share:
1 Reply
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_name 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_name
char(50) nullable, a_person_last_name 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_name, a_person_last_name, b_dob, a_dob)
VALUES(OLD_ROW.person_id, OLD_ROW.b_person_ssn,
NEW_ROW.a_person_ssn,
OLD_ROW.b_person_last_name,
NEW_ROW.a_person_last_name,
OLD_ROW.b_dob, NEW_ROW.a_dob);
- Roland
"John Hopfield" <Ho******@freemail.itwrote in message
news:11*********************@c77g2000hse.googlegro ups.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

Jul 6 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by takilroy | last post: by
6 posts views Thread by pg | last post: by
1 post views Thread by Graeme Hinchliffe | last post: by
5 posts views Thread by keith.culpepper | last post: by
7 posts views Thread by Serge Rielau | last post: by
16 posts views Thread by scoots987 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.