Tracking DML Changes without SQL Server 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 accesses the rows that have been modified during DML operation.

Let’s take an example of changing the address by reversing it’s original value:

Let’s create an Address table using the below script:

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

Let’s add some data to the Address table using the below script:

Insert into Address Values 
(
   234,
   567,
   '1234 One SQL Way, Microsoft City, U.S.'
)
Go
Insert into Address Values 
(
   345,
   678,
   '1234 One Windows Way, Microsoft City, WA'
)
Go

Let’s now update the address: (Please observe the use of OUTPUT clause)

Declare @Recordchanges table 
(
   change Varchar(255)
)
 
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

Once the record is updated, you can now check the values of both the records before the update and after the updates:

Select * from @RecordChanges
 
--Output Change
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'

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