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