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:

1Create Table Address
2(
3     ProductID Int,
4     SupplierID Int,
5     Address Varchar(255)
6)
7Go

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

01Insert into Address Values
02(
03   234,
04   567,
05   '1234 One SQL Way, Microsoft City, U.S.'
06)
07Go
08Insert into Address Values
09(
10   345,
11   678,
12   '1234 One Windows Way, Microsoft City, WA'
13)
14Go

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

01Declare @Recordchanges table
02(
03   change Varchar(255)
04)
05  
06Update Address
07   Set Address=reverse(address)
08  
09--Record the updates into the table variable
10OUTPUT '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:

1Select * from @RecordChanges
2  
3--Output Change
4Original Value:'1234 One SQL Way, Microsoft City, U.S.' has been changed to: '.S.U ,ytiC tfosorciM ,yaW LQS enO 4321'
5Original 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.