November 28, 2008 at 2:27 am
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!
November 28, 2008 at 3:15 am
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 transaction
set @U=newid();
insert into #mytab1(x,y) values(1, @U);
insert into #mytab2(x,y) values(2, @U);
commit transaction
select * from #mytab1;
select * from #mytab2;
drop table #mytab1;
drop table #mytab2;
November 28, 2008 at 3:34 am
hjo (11/28/2008)
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!
could you explain the problem in more detail.
November 28, 2008 at 4:08 am
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)
November 28, 2008 at 4:50 am
Have a lool at my suggestion above
November 28, 2008 at 5:13 am
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. 🙂
November 28, 2008 at 6:11 am
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 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.
January 9, 2009 at 8:51 pm
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
January 13, 2009 at 2:21 am
Thanks Paul, that's exactly what I was looking for...:)
January 13, 2009 at 5:45 am
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.
January 13, 2009 at 5:57 am
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...
January 13, 2009 at 6:02 am
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
January 13, 2009 at 6:17 am
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...
January 13, 2009 at 6:24 am
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.
January 13, 2009 at 6:41 am
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