SQL Server Tips ----- TRIGGER-LESS TRACKING OF DML OPERATIONS

There are times when you need to track all thechanges happening to a table. In the past, developers have used custom code totrack changes by calling stored procedures, triggers and until recently evenchange data capture (also known as CDC).

SQLServer now supports a simplified method of auditing changes using DMLstatements without having to use Triggers or CDC (Change Data Capture). SQLServer introduces an OUTPUT clause as a part of DML statement that can help youin tracking changes made during any DML operations.

The OUTPUT clause cansave the result-set in a table or table variable. The functionality is similarto what triggers had with INSERTED and DELETED tables which used to access therows that have been modified during the DML operation.

Example:

Inthis example we will change the ITEMCOMMENT value from the ITEM table to thereverse of the original value and store the original and changed values in atable.

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

UPDATE ITEM 
SET ITEMCOMMENT=REVERSE(ITEMCOMMENT) 
OUTPUT 'ORIGINAL VALUE: [' + DELETED.ITEMCOMMENT+'] HAS BEEN CHANGED TO: ['+ INSERTED.ITEMCOMMENT+']' INTO @RECORDCHANGES

--QUERY THE CHANGES FROM THE TABLE VARIABLE
SELECT * FROM @RECORDCHANGES

--QUERY THE ITEM TABLE
SELECT * FROM ITEM

 

NOTE: OUTPUTCLAUSE IS NOT SUPPORTED FOR THE FOLLOWING STATEMENTS:

1.       DML statements that reference local partitioned views, distributedpartitioned views, or remote tables.

2.       INSERT statements that contain an EXECUTE statement.

3.       Full-text predicates are not allowed in the OUTPUT clause when thedatabase compatibility level is set to 100.

4.       The OUTPUT INTO clause cannot be used to insert into a view, orrowset function.

5.       A user-defined function cannot be created if it contains an OUTPUTINTO clause that has a table as its target.

 

Please refer to SQL Server BOL (BooksOnline) for detailed information on the Output clause.