A Sample Database Trigger for Auditing

Suppose there is a table Event. All the updates or deletes on this table need to be auditted. We can use trigger for this purpose.
We create the Event_His table which has all the columns in the Event table. We also add three new columns to indicate the reason, the person and the date when a row in the Event table is changed.

Table Event

event_seq
created_by
created_dt
updated_by
updated_dt
lock_version
type
name
description
start_date
end_date
status

Table Event_His

event_seq
created_by
created_dt
updated_by
updated_dt
lock_version
type
name
description
start_date
end_date
status
reason
actioner
action_date

The Package Used by the Trigger:

CREATE OR REPLACE PACKAGE Auditpackage AS
Updater VARCHAR2(10);
PROCEDURE Set_updater(person  in VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY Auditpackage AS
PROCEDURE Set_updater(person  in VARCHAR2)  is
BEGIN
Updater   := person;    
END;
END;

Before deleting a row, one needs to first call Auditpackage.Set_updater(?) to set who will delete the record. Then the trigger will use the saved value when creating a new row in the history table.

The trigger is the following:

BEGIN
IF DELETING  THEN

insert into Event_His values
(Event_his_seqgen.NEXTVAL,:old.Event_seq, :old.created_by, :old.created_dt,
:old.updated_by, :old.updated_dt, :old.lock_version,
:old.type, :old.name, :old.description, :old.start_date, :old.end_date, :old.status,
'delete', Auditpackage.updater, sysdate);

ELSE

insert into Event_His values
(Event_his_seqgen.NEXTVAL,:old.Event_seq, :old.created_by, :old.created_dt,
:old.updated_by, :old.updated_dt, :old.lock_version,
:old.type, :old.name, :old.description, :old.start_date, :old.end_date, :old.status,
'update', :new.updated_by, sysdate);

END IF;

END;

As an option, the package variable Updater can be reset to null after a record is updated or deleted in the Event table. For this purpose, the following trigger is needed.

CREATE OR REPLACE TRIGGER Audit_event_reset
AFTER DELETE OR UPDATE ON Event
BEGIN
Auditpackage.Set_updater(NULL);
END;

Notice that the previous two triggers are both fired by the same type of SQL statement. However, the AFTER row trigger is fired once for each row of the table affected by the triggering statement, while the AFTER statement trigger is fired only once after the triggering statement execution is completed.

Reference

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1007170