Output clause and Auditing of data

There is very common scenario to auditing of data in a table using triggers. In this case, the trigger uses information from the inserted and updated tables to add rows into the audit tables. MS has introduced a new OUTPUT clause in SQL Server 2005 to audit data instead of using triggers to insert rows into an audit table.

CREATE TABLE test (col INT NOT NULL);
CREATE TABLE test_audit ( old_col INT NOT NULL, new_col INT NULL);
INSERT INTO test (col) values( 1 );
INSERT INTO test (col) values( 2 );

UPDATE test
SET col = col + 1
OUTPUT deleted.col ,inserted.col into test_audit
WHERE col = 1;

DELETE FROM test
OUTPUT deleted.col, NULL into test_audit
WHERE col = 2;

SELECT * FROM test
SELECT * FROM test_audit;