Light Auditing Using OUTPUT

Had a brief conversation at work today about the inserted and deleted tables that are available in sql 2000 triggers. The availability of these "tables" for sql 2005 has been expanded with the OUTPUT clause.

Any values that are inserted or deleted as the result of INSERT, UPDATE, or DELETE statements are available through this clause (the values as they exist BEFORE any triggers fire).

This is a handy way to enforce application level auditing without having to worry about maintaining triggers (and disabling them for non-audited, administrative udpates).

An example would really make this more clear. Since I don't want to clutter this up with sample code, I've put some code here(no longer available; 20060313), in case you have a sql 2005 instance on which you would like to run it. If you don't have an instance on which to run it, the query results are included at the end of the file. You can walk through the code to see how they were produced.

The Microsoft SQL Server Management Studio (still couldn't remember the whole thing) doesn't highlight OUTPUT as a keyword. Though it was rather ironic that one of the new features hadn't made it into the code editor, yet. Also noticed that NOCOUNT doesn't highlight like it does in query analyzer. Guess it's just a beta :-)


On a quick, unrelated note, table variables still don't support named constraints. For example,
DECLARE @tableVariable TABLE (pk int identity( 1, 1 )
CONSTRAINT [PK__tableVariable__pk] PRIMARY KEY CLUSTERED )
is not a valid sql statement while
DECLARE @tableVariable TABLE (pk int identity( 1, 1 ) PRIMARY KEY CLUSTERED )
and
CREATE TABLE tableName (pk int identity( 1, 1 )
CONSTRAINT [PK__tableName__pk] PRIMARY KEY CLUSTERED )
both are.

Sorry about the code pollution.