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

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
 

Richard Pruitt said:

It would be nice if you updated the script to work with tables that may be named a reserved word by using the appropriate brackets. For example I inherited a table called 'Order' and this table causes issues with your script. I edited it to fix it for me but you may want to update for everyone else. Otherwise it is pretty nice! Appreciate...

July 22, 2009 10:22 PM
 

Richard Pruitt said:

Also noticed that if I have a table that has a column named 'Operation' then it causes several errors.

During AutoAudit it causes the following:

Msg 4506, Level 16, State 1, Procedure UserEventLog_RowHistory, Line 10

Column names in each view or function must be unique. Column name 'Operation' in view or function 'UserEventLog_RowHistory' is specified more than once.

During AutoAuditDrop it causes the following:

Msg 4506, Level 16, State 1, Procedure UserEventLog_RowHistory, Line 10

Column names in each view or function must be unique. Column name 'Operation' in view or function 'UserEventLog_RowHistory' is specified more than once.

July 22, 2009 10:33 PM
 

rudyhinojosa said:

I was hoping to find an updated version that supports multiple primary keys.  If time is an issue can you give us a code snippet we can work with to get us going in the right direction.  I'm guessing the audit table will require 2 or 3 additional fields primarykey2, primarykey3 and/or primarykey4.  The additional primary key fields will have to be flagged as null since not all tables will have more than one primary key field.  Advance the trigger update IF statement to include a case statement if primary keys are more than one, populate their key into the respective fields...does that sound about right?

November 20, 2009 5:59 PM
 

Rudy Hinojosa said:

I've revised Pauls scripts to handle up to 3 primary keys if anyone is interested, drop me a note at rudyh30@yahoo.com

November 30, 2009 2:14 PM
 

Rudy Hinojosa said:

Currently engaged auditing. Everything works fine, but some access 97 users get random weirdness when they use the classic Shift+Enter to save form data.  Normally the form data stays on the screen static to allow them to make a form change on the same record and just hit Shift+Enter to save data again...well now with auditing enabled the form randomly loads another record post hitting shift+enter.  No errors, but was just wondering if anybody has experienced this or heard of it.  I wonder if upgrading to access 2000 or higher might fix the issue.

December 3, 2009 1:42 PM
 

RiyazKhan said:

Great Work. You are really SQL Champion. It is very useful for audit trail and it saved me a lot of time. :). Keep up the nice work.

January 30, 2010 3:49 AM
 

Anonymous said:

'ntext' needs to be included in the list of excluded types.

February 11, 2010 11:37 AM
 

Paul Nielsen said:

AutoAudit is a free SQL Server (2005, 2008) Code-Gen utility that creates Audit Trail Triggers with:

March 17, 2010 11:19 PM
 

Rono said:

The process errors out on a table named User when I run pAutoAuditAll.  The error is:

Incorrect syntax near the keyword 'User'.

It appears that it needs to have brackets around the table names to handle tables named as keywords.  I'm using version 1.10

March 24, 2010 11:47 AM
 

Paul Nielsen said:

If you're using AutoAudit, please update to the newest version on CodePlex:

http://autoaudit.codeplex.com/

thanks,

-Paul

March 29, 2010 6:19 PM
 

Rono said:

Note the bug in determining if the SchemaAudit table is already in the database:

-- remove Schema Table

IF Object_id('Audit') IS NOT NULL

 DROP TABLE dbo.SchemaAudit

-- remove Audit table

IF Object_id('Audit') IS NOT NULL

 DROP TABLE dbo.Audit

March 30, 2010 2:42 PM
 

Rono said:

The drop script drops the Created, Modified and RowVersion columns, but not CreatedBy or ModifiedBy.  I presume this is an oversight.

March 30, 2010 3:11 PM
 

Pankaj said:

I added code in the pAutoAuditDrop to remove constraints corresponding to CreatedBy and ModifiedBy cloumns and then added code to drop these two columns. However, I have one issue with pAutoAuditDrop. It does not work for a single table. But, when pAutoAuditDropAll is executed, it removes all footprint of AutoAudit from the database except 4 storedprocedures. I was wondering, why pAutoAuditDrop does not work for a sigle table, but for the entire database?

Thanks for the excellent work. Please keep up the good work.

August 3, 2010 4:04 PM
 

Geert said:

Just wondered: How does this detail with failed updates, failed for one reason or another, say dupl key or not being able to pass certain chacks? Or say a rollback??

