Is it possible to group Audit Logs by a Transaction Id

Sep 8, 2014 at 3:09 PM
Is there somehow a way to store additional informaitons in the AuditHeader.

Our requirement is to save and display all audit logs by a Transaction Scope.
The users want to see, what has been saved within one save transaction (All modified Tables with a given save transaction) and then group by that transaction. (to get a better overview of wich user has saved what and when)

We are already thinking to reuse the Application or HostName and store some unique ID per save transaction. But maybe you have already something better in your mind to add it to the super fantastic AutoAudit.
Nov 17, 2015 at 5:27 PM
Yes, I desperately need the TransactionId to group AuditHeader records also. I may consider contributing that code to this project if there's interest since we need it ASAP. Anyone know of any technical reason that wouldn't work with the current implementation (assuming we add a new TransactionId to the AuditHeader table)?
Coordinator
Nov 17, 2015 at 7:13 PM
Here's what I recommend as the easiest method to implement this feature...

The RowDescription column is not used by default in the AuditHeader table.
You could use that to store the Transaction_ID.

This solution assumes that you are currently not using the RowDescription column in the AuditHeader table.


How to implement:

-1. Modify the [Audit].[pAutoAudit] stored procedure (Open the stored procedure as an ALTER statement). Save your current copy somewhere in case you run into problems.
-2. Find the following text "+ ' @NestLevel TINYINT' + Char(13) + Char(10)"
-3. on a NEW LINE immediately after the one that you found, add this code:
   + '  ,@MyTransID BIGINT' + Char(13) + Char(10)
You will end up with something like this:
+ '  @NestLevel TINYINT' + Char(13) + Char(10)
+ '  ,@MyTransID BIGINT' + Char(13) + Char(10)
Repeat step 2 and 3 THREE times to apply this change to the for "build insert trigger" "build update trigger" and "Build delete trigger" sections

-4. find the following text "-- Row Description (e.g. Order Number)"
On THAT LINE of code, change "NULL" to "@MyTransID"
You will end up with something like this:
      + '        @MyTransID,     -- Row Description (e.g. Order Number)' + Char(13) + Char(10)   
Repeat step 4 FOUR times to apply this change to the for "build insert trigger" "build update trigger" (update has two occurences) and "Build delete trigger" sections

-5. find the following text "SET @AuditTime = GetDate()"
-6. on a NEW LINE immediately after the one that you found add this code:
  + 'select @MyTransID = transaction_id from sys.dm_tran_current_transaction' + Char(13) + Char(10) + Char(13) + Char(10)
You will end up with something like this:
   + 'SET @AuditTime = GetDate()' + Char(13) + Char(10) + Char(13) + Char(10)
  + 'select @MyTransID = transaction_id from sys.dm_tran_current_transaction' + Char(13) + Char(10) + Char(13) + Char(10)
Repeat step 5 and 6 THREE times to apply this change to the for "build insert trigger" "build update trigger" and "Build delete trigger" sections

-7. Save the updated stored procedure for safe keeping.
-8. Execute the script for the modified stored procedure to update the code in your database.
-9. Create a test table, add the new AutoAudit triggers to it with [Audit].[pAutoAudit]
-10. test by executing implicit (single statement) DML statements on your test table
-11. test by executing several DML statements within a Begin...Commit explicit transaction.
-12. Look at the data in the RowDescription column of the Audit.AuditHeadertable to see the Transaction_ID's
-13. When you are satisfied this is awesome, Execute [Audit].[pAutoAuditRebuildAll] to rebuild all of your existing AutoAudit triggers
-14. That's it. You're done.

Enjoy!
John
Nov 23, 2015 at 9:07 PM
Boom, you nailed it! I made the changes you outlined and the transaction ids are showing up in the RowDescription as expected, thanks John!