No data being added to AuditDetail table

Jan 14, 2014 at 10:54 AM
Hello guys,

I have just downloaded your project, and all seems excellent so far apart from I can't seem to get details of updates to be added to AuditDetail table no matter what configuration settings I use. Anyone have a similar experience?

I don't see a setting for this. Can you confirm, also I tried turning on DEBUG mode but I can't figure out where the output for this goes?

I apologise for my stupid questions, I am using the latest version, 3.30a.

Thanks,
Ed
Coordinator
Jan 14, 2014 at 12:19 PM
Hi Ed,

When you turn on debug mode the output goes into the messages tab of SQL Server Management Studio.

My guess for the cause of your problem is that you have setup AutoAudit to fully log insert and delete transactions but not updates. The default of course is to log everything.

Run this query to see what the settings are for your table(s):
Select * from Audit.AuditBaseTables
In the LogInsert, LogUpdate and LogDelete columns, a value of 0, 1 or 2 will be set.
0 = do not log (AutoAudit is turned off for that action)
1 = log minimally (AutoAudit just save the AuditHeader data but not the AuditDetail for that action)
2 = log fully (AutoAudit saves the AuditHeader and AuditDetail data for that action)

So, if you don't have a 2 in the LogUpdate column, then that is the reason for the behaviour you are getting from AutoAudit.
In order to change that setting, re-run the pAutoAudit stored procedure.

for example the following script would activate AutoAudit with full logging on insert, update and delete for all columns, add the audit time/user/version ddl columns to the table and log the SQL command to the AuditHeader table.
EXECUTE [Audit].[pAutoAudit]  
   @SchemaName = 'dbo'
  ,@TableName = 'myTable'
  ,@ColumnNames = '<All>' --this is the default setting
  ,@StrictUserContext = 1 --this is the default setting
  ,@LogSQL = 1 --the default setting is 0
  ,@BaseTableDDL = 1 --the default setting is 0
  ,@LogInsert = 2 --this is the default setting
  ,@LogUpdate = 2 --this is the default setting
  ,@LogDelete = 2 --this is the default setting
GO
In order to change your settings, simply re-run pAutoAudit for your table with the appropriate parameters. The triggers will be re-built according to the new settings.
Let me know if this solves your issue.
John
Jan 14, 2014 at 7:49 PM
Hi John,

I am afraid it was just me being stupid!
I was interpreting @LogInsert, @LogUpdate, @LogDelete was bools, setting LogUpdate to 2 and then running pAutoAuditRebuildAll fixed my issue.

Thanks a million!
Ed