Is a record like that recorded as a new record (insert) or not?

From what I recall the trigger events do occur before the actual commit.......

September 26, 2010 8:08 AM
 

Michael said:

Please publish the next version of SQL 2008 Super Bible in Kindle format.

November 12, 2010 10:30 AM
 

Paul Nielsen said:

Hi Michael,

Thank you for the vote of confidence, however, I've decided not to write another SQL Server Bible edition. As much as I enjoy writing, it's simply too much work for extremely little pay.

-Paul

November 12, 2010 10:50 AM
 

Paul Nielsen said:

Geert, a failed transaction rolls back the entire transaction including the trigger DML statements.

-Paul

November 12, 2010 10:51 AM
 

Sambusa said:

Hi,

I'm testing version 1.1. Could anyone please advise how to remove the SQL logging.

Version 2.0 doesn't seem to update modified and rowhistory columns.

Thanks,

Sambusa

November 30, 2010 6:15 PM
 

Paul Nielsen said:

Sambusa, The docs are at the top of the SQL script. There are parameters to control the behavior.

-Paul

November 30, 2010 7:14 PM
 

TD said:

anyone else seeing this?

Msg 1934, Level 16, State 1, Procedure SchemaAuditDDLTrigger, Line 23

SELECT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Getting this when I run a CREATE NONCLUSTERED INDEX statement

December 22, 2010 9:20 AM
 

Paul Nielsen said:

TD, have you downloaded the newest version from CodePlex.com/autoaudit?

December 22, 2010 10:46 AM
 

KM said:

Thanks for the wonderful script! It will come in very handy.

One issue that I've noticed: When I try to use pAutoAuditDrop (not pAutoAuditDropAll) for a specific table, it doesn't work. I get an error in the _Audit_Insert function ... "Invalid column name RowVersion" (sorry, I'm paraphrasing from memory).

The solution I came up with was this:

In your code, pAutoAuditDrop code runs things in the following order:

- drop default constraints

- drop columns from table

- drop triggers

- drop view

- drop udf

I moved things around to the following order, and it worked fine:

- drop triggers

- drop default constraints

- drop view

- drop udf

- drop columns from table

Other than that, everything's great - many thanks for an excellent script and an even more excellent book! :)

June 6, 2011 3:16 PM
 

KM said:

A couple more tweaks I had to make to the code due to the fact that I was adding to an existing live database (with existing data):

In pAutoAudit, I added this after every ALTER TABLE ... ADD" line:

   SET @SQL = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET [ColumnName] = [OldDefaultValue]'

   EXEC (@SQL)

(Replaced [ColumnName] with the column name, and [OldDefaultValue] with a suitable value for existing data.) I know it would have been far more efficient to update everything at once at the end, but I'm still quite a beginner at SQL Server, and I wanted to play it safe. It's just a one-time run anyway. :)

This was necessary because I would run into errors every time I tried to edit an pre-existing row, due to the NULL constraint being violated.

I also added DROP CONSTRAINT and DROP COLUMN lines in pAutoAuditDrop for CreatedBy and ModifiedBy.

June 6, 2011 4:12 PM
 

Paul Nielsen said:

KM, Which version of AutoAudit are you using?

-Paul

June 6, 2011 4:28 PM
 

KM said:

1.10e - I'd just downloaded it a couple weeks ago. :)

June 6, 2011 5:39 PM
 

Paul Nielsen said:

version 2.00h is on CodePlex, it's just not the default download.

June 6, 2011 5:52 PM
 

Dickson said:

You are God Sent man!

June 15, 2011 4:48 AM
 

Chau said:

works for me...however the updated columnname is not recorded in the audit table. also the updated row number should be recorded.

August 5, 2011 11:35 AM
 

Chau said:

Correct... columnname was recorded correctly.  I was just wrong

August 5, 2011 3:42 PM
 

Vince Swann said:

Very nice. Here's a link to a similar SQL Server audit codegen that I wrote a while ago:

http://www.dbvis.com/forum/thread.jspa?threadID=3174&tstart=0

It is all self-contained in a single SP that sits in the master database.

November 4, 2011 8:19 AM
 

GANESH.R said:

hi Mr. Paul Nielson

i have a problem on similar lines . i Have a project to complete

Problem : i need to audit trail some tables. (we have ruled out any trigger approaches).

The existing systenm is not an effetcive one. I need to change the approach for audit trails.

Pls help me

eg. we do update & insert on a table xyz based on some conditions .

Existing system:

loop

if (condition=true) then

get the old values for the record

update table xyz .( i may update 1 or more than 1 value)

package call to pkg_audit_trail( This will insert

old & new value of xyz table for this record in audit trail table

rowwise.

That is if i update 2 columns in xyz then 2 rows are created in audit trail table

in this i will make 2 call to pkg_audit_trail with old and new values harcoded

else

insert into tabele xyz;

package call to audit_trail( This will insert

old & new value of xyz table for this record in audit trail table

rowwise.

That is if the table has 20 columns in xyz then 20 rows

are created in audit trail table.

in this i will make 20 call to pkg_audit_trail )

end loop;

December 19, 2011 9:03 AM
 

Paul Nielsen said:

If you rule out triggers, then you have to ensure thast every proc or data access layer writes the the audit table. I've personally seen several examples of audit systems without triggers and every example had holes in the audits from some procs or UI code that didn't include the audit code.

What you're describing will be slower and more error prone that triggers.

December 19, 2011 11:30 AM
 

BinToBin Audit Trail said:

Audit Trail (Data Activity Monitoring) is a tool through which we can record all user’s activity on the database, where users are interacting with database directly or through application. It is very useful for those who wish to keep eyes on user activity with data in the database. Also it is helpful for Q.A. (quality assurance team) who is testing application and wish to know data flow before to approve application. This tool is quite easy to manage and view report than other tools. The person who is not DBA can play with this tool easily. This tool is designed for management/administration department to audit data in database.  It is just like a CC camera on database.

January 17, 2012 2:54 PM
 

Paul Nielsen said:

to: BintoBin

Posting an ad for a $500 audit tool on this blog is despicable.

-Paul  

January 17, 2012 3:37 PM
 

S. Gupta said:

I downloaded the code.  Works fine except for inserting new records.  Whenever I insert a new record using SSMS, I get a red circle with an exclamation next to the new inserted row stating:

"This row was successfully committed to the database.  However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only.  To fix this problem, please re-run the query."

The updates and deletes work with no problem.  If I close the table and re-open it, the red circle is gone and the record IS there.  However, this become a HUGE problem when ODBC connecting the SQL table to a front-end Microsoft Access db application.  Users who have inserted new records, have to close the Access application.  Then re-open it to be able to edit.  PLEASE HELP......you have a wonderful auto trigger mechanism.

February 6, 2012 1:05 PM
 

Jesse Shade said:

I have an updated version of 1.08 that has some of the 1.09 fixes in it.  I made it work with up to 6 primary key columns.  If you're interested in the code email me jesse.shade@gmail.com.

February 8, 2012 3:46 PM
 

David92595 said:

I Love this Audit Trail!!

I do have one question though, I've noticed that there is a bit of a lag in audit table.  Does anyone know how long the lag is?  Was it done on purpose or is it just my database? (I have a small database with only a few thousand records, so I don't think that's what it is)

April 20, 2012 9:17 PM
 

Aubrey said:

Is there a version that works with GUID primary keys?

April 24, 2012 3:23 PM
 

Richard said:

Just want to say this is a very noteworthy exercise. Thank you for making this available.

Is a non-beta release of v2 still on the cards? I'm sure v1.10e is perfectly adequate for my purposes but I thought I'd ask anyway.

September 11, 2012 10:08 PM
 

Jim Harvey said:

I ran into this...I was trying to update some data in one column, About 1500 records out of 43357, What is this telling me? Becised the fact it won't update. BTW I'm change a text 1L to a text 1.

Msg 50000, Level 16, State 2, Procedure tFlourPackerLog_Audit_Update, Line 451

String or binary data would be truncated.

Msg 3616, Level 16, State 1, Line 1

Transaction doomed in trigger. Batch has been aborted.

November 28, 2012 5:27 PM
 

Vasyl said:

I found probably error code in "autoaudit 1.10e.sql"

IF Object_id('Audit') IS NOT NULL

 DROP TABLE dbo.SchemaAudit

It have to be:

IF Object_id('SchemaAudit') IS NOT NULL

 DROP TABLE dbo.SchemaAudit

or I miss something?

October 27, 2013 9:39 PM
 

ROS said:

See http://autoaudit.codeplex.com/ to get updated versions

December 11, 2013 8:19 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