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.
fuerteventuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
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.
0
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!)
0
chapmandewCommented:
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
begin
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)
 
 
end

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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?
0
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?
0
Scott PletcherSenior 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
Why can't you just insert the tran number?  

declare @tranid bigint

select @tranid = transaction_id  from sys.dm_tran_current_transaction
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.