How to Audit Table using Triggers in Oracle - Example
In Oracle, you can audit changes to a table using triggers. Here's a basic example of how you can create a trigger to audit a table
Step 1: Create an audit table for the table you want to AUDIT.
Here Original table is : bbnl_total_customers
The following statement creates an audit table called bbnl_total_customers_audit with no data and with the same structure as the original table.
create table bbnl_total_customers_audit
as select * from bbnl_total_customers where 1=2;
Add two columns ip_address , audited_on for capturing the ip_address from which the changes have been done and audited_on is the date on which the changes have been done.
alter table bbnl_total_customers_audit
add ( IP_ADDRESS VARCHAR2(15),AUDITED_ON date);
Step 2: Create an Oracle Trigger for auditing
Auditing is enabled for only UPDATE and DELETE.
when the original table is updated a record is inserted in the audit table along with ip address.
when we try to delete any row in the table , it will not allow to delete. It shows the error alert message as "You are not allowed to delete data from this table"
create or replace trigger bbnl_total_customers_trg
before update or delete
on bbnl_total_customers
for each row
begin
if updating then
INSERT INTO bbnl_total_customers_audit
(
ZONE, STATE_CODE, STATE, IP_ADDRESS,AUDITED_ON
)
VALUES
( :OLD.ZONE,
:OLD.STATE_CODE,
:OLD.STATE,
sys_context('userenv','ip_address'),
sysdate );
end if;
if deleting then
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to delete data from this table');
end if;
end;
/
Testing the Trigger with the UPDATE Operation
update bbnl_total_customers_rp set state='Jharkhand' where rownum<2;
And this will insert a row in audit with all old values along with ipadress from which it is updated.
Testing the Trigger with the DELETE Operation
DELETE FROM bbnl_total_customers;
And this will throw an popup alert to the user who is deleting as follows
