Get Unique Transaction ID For The Current Transaction
-
Hi all
When multiple tables are updated in a single transaction, is there any way to tie those changes together with a unique transaction "ID" of some sort?
To clarify I'll use an example. Let's say I have 2 tables and an application which inserts a row into each (in a single transaction). I would like *something like a* default constraint on each of the 2 tables, which will insert an identical value into each for the 2 inserted rows, thereby enabling me to tie those changes together.
I hope this makes sense?
An ideal solution would work for Sql Server 2000 and up, but this is actually a hypothetical question and not something which I need to implement on an existing system.
Thanks!
-
Hi,
what about generating a uid inside the transaction:
declare @U uniqueidentifier;create table #mytab1 (x int, y uniqueidentifier);create table #mytab2 (x int, y uniqueidentifier);begin transactionset @U=newid();insert into #mytab1(x,y) values(1, @U);insert into #mytab2(x,y) values(2, @U);commit transactionselect * from #mytab1;select * from #mytab2;drop table #mytab1;drop table #mytab2; -
hjo (11/28/2008)
Hi allWhen multiple tables are updated in a single transaction, is there any way to tie those changes together with a unique transaction "ID" of some sort?
To clarify I'll use an example. Let's say I have 2 tables and an application which inserts a row into each (in a single transaction). I would like *something like a* default constraint on each of the 2 tables, which will insert an identical value into each for the 2 inserted rows, thereby enabling me to tie those changes together.
I hope this makes sense?
An ideal solution would work for Sql Server 2000 and up, but this is actually a hypothetical question and not something which I need to implement on an existing system.
Thanks!
could you explain the problem in more detail.
-
could you explain the problem in more detail.
Sure, I'll try my best...New example:
Let's say I have an application. Whenever a user updates a row in the [Client] table, 2 things happen:
1. A row is inserted into the table [UserAction].
2. The row in [Client] is updated.
Both of the actions occur in a single transaction. What I want to be able to do, is to tie the 2 actions together using some sort of ID.
I know I can implement this in the application or by using stored procedures, but I'm looking for a different way...
(PS: It doesn't have to be a GUID, any unique value will do)
-
Have a lool at my suggestion above
-
Have a lool at my suggestion above
The problem with that solution is that it's very tightly integrated with the logic. It would require changes to the specific application doing the inserts and would not be usable in a different context like a trigger. I'm looking for something a little bit more inconspicuous and less intrusive, although it would seem like I would have to go with something similar to your suggestion in the end...
Thank for the reply though.
-
The component that inserts the two rows is the only component that is aware of the fact that these rows have been imported in the same transaction. So you have to generate this information there.
The only question is where to place this component.
- In the application layer (.NET component) or
In SQL Server
If you want to do it in SQL Server than I'd say that the proper insert interface to the table is a stored proc.
We use sp_getbindtoken inside an insert/update trigger in our workflow engine. Just as you describe, we need a way to track all changes within a single transaction, but we cannot require that all code occur within a single stored procedure.
However, we have seen postings that suggest that sp_getbindtoken will be removed after SQL Server 2008. It does work fine for now, so I guess we have 3 years to discover an alternative.
Paul Rony
SplendidCRM Software
Thanks Paul, that's exactly what I was looking for...:)
Paul Rony (1/9/2009)
We use sp_getbindtoken inside an insert/update trigger in our workflow engine. Just as you describe, we need a way to track all changes within a single transaction, but we cannot require that all code occur within a single stored procedure.However, we have seen postings that suggest that sp_getbindtoken will be removed after SQL Server 2008. It does work fine for now, so I guess we have 3 years to discover an alternative.
Paul Rony
SplendidCRM Software
You wouldn't have an example of how you are doing this in the trigger would you?
I've tried this...
BEGIN TRAN;
DECLARE @bind_token varchar(255);
EXECUTE sp_getbindtoken @bind_token OUTPUT
SELECT @bind_token AS Token;
GO
...but it always seems to return the same Token value.
Your code seems correct, but are you committing or rolling back the transaction before running it a second time?
Remember that if you don't, a new transaction will not be started, it's only the transaction counter that will be incremented...
BEGIN TRAN;
update TESTTABLE set Title='MR' where StaffID='2027-sso'
DECLARE @bind_token varchar(255);
EXECUTE sp_getbindtoken @bind_token OUTPUT
SELECT @bind_token AS Token;
COMMIT TRAN
GO
Still get the same Token value every time
Just make sure that there are no open transactions left over from previous attempts.
"SELECT @@TRANCOUNT" should return 0 before you attempt to run it again.
Hope that helps, it definitely works for me...
Thanks - this works fine...
BEGIN TRAN;
DECLARE @bind_token varchar(255);
EXECUTE sp_getbindtoken @bind_token OUTPUT
SELECT @bind_token AS Token;
COMMIT TRAN
GO
...running it several times returns
jNN:fZ63e1K3Z2CE1KkC1=5---.RH---
ZQ/Ka[U;b1O3VV0I>DLZ[]5----QJ---
[T;?]BL7^QSQJh_PaOl@--5---/UJ---
;_0QPEcQeaKT@6^@?VH`<-5----jF---
...and so on.
Is there any guarantee that this value is unique?
We're using this in a trigger to audit changes to multiple tables.
I do not believe that the values are globally unique, but I suspect that they are unique for a single system.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply