Re: OLD copy of record---AUDIT TRAIL--How To?

Hi,

NNOOR wrote in message <72f98q$q25_at_journal.concentric.net>...
>
>We have the need to keep an audit trail of the data that exists
>in a few tables. That means making a copy of the record before
>any changes/deletes are commited.
>
>Question: what is the best way to do it?

The way we found is to maintain a second copy of your table (say called <table name>_AUDIT and this will have several extra fields (say AUDIT_SEQN_NUM, AUDIT_USER, AUDIT_DATE_TIME, AUDIT_TYPE) and these will be maintained by a trigger for each such table.

The sequence numbers are maintained by a stored procedure (so that we have no gaps - there is one number for each table).

The types are:

'B' - Update before picture
'A' - Update after picture
'I' - Insert
'D' - Delete

This is why sequence numbers are important - the before and after should have the same sequence number so they can be tied together.

This is all done on an after trigger, referencing :OLD and :NEW values.

This seems to work very well, but there is the overhead of maintaining the tables and triggers.

Hope this helps,

Paul Received on Sun Nov 15 1998 - 00:00:00 CST