Working with Audit Logs (Oracle)

Some sites require the database to track the last user’s changes to a database table. Others will require the database to log all changes to the table. These tasks are best accomplished by one or more triggers on the database table. In this way, changes made by applications other than ARCHIBUS or changes made from ARCHIBUS Web Central are also appropriately logged. Below are the steps for establishing these two triggers.

Note -- To use these triggers, your site must implement database-level security; that is, your users must log into the database with the user name that you would like included in your log.

How to Log the Last Database Change

To establish last change or insertion logging on a table, complete the following steps.

  1. Create two new fields in the table you wish to track:

modified_on_date
modified_by_username

  1. Alter the last_update and last_insert triggers, replacing the "bl" table name with the name of your table. The code for these triggers is detailed below.

Last_Update.sp
create or replace trigger last_update
after update on bl
for each row
begin
:new.modified_on_date := sysdate;
:new.modified_by_username := user;
end;
/
Last_Ins.sp
create or replace trigger last_insert
after insert on bl
for each row
begin
:new.modified_on_date := sysdate;
:new.modified_by_username := user;
end;
/

  1. Add these new triggers to your database.

How to Keep a Running Log of Changes

To establish a running log on all tables:

  1. Create an audit_log table with the following fields:

modified_by_username
modified_on_date
modified_at_time
modification_type

One way to do so is to use the create_log.sql script provided below.

create table audit_log
(table_name char(64) not null,
modified_by_username char(64) not null,
modified_on_date date not null,
modified_at_time time not null,
modification_type char(16) null );

  1. Alter the change_log trigger, replacing the "bl" table name with the name of your table. The code for this trigger is below.

create or replace trigger change_log
after insert or update or delete on bl
for each row
begin
insert into audit_log (table_name, modified_by_username, modified_on_date)
select "bl", user, sysdate from dual;
end;
/

  1. Add this trigger to your database.