By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

log table changes with a trigger...

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.