Audit Table using Triggers in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

How to Audit Table using Triggers in Oracle with Examples

In this article, I am going to discuss How to Create an Audit table using Triggers in Oracle with Examples. Please read our previous article where we discussed Row Level Trigger Examples in Oracle. We will learn many techniques of how to create auditing tables using triggers. We will discuss this in a step-by-step process so that you can easily understand this.

Step 1: Create a Table and Audit Table.

Let us create a table named COPY_EMP where all the data is stored. We will be creating the table from the data of the Employees table. But before that, if the table is already created. Let us drop the table COPY_EMP by executing the below SQL Statement.

drop table copy_emp;

So, the table is dropped. Let us go ahead and create the copy_emp table by executing the below SQL Statement.

Create table copy_emp as select * from employees;

So, the table is created. Let us check the table and see if the data Is present or not by executing the below SQL Statement.

select * from copy_emp;

So, data is present in the table copy_emp. Let us create an audit table named EMP_SALARY_AUDIT. We will use this table to store the changes that were made in the table copy_emp. So, in case any insert operation or delete operation is performed in the table copy_emp then the value is stored in the table EMP_SALARY_AUDIT. Even if there is a delete operation then also the information will be stored in the audit table. This is the main usage of the audit table. Let us see create the table EMP_SALARY_AUDIT by using the below SQL Script.

CREATE TABLE EMP_SALARY_AUDIT
( 
  EMP_ID NUMBER
  OPERATION VARCHAR2(100),
  OLD_SAL NUMBER,
  NEW_SAL NUMBER,
  OP_DATE DATE,
  BY_USER VARCHAR2(100)
);

From the above SQL Statement, we can see the data types as EMP_ID as a number and operation (insert, delete, update) as VARCHAR2(100) and OLD_SAL (old salary) as a number, and NEW_SAL (new salary) as a number and OP_DATE (operation date) as the date and BY_USER (by which user the operation is performed) as VARCHAR2(100). We will store the OLD_SAL and NEW_SAL in case of an update. Let us create the table.

So, the table EMP_SALARY_AUDIT is created. This is a very simple table. But, in real-life scenarios, there will be more columns as per the business requirements.

Step 2: Create an Oracle Trigger Linking these Tables

Let’s go ahead and create a trigger where the trigger should link between the tables COPY_EMP and EMP_SALARY_AUDIT by executing the below SQL Statement.

create or replace trigger emp_copy_sal_audit
after insert or update of salary or delete
on copy_emp
for each row
begin
  if inserting then
  insert into EMP_SALARY_AUDIT(EMP_ID, OPERATION,OLD_SAL, NEW_SAL, OP_DATE, BY_USER)
  values (:new.employee_id,'Inserting',null,:new.salary,sysdate,user);
  end if;
  
  if updating then
  insert into EMP_SALARY_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 deleting then
  insert into EMP_SALARY_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;

This is a very simple trigger statement. If the insert operation is executed on the table COPY_EMP then make an entry with the details of the employee_id, operation new value, time, and user details in the EMP_SALARY_AUDIT table.

We can see the timing is mentioned as after and the events are insert, update, or delete of column salary in the table COPY_EMP. This is a row-level trigger, so we have mentioned for each row. We cannot mention the timing as before because the operation should be succeeded on the table COPY_EMP then only the details should be inserted in the table EMP_SALARY_AUDIT.

From the if condition we can understand that if the inserting operation is executed then fill the table EMP_SALARY_AUDIT with the insert details. If deleting operation is executed then fill the table EMP_SALARY_AUDIT with the delete details and the same with the update operation.

From the insert statement we can see the EMP_ID is new.employee_id as the insert will be a new record and the operation is mentioned as “inserting” and OLD_SAL is null because the insert is a new record. NEW_SAL will be filled with the new salary and OP_DATE is mentioned with the sysdate and user with the username.

From the update statement we can see the EMP_ID is old.employee_id as this is updating and the employee_id is already stored in the table and taking the old EMP_ID. The operation is mentioned as “updating” and OLD_SAL is mentioned with the old salary. NEW_SAL will be filled with the new salary and OP_DATE is mentioned with the sysdate and user with the username.

