The problem seems to be solved.
Now I have an idea which should be the solution:
In the triggers I use an application lock with the name (@LockName = ) 'T'+cast(@@spid as varchar) using the procedures sp_getapplock and sp_releaseapplock.
Which means of this it should be possible check out the transaction membership:
*) request the lock with Update Mode and a very low timeout If it is already set (received lock request period timeout) then use the stored generated unique ID which was already generated for this transaction by another trigger execution.
*) If the lock was requested successfully (1st trigger execution in the transaction) then write some unique ID for the current transaction in a table with primary key (spid).
*) The lock is released automatically when the transaction ends because the application lock is a common lock with the same behaviour as any other lock.
Thanks for all you suggestions !
regards FZ
-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x62909113.[Email address protected]
Sent: Dienstag, 29. Juni 2004 21:40
To: LazyDBA Discussion
Subject: getting current transaction ID
Hi!
I would need a way to get the current transactionID of the connection (Sql Server 2000 SP3).
The reason for this is that want to create triggers on some tables which write update-, insert- and delete- history into another table from where this change history (only the committed entries) is picked up for notification to another service and I want to notify changes grouped by transaction so that the subscriber of this notifications doesnt get inconsistent data.
I am not using replication for this because the changes should be transmitted as fast as possible.
I didnt find such a connection property or function for reading the current transation ID in the BOL so far.
Does anybody know how such transaction ID can be read in T-SQL if this is possible at all ?
regards FZ
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page