How to create External Audit table in oracle?

In my previous articles I have given detailed information about triggers, statement triggers and row triggers as well. While working with row level triggers I have explained you that we are using the row level trigger to create audit tables. The audit tables are important tables to check the multiple operations on table. These are really useful tables in oracle. The oracle has its own audit tables but in this article I would like to give you the external audit table to see the multiple operations on oracle table. This is really very important topic as so many programmers missed to create audit table and facing difficulty when get issue. In this article i would like to give you step by step process of creating external audit tables to capture multiple operations on table with its testing.

Create External Audit tables step by step :

In this section i would like to give you step by step way to create the audit table to capture multiple operations on Employee_cp table.

Step 1 : Create Copy of Employee table

Create table Employee_cp as select * from Employee;

Step 2 : Create External Audit table to capture multiple operations

Create Table Emp_Audit
( EMP_ID NUMBER,
OPERATION VARCHAR2(20),
OLD_SAL NUMBER,
NEW_SAL NUMBER,
OP_DATE DATE,
BY_USER VARCHAR2(100)
);

Step 3 : Creating the trigger for External Audit Table

This is most important step where we can create the trigger. We can create a trigger in such way that we can insert the values in Audit records to maintain it.

Create or replace trigger T_Emp_Audit
after insert or update of salary or delete
on Employee_cp
for each row
begin

/*** IF You are inserting the value in Employee_cp table also insert data in audit table***/
if inserting then
insert into Emp_Audit(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
values (:new.employee_id,’Insertion’,null,:new.salary,sysdate,user);
end if;

/*** IF You are updating the value in Employee_cp table then insert data in audit table***/

if updating then
insert into Emp_Audit(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
values (:old.employee_id,’updating’,:old.salary,:new.salary,sysdate,user);
end if;

/*** IF You are Deleting the value in Employee_cp table then insert data in audit table***/

if deleting then
insert into Emp_Audit(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
values (:old.employee_id,’deleting’,:old.salary,null,sysdate,user);
end if;

end;

Kindly make sure that you are not using the commit or rollback in triggers. It is strictly avoided to use commit and rollback statements in trigger.

Step 4 : Testing the trigger and External audit table data.

Insert Operation :

If you are inserting the record in Employee_Cp table as follows,

insert into Emloyee_cp (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,salary,hire_date,job_id)
values (1,’Amit’,’S’,’complexsql@gmail.com’,100000,sysdate,’BI engineer’);

Commit;

You can check the data in audit table using following statement,

Select *
to_char(OP_DATE,’dd-mm-yyyy hh24:mi:ss’)OP_DATE ,BY_USER
from Emp_Audit
order by OP_DATE;

Update Operation :

update Emloyee_cp
set salary=salary +10
where EMPLOYEE_ID=1;

You can check the audit table,

Select *
to_char(OP_DATE,’dd-mm-yyyy hh24:mi:ss’)OP_DATE ,BY_USER
from Emp_Audit
order by OP_DATE;

Delete Operation :

delete from Emloyee_cp
where EMPLOYEE_ID=1;

You can check delete operation in Audit table,

Select *
to_char(OP_DATE,’dd-mm-yyyy hh24:mi:ss’)OP_DATE ,BY_USER
from Emp_Audit
order by OP_DATE;

These audit tables are important to track multiple transactions. When there is million of data and you need to work on the issue mining the data then these audit tables plays an important roles. I hope you like this article is useful to you. If you like this article or if you have any questions about same kindly comment in comments section.