Using the OUTPUT Clause in TSQL for Auditing

The OUTPUT clause is often times underappreciated by the TSQL programmer. There are many really interesting things that you can do with the OUTPUT clause, from inserting to two tables with one statement or to replace the SCOPE_IDENTITY option. For this posting I am looking into using the OUTPUT clause for auditing.

By auditing in this example, I specifically mean tracking what was changed, and when it was changed.

In the sample below I create a database called output_demo, create a called Departments, add a few rows, create another table called DeptAudit then update Departments and insert to DeptAudit in one statement.


USE [Master];

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'output_demo')
BEGIN
 ALTER DATABASE [output_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 DROP DATABASE [output_demo];
END
GO
CREATE DATABASE [output_demo];
GO

USE [output_demo];
-- create a table to use for the demo
CREATE TABLE [Departments] (
 id int, --would normally be an INT IDENTITY
 department VARCHAR (200),
 parent int
 );

CREATE TABLE [DeptAudit] (
 id int, --would normally be an INT IDENTITY
 old_department VARCHAR (200),
 new_department VARCHAR (200),
 parent int,
 ModificationDate DATETIME
 );
-- insert top level departments
insert into [Departments] (id, department, parent)
values (1, 'Camping', null),
 (2, 'Cycle', null),
 (3, 'Snowsports', null),
 (4, 'Fitness', null);

-- now some sub-departments for camping
insert into [Departments] (id, department, parent)
values (5, 'Tents', 1),
 (6, 'Backpacks', 1),
 (7, 'Sleeping Bags', 1),
 (8, 'Cooking', 1);

select * from Departments;

-- here's the magic to update one table and insert into another in one statement

update dept
 set dept.department = 'Camp Kitchen'
output [deleted].id, [deleted].department,
       [inserted].department, [deleted].parent, GETUTCDATE()
 INTO DeptAudit
 from Departments dept
 where dept.id = 8;

select * from Departments;
select * from DeptAudit;

Which generates this output.

Where you can see that the update statement to change ‘Cooking’ to ‘Camp Kitchen’ was logged into the DeptAudit table showing both the old and the new value.

A similar process could be logged on INSERT, UPDATE, DELETE, or MERGE in order to track anything that is being changed in a table.

Using the OUTPUT Clause in TSQL for Auditing

Leave a Reply Cancel reply