Tutorial :SQL Server Triggers - grouping by transactions


At work we have just started building an auditing framework for our database (i.e. to log what data has changed when it is created or updated).

We'd quite like to implement this using triggers as data sometimes gets imported into the system from other places, not just via the front-end. This seems to be a fairly common solution.

However I'd like to make one addition: if a user runs an update which updates more than one table, I'd like those to be grouped together in a batch (i.e. to generate a unique batch ID for each set of updates).

The problem is, getting a batch ID from within a trigger. Each update will run within a transaction, so I was wondering whether I could use the Transaction ID to generate a batch ID. However I can't find a way of accessing the Transaction ID anywhere within T-SQL.

Anyone have any ideas?

P.S. - We are running SQL Server 2008, if it makes any difference


you can just use select * from sys.dm_tran_current_transaction



Outside the scope of the question but something to think about when designing your audit solution. If you intend to audit records that include bulk inserts, make sure your bulk inserts all include the FIRE_TRIGGERS keywords. You also need to ensure that the triggers themselves properly handle multiple row inserts (and not, not, not through a cursor!).


Found this on the net to get the current transaction id (which you could then use to generate a batch id), but not sure if it will work or not:

SELECT TOP 1  @transactionID = req_transactionID  FROM    master..syslockinfo l  INNER JOIN    master..sysprocesses p ON l.req_spid = p.spid AND l.rsc_dbid = p.dbid AND p.spid = @@spid  WHERE   l.rsc_dbid = db_id() AND p.open_tran != 0 AND req_transactionID > 0   ORDER BY req_transactionID   

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »