Wednesday, November 22, 2017

SQL Server - Audit DML Changes without Triggers

By Saleem Hakani on 9/21/2014

You can now audit your changes using DML statements without even using TRIGGERS:


SQL Server supports an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the result set in a table or table variable.

The functionality is similar to what triggers had with INSERTED and DELETED tables which used to access the rows that have been modified during the DML operation.


For Example:

Let's change the address from the address table to the reverse of the original value.


--Create the address table

Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))


--Insert data

Insert into Address Values (234,567,'1234 One SQL Way, Microsoft City, U.S.')

Insert into Address Values (345,678,'1234 One Windows Way, Microsoft City, WA')


--Declare a table variable

Declare @Recordchanges table (change Varchar(255))


--Update the address

Update Address

Set Address=reverse(address)


--Record the updates into the table variable

OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' into @RecordChanges


--Query the changes from table variable

Select * from @RecordChanges






Original Value:'1234 One SQL Way, Microsoft City, U.S.' has been changed to: '.S.U ,ytiC tfosorciM ,yaW LQS enO 4321'

Original Value:'1234 One Windows Way, Microsoft City, WA' has been changed to: 'AW ,ytiC tfosorciM ,yaW swodniW enO 4321'


This is one of the many interesting features that have been added since SQL Server 2005.

Note: You may also use the above logic to track any changes you do to the data and store it in a table.

SQL Article Tags
Copyright [2014] by SQLCOMMUNITY.COM