AuditBaseTables table problem

Dec 27, 2013 at 10:18 AM
Edited Dec 27, 2013 at 10:20 AM
Hi,
pls. note that delete, update and insert triggers are fired only if there is record for Schema+Table in AuditBaseTables and there EnabledFlag=true

That is fine, unless you need to deploy DB changes to another SQL server.
If you use DB schema compare tool like DbDiff, SQL Compare, Schema Compare, etc then auditing will not work for new tables.
By schema compare you modify target DB, add new tables, even add autoaudit triggers. But since DB compare tools do not modify data in tables, thus not adding records for such new tables to AuditBaseTables. Therefore autoaudit triggers will not work.
And you have to add somehow to target DB Audit.AuditBaseTables table proper records for new tables.
Or you have to disable check in delete, update, insert triggers

Look for '--get the EnabledFlag setting from the AuditBaseTables table' in AutoAudit sql script and delete or comment following code (3x in AutoAudit script)
  • '--get the EnabledFlag setting from the AuditBaseTables table' + Char(13) + Char(10)
  • 'IF NOT EXISTS (SELECT 1 FROM ' + quotename(@AuditSchema) + '.[AuditBaseTables] '
  • ' WHERE [SchemaName] = ''' + @SchemaName + ''''
  • ' AND [TableName] = ''' + @TableName + ''''
  • ' AND [EnabledFlag] = 1)' + Char(13) + Char(10)
  • ' BEGIN' + Char(13) + Char(10)
  • ' IF @DebugFlag = 1 PRINT ''AutoAudit EnabledFlag set to "false" for this table in the AuditBaseTables table. Exiting trigger...''' + Char(13) + Char(10)
  • ' return' + Char(13) + Char(10)
  • ' END' + Char(13) + Char(10) + Char(13) + Char(10)
Then do not forget to run pAutoAuditRebuildAll sproc
Coordinator
Dec 30, 2013 at 11:43 AM
As you have highlighted, the AutoAudit triggers look in the AuditBaseTable to check if the AutoAudit triggers are enabled or not.

Here is what I would suggest you do if you are migrating tables/data to another database and you wish AutoAudit to be active for the transfer:
  • Install AutoAudit in the destination database if it is not already there
  • Synchronize the data for the AuditBaseTable table
  • Transfer the table object schema (including AutoAudit trigger)
  • Transfer the table data
By having first copied the AuditBaseTable data, The AutoAudit triggers will execute normally without the need for commenting out the EnabledFlag setting code in the triggers.

Keep in mind that SQL Server does not fire triggers if your data transfer tool uses bulk copy to transfer the data.

John