THE SQL Server Blog Spot on the Web

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

Paul Nielsen

www.SQLServerBible.com

AutoAudit 1.09 posted to CodePlex

AutoAudit is a code-gen utility that creates audit trail triggers, views to reconstruct deleted rows, and a table-valued UDF to reconstruct row history. 

AutoAudiot 1.09 adds:
_RowHistory table-valued UDF
SchemaAudit triggers that keep the triggers in synch with ALTER TABLES, and audits database changes
compatability with SQL Server 2008 data types
several nit bug fixes

AutoAudit CodePlex site 

ScreenCast demoing AutoAudit 1.09 (6:45)

 please post issues and feature requests to CodePlex, Thanks, -Paul

Published Monday, October 13, 2008 7:05 PM by Paul Nielsen

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:

1.09a is posted, with 2 changes to _RowHistory:

fixes a hardcoded path bug

values not changed in row versions were reported as 1, or 1/1/1980, and are now reported as null

October 23, 2008 12:29 PM
 

sam said:

hello, after testing AutoAudit 1.09, I had a little problem. The "ALTER_TABLE" Schema of the trigger, is not activated when you change a table in design. We assume that as you use a temporary table does not exist and the real can not find it. Any solution? Thanks in advance

March 7, 2009 5:50 AM
 

RayvenUK said:

Tested this code quite extensively and found a couple of bugs.

The RowHistory does not like it when the initial row has NULL field values for the first version. The COALESCE which is used to detect whether there is a row and if not use the actual current field value is also picking up the initial NULL field value and returning the current value.  As a result, the initial row is incorrect in the history.

I've corrected this by using a SELECT CASE WHEN EXISTS instead, first checking to see if the row exists and if so using the OldValue regardless. If the row does not exist, use the actual value.

The second I'm not sure whether it is a bug or not, but when you delete a record and query the RowHistory, the initial record disappears giving you no indication of what the row originally looked like.  I'm working on a fix for this at present as I'd really like this feature.

March 8, 2009 6:43 AM

Leave a Comment

(required) 
(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