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
