THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search

Paul Nielsen

www.SQLServerBible.com

CodeGen to Create Fixed Audit Trail Triggers

Audit Trails - what a pain they can be. We all know that a dynamic audit trigger, that investigates the metadata and builds an audit insert statement on the fly, is easy to implement, but performs as well as my teenager gets up in the morning. A fixed audit trail trigger, hard-coded specifically for the table, minimizes the database workload, but the only task worse than building it is maintaining it.

Wouldn’t it be cool to run a procedure that examined the table’s metadata and used code-gen to build a perfect fixed audit trail trigger? When the table’s schema changes, simply rerun the procedure. I’ve been meaning to write this for about a year. I wanted to include it in the SQL Server 2005 Bible, but just didn’t have the time.

Four events coalesced for me two weeks ago regarding audit trail triggers. First, I saw a blog entry stating the only way to build a dynamic audit trail trigger was to use CLR. UGHGG. 2) A new client has an inconsistent audit trail system built into some stored procedures, but not all. They need an easy way to quickly build a trigger based audit trail.  3) A reader wrote asking for more information and advice on implementing the T-SQL dynamic audit trail trigger I wrote about in SQL Server 2000 Bible. I wanted to give him a better solution than my old dynamic trigger. And 4) I’m starting to edit and prep the SQL Server Bible for the next edition and on the list of new chapters was this idea for a better audit trail method.

So, here’s a script that creates the audit table and builds 4 stored procedures.

AutoAudit 'schema', 'table'

which:

   1) creates dbo.audit  table if not already there

   2) adds created and modifed columns to table

   3) builds insert, update, and modified audit trail trigger

 

AutoAditDrop 'schema', 'table'

which:

1)      drops insert, update, and modified audit trail trigger

 

AutoAuditAll 

AuditDropAll

1)      runs the autoaudit or autoauditdrop for every table in the database other than the audit table.

 

This is only a version 1, so test before you put it in production. I’m open to suggestions and requests.

 

This version is limited to tables with single column primary keys. But that’s easy to change if enough readers ask for it. Also, in the next version I’m going to add error handling.

 


Published Monday, January 15, 2007 11:07 AM by Paul Nielsen
Filed under: ,

Attachment(s): autoaudit.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul Nielsen said:

-----------------------------------

version 1.01 - Jan 15, 2007

  added row version column, incremented by the modified trigger

  cleaned up how the tablename is written to the audit.tablename column

  added delete trigger, which just writes the table, pk, and operation ('d') to the audit table

  changed Audit.[Column] to Audit.ColumnName

-- Query to determine lastvalues of deleted rows

Select TableName, PrimaryKey, ColumnName, NewValue as LastValue from dbo.audit

Where Operation IN ('i', 'u')

AND AuditID IN (

  Select Max(AuditID)

  FROM dbo.audit a

   JOIN (SELECT TableName, PrimaryKey

              FROM dbo.audit where operation = 'd') d

       on a.tablename = d.tablename and a.primarykey = d.primarykey

  GROUP BY a.TableName, a.PrimaryKey, a.ColumnName )

--Pn

January 15, 2007 9:37 PM
 

Paul Nielsen said:

v1.03 includes a handful of bug fixes and generates a view for each table that reconstructs deleted rows.

January 16, 2007 11:10 PM
 

Cade Roux said:

Any chance of a SQL Server 2000 version of this?  sys.tables etc doesn't work in SQL 2000.

January 26, 2007 10:35 AM
 

Vanessa said:

Paul, this is excellent, can you please update for multiple column primary keys. A version for SQL2000 would be appreciated as well. Thanks for the info

February 4, 2007 6:08 PM
 

Ryan said:

Hello - excellent work and a couple of suggestion.

I am wondering if you might add some sort of auditing of the rowversion column. It can be done now using the optional columns, I suppose. It's odd to have an autoincrement rowversion that doesn't make it into dbo.audit, though.

Use Case: Construct a "history" of the last n edits of a specific record, starting with the version immediately before the present one and displaying version #, edit date, and edited by (sometimes with some detail of the "state" of the record if there is state information) in descending order. This forms a simple audit view and answers the usual questions users of the system have - only more advanced audit questions need to be routed to the admins.

On a related note, I would have to customize this script, currently, to pass username as a parameter to the triggers since we are gathering userid from a single sign-on implementation and cannot use suser_sname().

All in all excellent stuff, and I know no script can be all things to all people ;)

March 8, 2007 7:41 PM
 

Nicneeb said:

Oh, King of SQL, THANK YOU!

March 13, 2007 10:38 AM
 

Peter Wanyonyi said:

Sweet! Thanks a heap!!

March 28, 2007 3:05 AM
 

Lapin said:

This looks to be very useful, except that I have to do this sort of thing on a SQL 2000 database. sys.tables and sys.schemas don't exist there.

What would it take to modify this for SQL 2000? I confess I'm not a guru on the system tables.

