Read Prev Project: Auditing
Update and Delete Operation with Trigger
Post Reference: Vikram Aristocratic Elfin Share
When you want to Audit only
the column which gets updated, you require something called update () function
to find the column which gets updated.
I am writing this article
because today I earshot something like this from a conversation which was going
between an SQL Expert Manish Sharma and a Project team of iGATE. Their discussion influences
me to write up this article. Thanks guys n sorry for listening your discussion;-P,
anyways your discussion made an increment to my article list.
Here I have a table emp with following data.
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)
Now I am creating a Audit table which will hold the old
values of update or delete operation.
Create table Tab_MyAudit1
(
A_Action char(1) not null,
A_AuditDate datetime not
null,
A_Table varchar(15) not null,
A_Values xml
)
Command(s)
completed successfully.
My Objective: To write a trigger which will insert only the old
column values which gets updated with update statement, no need to record other
column values which are not the part of update statement. And in case of delete
record all column values.
Now here we are creating a trigger which is using
Update() function to check which column gets updated, accordingly it insert the
values to Audit table. The if (SELECT COUNT(*) FROM inserted) > 0 will tell whether the DML operation is Update or
Delete. In case of Update INSERTED table will have data so this condition will
set to true whereas, in case of delete, only Deleted table will hold data, so
this condition will set to false and the pointer will go to else part.
create trigger TGR_EMP_UpdateDeleteLog1
ON emp
for UPDATE,DELETE
AS
begin
Declare
@AuditAction varchar(1)
Declare
@OldMessage varchar(200)
--Condtion for U
or D
if (SELECT COUNT(*) FROM inserted) > 0
begin
set
@AuditAction = 'U'
set
@OldMessage =
(select (case
when
update(ename)then deleted.ename end) as OldName,
(case
when update(dept_id)then deleted.dept_id end) as OldDeptId
from
deleted
inner
join emp
on
deleted.emp_id=emp.emp_id
FOR XML AUTO)
end
else
begin
set
@AuditAction = 'D'
set
@OldMessage =
(SELECT * from deleted FOR XML AUTO)
end
insert into Tab_MyAudit1 values
(@AuditAction,getdate(),'EMP',@OldMessage)
End
Command(s)
completed successfully.
Now our Trigger is ready, let’s test it with DML
statement, before that lets query Audit table and emp table.
select * from Tab_MyAudit
(0 row(s)
affected)
So there is no row in Audit table Tab_MyAudit for now. Lets query Emp table too
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha 1
2 richa 1
3 sidhika 2
4 Pragyan 2
5 sneha 3
6 Kavita 3
(6 row(s)
affected)
Update Operation
Now since everything is in place, let’s fire an Update
DML where we are updating employee name to Sneha where the employee id is 4.
update emp
set ename = 'Sneha' where emp_id = 4
let’s check Audit table, the Audit table should record
only old Name which gets changed through Update statement. i.e “Pragyan” should
get inserted in Audit table.
select * from Tab_MyAudit1
A_Action
A_AuditDate A_Table A_Values
--------
----------------------- ------- ---------------------------
U 2013-12-30 22:11:26.123 EMP <deleted
OldName="Pragyan"/>
(1 row(s)
affected)
So to our expectation we found correct entry in Audit
table. Let’s query Emp table too to
check the update.
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha 1
2 richa 1
3 sidhika 2
4 Sneha 2
5 sneha 3
6 Kavita 3
(6 row(s)
affected)
Now we have two employee with employee name ‘Sneha’ but
different dept_id i.e. 2 and 3 , lets update the dept Id to 1 where employee
name is ‘Sneha’
update emp
set dept_id = 1 where ename = 'Sneha'
Now let’s query
the Audit table, our expectation is, the department which gets updated
to 1 are 2 and 3, so 2 and 3 should gets inserted in a single row in audit
table.
select * from Tab_MyAudit1
A_Action
A_AuditDate A_Table A_Values
--------
----------- ------- ----------------------------------------------
U 2013-12-30 EMP
<deleted OldName="Pragyan" />
U 2013-12-30 EMP
<deleted OldDeptId="2"/><deleted
OldDeptId="3" />
(2 row(s)
affected)
So to our expectation it records both the department id
as we can see the second record. Also let’s query emp table to check the
update.
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha 1
2 richa 1
3 sidhika 2
4 Sneha 1
5 sneha 1
6 Kavita
3
(6 row(s)
affected)
Delete Operation
Now here we are trying to delete employee with employee
id 5 and will check whether the whole record values gets stored in Audit Table.
delete emp where emp_id = 5
Let’s query Audit table
select * from Tab_MyAudit1
A_Action
A_AuditDate A_Table A_Values
--------
---------- -------- ---------------------------------------------
U 2013-12-30 EMP <deleted OldName="Pragyan"
/>
U 2013-12-30 EMP <deleted
OldDeptId="2"/><deleted OldDeptId="3"/>
D 2013-12-30 EMP <deleted emp_id="5"
ename="sneha" dept_id="1"/>
(3 row(s)
affected)
So to our expectation as we can see the third record,
it records all column value in Audit table. Lets query our Employee table to
see the update.
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha 1
2 richa 1
3 sidhika 2
4 Sneha 1
6 Kavita 3
(5 row(s)
affected)
Take a good look at Programmer, he
will take care of your needs, code has answer to every problem.
Post Reference: Vikram Aristocratic Elfin Share
