Software Technology Tips

Sometimes we need to audit all DML operations for tables in a MSSQL database. There are many methods for achieving this, one of the most common approaches is using  DML Triggers . But DML triggers can be expensive so we can make use of  the OUTPUT clause which is a new TSQL feature available in SQL SERVER 2005 and later. The OUTPUT clause can make use of the INSERTED and DELETED virtual tables just like Triggers.

 We can use output clause with a  table variable , temporary table or a permanent table.  Some examples are given below:

 

 

OUTPUT clause with INSERT statement

--------Creating the primary table which will store data
 
CREATE TABLE TestTable (ID INT, FIRSTNAME VARCHAR(100))
 
----Declaring a TABLE VARIABLE to store values of OUTPUT clause
 
DECLARE @TmpTable TABLE (ID INT,FIRSTNAME VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
 
----values in the table variable.

 
INSERT TestTable (ID, FIRSTNAME)
OUTPUT Inserted.ID, Inserted.FIRSTNAME INTO @TmpTable
VALUES (1,'FirstVal')

 
 
-----OUTPUT clause into Table with UPDATE statement
 
--------Creating the primary table which will store data
 
CREATE TABLE TestTable5 (ID INT, FIRSTNAME VARCHAR(100 ))
 
----Declaring a TABLE VARIABLE to store values of OUTPUT clause
 
DECLARE @TmpTable TABLE (ID_New INT, FirstName_New VARCHAR(100),ID_Old INT, FirstName_Old VARCHAR(100 ))
 
----Insert values in real table as well use OUTPUT clause to insert
----values in the table variable.
 
INSERT
TestTable5 (ID, FIRSTNAME
)
VALUES
(1,'Ari'
)
INSERT
TestTable5 (ID, FIRSTNAME
)
VALUES
(2,'Ari1'
)


----Update the table and insert values in table variable using Output clause


UPDATE
TestTable5
SET
FIRSTNAME =
'Ari3'
OUTPUT
Inserted.ID, Inserted.FIRSTNAME, Deleted.ID, Deleted.FIRSTNAME INTO
@TmpTable
WHERE
ID IN (1,2)

-----OUTPUT clause into Table with DELETE statement
 
----Creating the table which will store permanent table

 
CREATE TABLE TestTable6 (ID INT, FIRSTNAME VARCHAR(100))
 
----Creating temp table to store values of OUTPUT clause
 
DECLARE @TmpTable TABLE (ID INT, LASTNAME VARCHAR(100))
 
----Insert values in real table
 
INSERT TestTable6 (ID, FIRSTNAME)
VALUES (1,'Ari1')
INSERT TestTable6 (ID, FIRSTNAME)
VALUES (2,'Ari2')

 
----Update the table and insert values in table variable using Output clause


DELETE
FROM TestTable6
OUTPUT Deleted.ID, Deleted.FIRSTNAME INTO @TmpTable
WHERE ID IN (1,2)


Related Tags:

Database

Author: Arijit Rath

Database

Let us Connect!

iso 9001 QA25 Red Herring STPI D&B Fastest Growing SME 2013 Award zinnov Nasscom

copyright (c) Mindfire Solutions 2007-2014. Login