SQL Server auditing trigger - ID to link ammendments

I have created a trigger that will audit all changes to the database. I have copied this trigger to all tables in my database so that every single amendment is logged.
My front end application makes amendments to the databse and sometimes, it makes multiple changes to different tables in the same transaction. When this happens, I would ideally like to reflect this in the audit table. What I mean by that is have someway of being able to tell which changes were part of the same transaction. For example, clicking Complete in the front end app writes a line to the sales table and decreases the stock in the stock table (just one of many possible examples). This would make 2 additions to the audit table and it would be great if there was a way in which I could tell from the audit table that those two changes happened as part of the same action, without having to make any custom development from the front end. For example, if they both had the same timestamp to the millionth of a second, I could convert the time and date to a number and that would be the unique identifier. The reality however is that I know that each trigger is activated independently.
Is there a way of achieveing what I want? Any suggestions or similar concepts will help set me off in the right direction  many thanks for looking.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Scott PletcherConnect With a Mentor Senior DBACommented:
Perhaps, but only if you had one person at a time doing the modifications.  Otherwise it won't be able to tell Person A's mods from Person B's.
One way to do this is to log the transaction id in the trigger for the batch.  So, in your trigger you could add this:
declare @tranid bigint

select @tranid = transaction_id  from sys.dm_tran_current_transaction

This will be unique for different transactions (even select statements).  Then just insert this value into your audit tables.  You'll have to do a bit of custom coding, but that is about it.
fuerteventuraAuthor Commented:
Many thanks for your reply - do you mean to declare as a global variable or something?
Not quite sure I've understood your suggestion - each table would have it's own primary key which to my mind, would mean that the primary key value would be stored in the audit table, but would be different for each audit entry and wouldn't link to any other entry, even if made at the same time?? Hope I'm making sense (but I doubt it - I'm new to all of this!)
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Well, kinda.  It would be global to the trigger, which would run in a unique transaction scope.  The audit tables may have primary keys (or even the underlying tables), but you could insert multiple records into the auditing table from within the same transaction...so I imagine that you would want to insert this transaction id as an indicator into your auditing tables to let you know that those records were inserted from the same process.  Does that make sense?  Your trigger may look something like this:

This will allow you to associate what records are updated by different transactions
create trigger tr_tabletrigger on table
for insert, update, delete
declare @tranid bigint
select @tranid = transaction_id  from sys.dm_tran_current_transaction
--only 1 record in table always
insert into tableaudit(val1,val2.....transactionid)
select val1, val2...@tranid
from inserted (or deleted)

Open in new window

Scott PletcherSenior DBACommented:
Really the app needs to address this, by sending in something unique for every task that it does.

From the SQL side, whether you can do it or not is going to depend on how the app does the multiple changes.  Does it do all the modifications in one batch?  Does it send the statements to SQL all at once or one at a time?  Does it do everything in one transaction or in multiple transactions?
fuerteventuraAuthor Commented:
Both really - there could be multiple modifications in one batch or it could send them all in one batch. It can do anything in one transaction or in multiple transactions and this is why it will be tricky doing it from the SQL Server side. I know it would ideally be done from the front end.
One idea I thought of would be the follwing:
When a trigger is activated, it checks the audit table to see if a modification has been applied to the database in the last 0.2 seconds (could be greater or less than 0.2 seconds). If there is, it uses the unique id that has been generated for that modification. if not, it creates it's own id which could be a time down to milliseconds converted into a number.
Do you think this could be feasible?
chapmandewConnect With a Mentor Commented:
Why can't you just insert the tran number?  

declare @tranid bigint

select @tranid = transaction_id  from sys.dm_tran_current_transaction
All Courses

From novice to tech pro — start learning today.