The “SQL Guy” Post # 23: TRIGGER-less Tracking of Database Changes

  • Article

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.

 

Example:

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

 

clip_image002

 

--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

 

-- RESULTSET FROM THE CHANGED TABLE

clip_image004

 

--QUERY THE ITEM TABLE

SELECT * FROM ITEM

 

--YOU WILL SEE THE BELOW RESULTSET

clip_image006

 

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.

 

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

 

DamirB-BlogSignature