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