Hi there,

I'm putting together a simple database auditing system (on a SQL 2005
system), using triggers to capture database changes and logging them to a
set of shadow tables. I need to also capture transactional information; that
is, be able to group the captured changes by their respective transactions.

I've found two ways of getting "transaction ID" information:

- The "sp_getbindtoken" stored procedure: unfortunately, SQL Server 2005
Books Online indicates that this feature will be removed in a future
version. So, I'm staying clear of this one.

- The "sys.dm_tran_current_transaction" view: the transaction_id column
seems perfect for what I need; it's a bigint that monotonically increases
with each new transaction. But, I've just found that this transaction_id
seems to get reset back to 0 when you stop and start SQL Server. Obviously,
this could lead to duplicate transaction id's in my auditing tables, which
is obviously not a good thing.

Can anyone suggest some other way of getting a transaction identifier unique
for the lifetime of the database?

Thanks,
Adrian

Adrian (alyba***@hotmail.com) writes:
Show quoteHide quoteI'm not sure that this is a meaningful thing, as the transction id does not
carry any useful information.

You could combine the transaction id from sys,dm_tran_current_transaction
with the starttime for SQL Server. This you can get as the logintime
for spid 1 from sys.dm_exec_sessions, I believe.

Also keep in main that if the triggers are invoked by plain users,
the users are not likely to have access to the DMV. This could be
arranged by signing the triggers with a certificate assoicated with
a login that has VIEW SERVER STATE permission.

Add a field
simple insert of strings
Complex Query
adjust my code
avoid using cursors....