Using Pragma Autonomous_Transaction to Create an Audit Trail

This pragma (compiler directive) is useful because the insert into the audit table -- while happening in the middle of another transaction, stands on its own. That means if a rollback happens on the main transaction, your audit trail will still show that a change was attempted.

For example, connect to the HR schema:

Connect hr/hr

---

This sequence will be used as a transaction number in the audit table.

Create Sequence Trans_Seq;

---

This is a simple table that tracks a transaction number, the user that attempted the update, and the system date.

Drop Table Track_Upd_Sal purge;
Create Table Track_Upd_Sal ( Trans_Num  NUMBER
                           , Trans_User VARCHAR2(10)  
                           , Trans_Date DATE
                           );

---

This procedure will be called by a trigger. Because it is autonomous, the Insert shown here will happen whether or not the update to Salary is committed or rolled back. (The 'Commit' shown in this procedure does not affect the transaction in the Employee table.)

Create or Replace Procedure p_Track_Upd_Sal
    ( User_In   VARCHAR2
    , Date_In   DATE)                   is
    Pragma Autonomous_Transaction; 

Begin
    Insert into Track_Upd_Sal values( Trans_Seq.NextVal
                                    , User_In
                                    , Date_In);
Commit;
End;
/

---

This trigger will call the above procedure if someone tries to update the salary in the Employees table.

Create or Replace Trigger Bef_Upd_Sal_Emp
    Before Update 
        of Salary 
            on Employees
                referencing new as new old as old 
                    for each row
Begin
    p_Track_Upd_Sal ( User
                    , SYSDATE);
End;
/

---

OK, the user 'HR' is now going to try this:

Update Employees
    Set Salary = 10000
    Where Employee_Id = 197;

---

If we look, the salary has been updated, and the audit trail has a new record.

Select Employee_id
     , Salary
  From Employees
 Where Employee_Id = 197;
    
Select *
From Track_Upd_Sal;

---

But notice the salary change can still be rolled back.

Rollback;
Select Employee_id
     , Salary
  From Employees
 Where Employee_Id = 197;

Tiny URL: http://tinyurl.com/autotran