Lapin in Albuquerque

April 25, 2007 2:32 PM
 

merl said:

Wow. Just ... wow. Nice.

August 24, 2007 5:31 PM
 

Lance Jones said:

Thanks for writing this script, it's pretty much amazing.

I've found a small bug when creating insert and update triggers for a table: there is a typo in the section that checks the column types; smalldateteime should be smalldatetime. It looks like this typo occurs three times throughout the script.

Otherwise, everything works great. Thanks again!

October 5, 2007 5:04 PM
 

Lara said:

Thanks for such a useful and easy to understand script.  It's made my life a little easier!

October 11, 2007 10:54 AM
 

Mark said:

This looks like a very usefull tool. I only have one problem. In our application we handle security within the application, not within SQL. We also use COM+ objects to manage all the database transactions, so to enable connection sharing we use a single login ID from all clients. So my problem is, how can I tell who made the changes to the data as the function you use to find the user will always return the same person?

Thanks for any ideas you can give on this.

October 18, 2007 9:19 PM
 

Prasad Gelli said:

Paul... Excellent work ... My search ended here :)

Can i get the same for SQL 2000 also ... please

November 5, 2007 4:36 AM
 

andy said:

Great stuff thanks so much, i would also vote for the support for multiple column primary keys, since you mentioned it wouldn't be a difficult change.

Thanks.

November 14, 2007 7:48 AM
 

Sean C said:

thanks for this, you may have saved me a huge amount of work!  multiple column keys would be really good though.

November 20, 2007 10:50 AM
 

sotto said:

Hi Paul,

Wow ! ... this script seems to be almost exactly what i'm looking for.

So, here's my vote for a sql2000 version that supports multiple columns.

Meantime, when i do new sql2005 development, i may benefit from this script already.

Thanks !

December 10, 2007 6:10 PM
 

jignesh said:

Hi Paul,

i faced problem when running audit procedure for table have PK as uniqueidentifier, i tried to fix it but not able to fix it.

Coul please help me in this.

Thanks a lot for the beautiful script.

Jignesh

December 14, 2007 5:17 PM
 

Paul Nielsen said:

Jignesh, The problem is that the generated History table has an INT column for the PK, and the triggers assume INT for the PK as well. If all your tables use GUIDs, you could generate the triggers and History table and then modify the code and table design to accomodate GUIDs.

If you have more trouble, you can email me directly,

- pauln@sqlserverbible.com

December 14, 2007 9:09 PM
 

TRavis said:

Is there a way to capture the SQL statement that was executed - in additiont to what type of event it was (update, insert, etc.)?

January 8, 2008 12:56 PM
 

TRavis said:

Also, can this be used againt a table with no PK?

January 8, 2008 1:10 PM
 

Paul Nielsen said:

Hi TRavis,

Thanks for playing with AutoAudit.

It captures the type of statement already, but not the statement itself. It is possible. Before I update AutoAudit, I want to dig into Katmai's Change Logging feature.

Yes, the table must have a PK. But seriously, you want a PK on every table. Without a PK, it's not reeeealllly a table.

-Paul

January 8, 2008 2:21 PM
 

Joe said:

Nice work Paul, saved me boring work and with small adjustments worked like a charm. There is a small case issue in the script (for case sensitive databases), "audit" instead "Audit", "operator" instead of "Operator" etc.

Support for multicolumn PKs are welcome too.

February 7, 2008 9:51 AM
 

S said:

This all looks good. But me is very new to SQL server 2005 and can someone tell me what is SchemaName thing.

Thank you.

April 2, 2008 8:33 AM
 

Neil said:

Paul has a v1.06 of this script available at http://www.sqlserverbible.com/files/autoaudit.zip

June 19, 2008 4:29 PM
 

Neil said:

"use CLR. UGHGG."

I am doing something similar and was going to use CLR, but your comment has made me think. I was thinking that I could use C# with all the debugging capabilities of vs2005 to create my project. I would really appreciate knowing your reasons for your distaste of CLR for this type of trigger.

June 20, 2008 2:03 AM
 

Paul Nielsen said:

Hi Neil,

CLR is not intended for data access, native T-SQL is by far the

best when readign and writign directly to the tables. By definition an audit trigger's primary purpose is data access.

It would be needlessly complex to codegen and then keep updated fixed audit trail triggers for every table. The CLR audit trigger solution generally tries to dynamically on-the-fly determine the meta-data and then dynamically handle the audit.

Any trigger, T-SQL or CLR, will extend the duration of the transaction. The CLR solution has two counts against it: it's not the best for data access, and dynamic is goign to be slower than pre-written.

That's why this audit trail pre-writes T-SQL triggers.

-Paul

June 20, 2008 9:55 AM
 

Paul Nielsen said:

If you want to be notified when I release a new version, you can subscribe to my free e-newletter at: http://tinyurl.com/5zzlmo

-Paul

June 20, 2008 9:59 AM
 

Paul Nielsen said:

