Track changes on your table with OUTPUT Clause no need for trigger

SQL Server provides a simplified method of auditing changes using DML statements without having to use Triggers. With the invent of OUTPUT clause as a part of DML statement helps us in tracking changes made during any DML operations.

The OUTPUT clause can save the result-set in a table or table variable. Let stimulate the change tracking with OUTPUT clause

Let’s create a table testOutputClause with id and name as a column and insert few data.

create table testOutputClause

(id int identity(1,1),

name varchar(15))

insert into testOutputClause values('Purushottam')

insert into testOutputClause values('Vrishab')

insert into testOutputClause values('Palash')

select * from testOutputClause

id          name

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

1           Purushottam

2           Vrishab

3           Palash

(3 row(s) affected)

Now we are creating audit table tracker which will record all the changes that will be performed on testOutputClause table.

create table tracker

(tableName varchar(18),

changesDone varchar(100))   

Now we going to fire update statement on testOutputClause and at the same time we will be recording the changes using OUTPUT Clause.

update testOutputClause

set name = 'Ipsita'

output 'tracker','the name'+ deleted.name +'is changed to'+ inserted.name

into tracker

where id=1

(1 row(s) affected)

Lets see the result of update statement on testOutputClause, so records are now updated with name = Ipsita where the id was 1.

                                                             

select * from testOutputClause

id          name

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

1           Ipsita

2           Vrishab

3           Palash

(3 row(s) affected)

Now let’s see the result of tracker (the audit table).

select * from tracker

tableName          changesDone

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

tracker            the name Purushottam is changed to Ipsita

(1 row(s) affected)

We can see the update is tracked successfully in audit table.

NOTE: You can use SQL Server magic table i.e INERTED and DELETED with OUTPUT Clause and records the changes in DML.

Code until you loses yourself

 Post Reference: Vikram Aristocratic Elfin Share