From the delete statement, it’s very simple as we can see the EMP_ID is old.employee_id as this is deleting and the employee_id is already stored in the table and taking the old EMP_ID. The operation is mentioned as “deleting” and OLD_SAL is mentioned with the old salary. NEW_SAL will be null because we are deleting and we are not entering the new salary details and OP_DATE is mentioned with the sysdate and user with the username. Let us go ahead and execute and compile this trigger

So, the trigger is compiled. We do not use commit or rollback in triggers. The commit or rollback should be only the main transactions (DML or DDL). We will get an error if we execute the trigger with a commit.

Step 3: Testing the Trigger.

Let us understand the different use cases to test the above-created trigger in the oracle database.

Case 1: Testing the Trigger with Insert Operation.

Let us go ahead and insert a new employee into the COPY_EMP table and test the trigger. To Insert a new employee, please execute the below SQL Statement.

insert into copy_emp (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, salary, hire_date, job_id)
values (1,'JAY','JAY','dotnet@hotmail.com',200,sysdate,'ORACLE_DBA');

From the above INSERT SQL statement, we can see the insert details as employee_id as 1 and first name and last name as JAY and the email_id as dotnet@gmail.com and salary is 200 and hire_date is sysdate and job_id as oracle_dba. So, let us execute this insert statement.

So, the details are inserted. Let us check the table COPY_EMP where the emp_id is 1 by executing the below SELECT Statement.

Select * from employees where employee_id=1;

So, the insert operation is recorded in the table COPY_EMP. Let us check the audit table. Let us check the columns in the EMP_SALARY_AUDIT table by executing the below SQL Statement.

select EMP_ID, OPERATION, OLD_SAL, NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss') OP_DATE , BY_USER 
from EMP_SALARY_AUDIT
order by OP_DATE;

Here, you can see the details of the operation performed in the table EMP_SALARY_AUDIT. The OLD_SAL is null and NEW_SAL is 200 by the user HR.

Case 2: Testing the Trigger with the Update Operation

Let us test the Trigger with an update operation. Please execute the following Update Statement.

Update copy_emp Set salary=salary+10 Where department_id=20;

So, here, we are updating the salary of the employees who is having department_id as 20. Let us execute the above UPDATE Statement and see if this operation is recorded in the EMP_SALARY_AUDIT table or not.

So, the table is updated. Let us use the same select query to check the EMP_SALARY_AUDIT table by executing the following SELECT Statement.

select EMP_ID, OPERATION, OLD_SAL, NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss') OP_DATE , BY_USER 
from EMP_SALARY_AUDIT
order by OP_DATE;

Here, you can see there are two employees with department_id as 20. So, these two records are updated in the COPY_EMP table and those details are also inserted into the EMP_SALARY_AUDIT table.

Case 3: Testing the Trigger with Delete Operation

Let us just delete the row that we have created. We have created the employee_id as 1. So, please execute the following DELETE statement to delete the employee whose employee_id is 1 from the COPY_EMP table.

Delete from copy_emp Where employee_id=1;

The row is deleted. Let us check the audit table and see if the details are inserted or not. We will be using the same select query as follows.

select EMP_ID, OPERATION, OLD_SAL, NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss') OP_DATE , BY_USER 
from EMP_SALARY_AUDIT
order by OP_DATE;

Here, you can see the delete operation is also recorded in the table EMP_SALARY_AUDIT.

Note: We have not committed the data yet. If we execute the commit, then the row will be deleted from the COPY_EMP table. If we execute rollback then all the records will be rollback and the EMP_SALARY_AUDIT will become an empty table. This is how we use the audit table and we test the trigger using multiple DML operations in oracle.

In the next article, I am going to discuss Trigger Firing Sequence in Oracle with Examples. Here, in this article, I try to explain how to create an Audit table using Triggers in Oracle with Examples. I hope you enjoy this Audit table using Triggers in the Oracle article.