Re: Table Auditing

From: Don Fraser <don_at_clear.net.nz>
Date: 1997/11/10
Message-ID: <646ll4$475@granny.mac.co.nz>#1/1

Lisa Lewis wrote in message <63qj59$29sq_at_sccat.pgh.wec.com>... >Can anyone offer suggestions on how to do auditing.

I use just one archive table for both update and delete; The live table has user, rec_date and primary key columns The archive has axactly the same structure but does not have a primary key constraint.

Before update or delete, trigger fires and copies the current record from the live table to the archive. For update the live record has new user and rec_date column values.
For delete I also write the old record to the archive but then write another with the user and rec_date columns set for current user and date.

Deleted records are those whose primary keys in the archive table but not in the live. A full record history is maintained.

Example of the trigger code:

Create or replace trigger trigger_name
before update or delete
on live_table
for each row
begin
insert into live_arc select

 :old.LIVE_ID


,:old.LIVE_REC_DATE

,:old.LIVE_USER

,:old.LIVE_DESCRIPTION

,:old.LIVE_SERIALNO

,:old.LIVE_FILENAME

,:old.LIVE_FATHER

,:old.LIVE_MOTHER

,:old.LIVE_DATAMOD

,:old.LIVE_DATATITLE

,:old.LIVE_REGION
,:old.LIVE_LOCK

from dual;
if deleting then
 insert into own_arc select
 :old.LIVE_ID

 ,sysdate
 ,USER
 ,:old.LIVE_DESCRIPTION


,:old.LIVE_SERIALNO

,:old.LIVE_FILENAME

,:old.LIVE_FATHER

,:old.LIVE_MOTHER

,:old.LIVE_DATAMOD

,:old.LIVE_DATATITLE

,:old.LIVE_REGION
,:old.LIVE_LOCK

from dual;
end if;
end; Received on Mon Nov 10 1997 - 00:00:00 CST