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


 

Comments (1)

  1. that’s pretty cool – I don’t remember hearing about this anywhere previously.