Auditing Update and Delete Operation with Trigger

There are many ways to audit trail, and with the release of SQL Server 2008, we got ample of way to audit your DML statements, but in this article we will see how we can audit any DML (update/Delete) using Trigger. For that we are taking an existing EMP table.

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           sapna      2

5           sneha      3

6           Kavita     3

7           Binny      1

(7 row(s) affected)

We are creating a Audit Table which will hold the old values which gets updated or deleted with DML operation on EMP table. Here we have taken an XML field which will hold the old values in XML format.

Create table Tab_MyAudit

(    

      A_Action char(1) not null,

      A_AuditDate datetime not null,

      A_Table     varchar(15) not null,  

      A_Values xml

)

Command(s) completed successfully.

Now since platform is ready we can now create our Trigger to log the data which gets updated or deleted either using Update or Delete statement.

create trigger TGR_EMP_UpdateDeleteLog

on emp

after update, delete

as

begin

      declare @oldValues xml

      set @oldValues = (SELECT *    from  deleted

      FOR XML AUTO)    

     

      insert Tab_MyAudit

      select A_Action = case when i.emp_id is null then 'D' else 'U' end,

      A_AuditDate = GetDate(),'EMP',@oldValues

      from deleted d

           left outer join inserted i

           on i.emp_id = d.emp_id              

end

Command(s) completed successfully.

Here in this trigger we have used LEFT JOIN, left of left join is DELETED table and right if LEFT JOIN is INSERTED table, this is because, whether delete or Update operation perform, DELETED table always have row but INSERTED table will get row only in case of UPDATE statement fire.

Let’s check the data inside Audit Table

select * from Tab_MyAudit

(0 row(s) affected)

So for now we don’t have any records in Audit table. Now it’s time to check our trigger by firing some update statement on EMP table.

update emp

set ename = 'Pragyan' where emp_id = 4

Now since we have fired Update statement, Trigger must have implicitly fired and the old value of ename where emp_id is 4 must saved in Audit Table. So lets query Audit table to check the trail.

select * from Tab_MyAudit

A_Action A_AuditDate             A_Table A_Values

-------- ----------------------- ------- --------------------------------------------------

U        2013-12-30 21:19:03.127 EMP     <deleted emp_id="4" ename="Pragyan" dept_id="2" />

Yes to our believe, the old value of emp_id =4 has been saved in Audit trail Table.

Let’s now try with Delete statement. Here we are deleting Employee with emp_id = 7

delete emp where emp_id = 7

Trigger must have implicitly fired with the Delete statement and the old value of employee with emp_id is 4 must saved in Audit Table. So lets query Audit table to check the trail.

select * from Tab_MyAudit

A_Action A_AuditDate             A_Table A_Values

-------- ----------------------- ------- --------------------------------------------------

U        2013-12-30 21:19:03.127 EMP     <deleted emp_id="4" ename="Pragyan" dept_id="2" />

D        2013-12-30 21:21:02.850 EMP     <deleted emp_id="7" ename="Binny" dept_id="1" />

(2 row(s) affected)

So here we can see in the second record the deleted values are stored in Audit table.

In the next article we will see, how to audit only column values which gets updated using DML statement.

When codes go wild it is hard to handle, subsequently programmer weight comes up.

Post Reference: Vikram Aristocratic Elfin Share