SYSK 133: Oh, the Mighty OUTPUT Clause!

SQL 2005 now has the new OUTPUT clause – see documentation at http://msdn2.microsoft.com/en-us/library/ms177564.aspx.   A lot of examples show how you can use the OUTPUT clause to store inserted/updated/deleted values into another table or a table variable.  If this feature is new to you check out these couple of articles -- http://www.dbazine.com/sql/sql-articles/cook18/ and http://www.databasejournal.com/features/mssql/article.php/3598636.

One of my favorite usages of this new feature is creating an audit record when any record from an underlying table is touched, including data retrieval via SELECT statement (which is the example given below) as an ACID operation. 

Say, you have a table Table1(pk_id, col1, col2):

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Table1](

      [pk_id] [int] IDENTITY(1,1) NOT NULL,

      [Col1] [int] NOT NULL,

      [Col2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

(

      [pk_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

and Table1Audit(AuditRecordId, DateTimeStamp, AccessType, UserName, pk_id, col1, col2):

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Table1Audit](

      [AuditRecordId] [int] IDENTITY(1,1) NOT NULL,

      [DateTimeStamp] [datetime] NOT NULL,

      [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [AccessType] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [pk_id] [int] NOT NULL,

      [Col1] [int] NULL,

      [Col2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

 CONSTRAINT [PK_Table1Audit] PRIMARY KEY CLUSTERED

(

      [AuditRecordId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

You could disable direct table access and expose data via a stored proc as follows (for clarity, I’m limiting this discussion to data retrieval only):

CREATE PROCEDURE GetTable1Data(@Col1DataFilter int)

AS

BEGIN

      INSERT INTO Table1Audit(DateTimeStamp, UserName, AccessType, pk_id, Col1, Col2)

      OUTPUT inserted.pk_id, inserted.Col1, inserted.Col2

      SELECT getdate(), suser_sname(), 'S', pk_id, Col1, Col2 FROM Table1

      WHERE Col1 = @Col1DataFilter

END

Now, insert some data:

insert into Table1 values(123, 'Some data 1')

insert into Table1 values(836, 'Some data 3')

insert into Table1 values(123, 'Some data 4')

insert into Table1 values(234, 'Some data 2')

and see it in Table1 and nothing, at this point, in table Table1Audit (I’m assuming you have not, yet, disabled direct table access for selects):

select * from table1

select * from Table1Audit

Now, get your data via the stored proc and see the audit tracking performed:

GetTable1Data 836

select * from Table1Audit

go

GetTable1Data 123

select * from Table1Audit

go