AutoAudit 1.07 is posted at www.SQLServerBible.com

-Paul

pauln@sqlserverbible.com

June 25, 2008 12:23 AM
 

Paul Nielsen said:

AutoAudit 1.08 is posted at www.SQLServerBIble.com

It cleans up some case sensitive issues, and AutoAuditDrop now drops the audit columns and defaults from audited tables.

-Paul

June 26, 2008 5:50 PM
 

Daniel Weggemans said:

Thanks Paul great work. Saved me from a whole lot of typing :)

I had some conversion issues with the isnull function though. I suggest updating the equallity check from isnull to nullif. That worked like a charm for me for all datatypes.

For the update trigger I changed this line:

AND isnull(Inserted.[' + c.name + '],'''') <> isnull(Deleted.[' + c.name + '],'''')' + Char(13) + Char(10)+ Char(13) + Char(10)

into

AND nullif(Inserted.[' + c.name + '], Deleted.[' + c.name + ']) is not null' + Char(13) + Char(10)+ Char(13) + Char(10)

July 8, 2008 9:05 AM
 

Paul Nielsen said:

Daniel, I'd like to hear more about the conversion issues you had.

Tha line of code is looking to see if the column data actually changed.

Please email me,

-Paul

pauln@sqlserverbible.com

July 16, 2008 9:39 AM
 

Pierre said:

Hi Paul,

I get an error when running the pAutoAudit stored proc against tables that contain uniqueidentifiers.

Is there any way of fixing this?

Regards,

Pierre

July 28, 2008 7:59 AM
 

Paul Nielsen said:

There's now a CodePlex project for AutoAudit...

http://www.codeplex.com/AutoAudit

August 5, 2008 2:00 AM
 

Daniel Weggemans said:

Paul, Pierre,

Uniqueidentifiers is exactly what caused the conversion issues I was talking about previously, also numeric columns caused problems: error converting data type ... to ...

I managed to work around it by altering the check if the column was changed from using isnull to nullif. I hope that solves your problem too.

August 6, 2008 5:12 AM
 

Paul Nielsen said:

For discussion, issues, and voting on fixes and new feature requests please visit

http://www.codeplex.com/AutoAudit

August 25, 2008 12:53 PM
 

Bob Huff said:

Hi Paul,

I'm very new at this.

Can it be modified to also log the idenity of the user effecting the change?

Thanks again! Great sscript!

Bob

September 7, 2008 1:53 PM
 

Paul Nielsen said:

Hi Bob,

If the users are logging into SQL Server with their own credentials, then AutoAudit does capture the user ID who made the change.

If every user logs in as SA, then no, sorry, there's no easy way to pass in the user id.

-Paul

September 9, 2008 1:10 PM
 

Richard Churchill said:

Hi Paul,

I've left a question on the discussion part of AutoAudit's site on CodePlex, for good measure I thought I'd drop it here aswell.

I've applied AutoAudit to my test database but when an update comes in I get the following error: -

error in [dbo].[subscriber_modified] trigger.  The transaction ended in the trigger.  The batch has been aborted.

I have another trigger on the table named "Subscriber_AspNet_SqlCacheNotification_Trigger" which was auto generated by SQL Cache Dependancy for the ASP.net web apps.  When removed the error goes away and AutoAudit's triggers work perfectly fine.

AutoAudit does exactly what I need but I'm not sure at what point it's going wrong.  Could you shed any light on a fix?

Many Thanks

Richard

September 21, 2008 2:30 PM
 

jc said:

I noticed that the test file and the source code links on codeplex are the same.  At least, today when I downloaded both of them I received the same thing twice.

looks cool.  very excited to learn the clr is not the only way to do this.  i had developed a generic audit trail using the clr but was unable to grab the table name - for some odd reason msft did not include that.  

cheers!

jc

October 14, 2008 9:44 AM
 

Paul Nielsen said:

AutoAudit 1.09 is up on CodePlex:

support for SQL Server 2008 data types

_RowHistory UDF

SchemaAudit trigger keeps AutoAudit in synch w/ Alter Table changes

October 14, 2008 10:40 AM
 

Cade Roux said:

The NULLIF() workaround only works if the column is changing FROM NULL to non-NULL.  If a column is changing from non-NULL to NULL, you will get an error, because the first parameter to NULLIF() cannot be NULL.  I have posted a general workaround to the CodePlex discussion and the issues related to the use of ISNULL(, '').

February 12, 2009 6:03 PM
 

sam said:

Hello.Sorry for my English level.

I am inexpert in this topic. The autoaudit has seemed to me to be brilliant.

Does SQL2008 take something of internal audit?

Also we use Oracle, from 8i and Superior there is something similar to what you propose? Some link?

March 3, 2009 5:13 PM
 

Jose Barreto's Blog said:

Here are some notes on SQL Server 2008 Change Tracking (CT) and Change Data Capture (CDC) I took while

March 24, 2009 3:07 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement