Auditing only updated column with Trigger

Read Prev Project: Auditing Update and Delete Operation with Trigger

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

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

Changing the default behavior of DML statements (INSERT/UPDATE/DELETE)

Generally we are using DML statements like Insert for inserting data into database table likewise Update for updating existing data in database table, but it is also possible to change the default behavior INSERT/UPDATE/DELETE (DML) statements for example while inserting data using INSERT statement, the new record may applies as an update to the table or even applies as a delete to the table.

You can implement the changing behavior with INSTEAD OF TRIGGER. Let’s do some practical to achieve the changing behavior of DML statements.

use testDemo

go

if object_id('ChangingDMLBehavior') is not null

      drop table ChangingDMLBehavior

go

     

We were checking the existence of 'ChangingDMLBehavior' table object.

Now lets create a table 'ChangingDMLBehavior'

create table ChangingDMLBehavior

(id int not null identity(1,1),

name varchar(10))

Command(s) completed successfully.

Table is now ready; it’s time to write an INSTEAD OF TRIGGER to stimulate the changing behavior of INSERT statement.

 create trigger trg_DMLBehavior on ChangingDMLBehavior

instead of insert

as

begin

      select * from inserted

      select * from deleted

      return

end

go

Command(s) completed successfully.

Now since Trigger is in place, now it’s time to try out INSERT statement on 'ChangingDMLBehavior' and check out the behavior of INSERT Statement.

 insert into ChangingDMLBehavior(name) values('Binny')

Now querying the table to find the inserted records, to our shock we found no records.

select * from ChangingDMLBehavior

id          name

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

(0 row(s) affected)

This is because a record is inserted to table 'ChangingDMLBehavior' with INSERT command, but nothing happens to the table due to the instead of trigger. This is because the trigger does not tell SQL Server to perform any data manipulation operation.

Let’s do some modification of INSTEAD OF TRIGGER to allow INSERT OPTION on table.

alter trigger trg_DMLBehavior on ChangingDMLBehavior

instead of insert

as

begin

      set nocount on

      insert into ChangingDMLBehavior(name)

            select name from inserted

      return

end

go

Command(s) completed successfully.

Now we can fire INSERT statement on 'ChangingDMLBehavior' table and can expect insertion of record to the table. Lets insert one record into 'ChangingDMLBehavior'.

insert into ChangingDMLBehavior(name) values('Prachi')

(1 row(s) affected)

Querying the table we get

select * from ChangingDMLBehavior

id          name

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

1           Prachi

(1 row(s) affected)

Instead of triggers are useful when you want some setting kind of table in database to hold the defined values.

When you are in love with your code you will stop dreaming code with closed eyes because reality better than your dreams

Post Reference: Vikram Aristocratic Elfin Share

Make use of Magic tables with output clause while inserting/updating or Deleting record in your database table objects

There are two magic table provided by SQL server- Inserted and Deleted.

Inserted and Deleted: The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert transaction, new rows are added to the inserted table.  In case of update transaction which is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table.

OUTPUT Clause: The clause returns a copy of the data that you’ve inserted into or deleted from your tables.  OUTPUT clause has accesses to inserted and deleted tables, OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

Let’s explore this with the help of an example:

For our explanation we have taken a table ‘User_Tab’ having the following records in it.

select * from user_tab

user_id     user_name                                          active

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

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Fani                                               1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

(11 row(s) affected)

INSERT : Now we are trying to insert a new record in it. See the below mentioned code, here we are using OUTPUT clause to show the value which are going to inserted into the table

insert into user_tab(user_name,active)

output INSERTED.user_id,INSERTED.user_name

values ('suranjan','True')

user_id     user_name

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

16          suranjan

(1 row(s) affected)

DELETE: Here we are using OUTPUT clause with Deleted magic table of Sql Server to fetch the deleted value.

DELETE user_tab

OUTPUT deleted.user_id,deleted.user_name

WHERE user_name='suranjan'

user_id     user_name

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

16          suranjan

(1 row(s) affected)

UPDATE:  In case of update, the old rows are copied to the deleted table first, and then the new rows are copied to the inserted table. See the below code

update user_tab

set user_name = 'Phaneesh'

output deleted.user_name as 'PreviousName' ,inserted.user_name as 'NewName'

where user_name = 'Fani'

PreviousName                                       NewName

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

Fani                                               Phaneesh

(1 row(s) affected)

Post Reference: Vikram Aristocratic Elfin Share