There are times when you need to track all the changes happening to a table. In the past, developers have used custom code to track changes by calling stored procedures, triggers and until recently even change data capture (also known as CDC).
SQL Server now supports a simplified method of auditing changes using DML statements without having to use Triggers or CDC (Change Data Capture). SQL Server introduces an OUTPUT clause as a part of DML statement that can help you in tracking changes made during any DML operations.
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.
In this example we will change the ITEMCOMMENT value from the ITEM table to the reverse of the original value and store the original and changed values in a table.
TRACKING CHANGES USING OUTPUT CLAUSE
--CREATE ITEM TABLE
CREATE TABLE ITEM (ITEMNAME VARCHAR(40), ITEMCOMMENT VARCHAR(255))
--INSERT DATA IN TO ITEM TABLE
-- PLEASE NOTICE THE MULTIPLE INSERTS WITH ONE INSERT STATEMENT
INSERT INTO ITEM VALUES
('MANGO','ELPPA NAHT RETTEB SI OGNAM'),
('PEACH','ETATS HCAEP A SI AIGROEG'),
('GRAPES','TAERG ERA SEPARG')
-- QUERY THE ITEM TABLE
SELECT * FROM ITEM
--DECLARE A TABLE VARIABLE TO STORE THE CHANGED VALUE
DECLARE @RECORDCHANGES TABLE(CHANGE VARCHAR(255))
--UPDATE THE ITEM TABLE TO FIX ITEM COMMENTS
--NOTICE THE USE OF OUTPUT PARAMETER BELOW.
--THE CHANGES ARE STORED IN THE TABLE VARIABLE
OUTPUT 'ORIGINAL VALUE: [' + DELETED.ITEMCOMMENT+'] HAS BEEN CHANGED TO: ['+ INSERTED.ITEMCOMMENT+']' INTO @RECORDCHANGES
--QUERY THE CHANGES FROM THE TABLE VARIABLE
SELECT * FROM @RECORDCHANGES
-- RESULTSET FROM THE CHANGED TABLE
--QUERY THE ITEM TABLE
SELECT * FROM ITEM
--YOU WILL SEE THE BELOW RESULTSET
NOTE: OUTPUT CLAUSE IS NOT SUPPORTED FOR THE FOLLOWING STATEMENTS:
1. DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
2. INSERT statements that contain an EXECUTE statement.
3. Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
4. The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
5. A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.