Group all transaction's updates - or getting current transaction id

Hi all,
I need to find a mechanism to put triggers on tables, and when an update
takes place - group all updates which happened in the same transaction
togheter. (Thanks, nr...)
For example: Let's say i have 3 tables : Hotel, RoomType and seasson.
Now, let's say that i'm invoking a series of actions on these tables, in one
transaction. Now, i have another table, called notificatioTable. After the
transaction completes, notification table'll have the following rows:
Action TransactionId
TableName
===== ===========
=========
Insert into hotel... 6
Hotel
Insert into RoomType... 6
RommType
Insert Into seasson 6
Seasson
So i am able to know that these 3 actions happend in the same transaction.
The easiest way todo it, is to find a unique transaction id. But so far i've
found only "work-arounds" to do it.
Does anyone have any idea how to give current transaction a unique id, to
which triggers will have access?
Does anyone have any other idea how to implement this mechnism?
Thank,
IdoI don't know what work-arounds you've tried but one technique I've seen used
is to get a token via sp_getbindtoken and use the bind token as a
transaction id. However, I'm not certain if the value is guaranteed to be
unique over time.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ido Ben" <ido_ben@.hotmai.com> wrote in message
news:OfWSjmeXFHA.2768@.tk2msftngp13.phx.gbl...
> Hi all,
> I need to find a mechanism to put triggers on tables, and when an update
> takes place - group all updates which happened in the same transaction
> togheter. (Thanks, nr...)
> For example: Let's say i have 3 tables : Hotel, RoomType and seasson.
> Now, let's say that i'm invoking a series of actions on these tables, in
> one transaction. Now, i have another table, called notificatioTable.
> After the transaction completes, notification table'll have the following
> rows:
>
> Action TransactionId
> TableName
> ===== ===========
> =========
> Insert into hotel... 6 Hotel
> Insert into RoomType... 6 RommType
> Insert Into seasson 6 Seasson
>
> So i am able to know that these 3 actions happend in the same transaction.
> The easiest way todo it, is to find a unique transaction id. But so far
> i've found only "work-arounds" to do it.
> Does anyone have any idea how to give current transaction a unique id, to
> which triggers will have access?
> Does anyone have any other idea how to implement this mechnism?
> Thank,
> Ido
>|||Thanks Dan...
Well, i've concidered using sp_getbindtoken. But i don't know whether it has
side affects. I mean, after all, this sp supposed to be used when one is
trying to share a transaction between clients, and not for giving me a GUID
for the transaction. Do you know what are the side affects of this sp? What
is it doing behind the scenes? I havent succeeded to find a good answer
about this so far.
As for the work-arounds, in the mean time i think i'll simply create a new
table in the db, called ConnectionTransaction or so. This table will have
two columns: spid (server process id) and transactionId. Whenever i'll begin
a new transaction, i'll simply insert a new line into the table. spid column
will have the value of @.@.spid, and transactionId column will have a value
which will be generated using newid(). From within the trigger i'll just
pick the line from ConnectionTransaction table, using "select transactionId
from ConnectionTransaction where spid = @.@.spid". This is based on the
assumption that in a certain time point, there is no more than one
transaction in a specific connection (except of nested transactions). As for
nested transaction, i'll be able to find them checking @.@.trancount, and will
give them an id consists of the transaction id and @.@.trancount. Ofcourse,
since connections are pooled (.net) , i'll make sure that each connection
has just one line in ConnectionTransaction table (I'll just update an
existing line , if neccecery, insted of inserting a new one) .
This will work for the mean while, since i have a relatively limited amount
of transactions i need to know about.
Thanks,
Ido
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O$RJ3bgXFHA.584@.TK2MSFTNGP15.phx.gbl...
>I don't know what work-arounds you've tried but one technique I've seen
>used is to get a token via sp_getbindtoken and use the bind token as a
>transaction id. However, I'm not certain if the value is guaranteed to be
>unique over time.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ido Ben" <ido_ben@.hotmai.com> wrote in message
> news:OfWSjmeXFHA.2768@.tk2msftngp13.phx.gbl...
>|||I'm not aware of any side-affects using sp_bindtoken since there is no
requirement that the returned token be used at all or used in any particular
way. However, I honestly don't know the implementation details behind the
proc.
From you description, it seems you are mostly concerned with transactions
performed from your application code. Your approach to assign the
transaction id in app code when a transaction is started is probably the
best method. I don't see the need to address nested transactions though,
unless these are significant to you for some reason. In practice, data
modifications done in nested transactions are committed only when the
outermost transaction commits so these are actually part of the same
transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ido Ben" <ido_ben@.hotmai.com> wrote in message
news:e9ImJbhXFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Thanks Dan...
> Well, i've concidered using sp_getbindtoken. But i don't know whether it
> has side affects. I mean, after all, this sp supposed to be used when one
> is trying to share a transaction between clients, and not for giving me a
> GUID for the transaction. Do you know what are the side affects of this
> sp? What is it doing behind the scenes? I havent succeeded to find a good
> answer about this so far.
> As for the work-arounds, in the mean time i think i'll simply create a new
> table in the db, called ConnectionTransaction or so. This table will have
> two columns: spid (server process id) and transactionId. Whenever i'll
> begin a new transaction, i'll simply insert a new line into the table.
> spid column will have the value of @.@.spid, and transactionId column will
> have a value which will be generated using newid(). From within the
> trigger i'll just pick the line from ConnectionTransaction table, using
> "select transactionId from ConnectionTransaction where spid = @.@.spid".
> This is based on the assumption that in a certain time point, there is no
> more than one transaction in a specific connection (except of nested
> transactions). As for nested transaction, i'll be able to find them
> checking @.@.trancount, and will give them an id consists of the transaction
> id and @.@.trancount. Ofcourse, since connections are pooled (.net) , i'll
> make sure that each connection has just one line in ConnectionTransaction
> table (I'll just update an existing line , if neccecery, insted of
> inserting a new one) .
> This will work for the mean while, since i have a relatively limited
> amount of transactions i need to know about.
> Thanks,
> Ido
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O$RJ3bgXFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||Thanks...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLm3jj5XFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I'm not aware of any side-affects using sp_bindtoken since there is no
> requirement that the returned token be used at all or used in any
> particular way. However, I honestly don't know the implementation
> details behind the proc.
> From you description, it seems you are mostly concerned with transactions
> performed from your application code. Your approach to assign the
> transaction id in app code when a transaction is started is probably the
> best method. I don't see the need to address nested transactions though,
> unless these are significant to you for some reason. In practice, data
> modifications done in nested transactions are committed only when the
> outermost transaction commits so these are actually part of the same
> transaction.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ido Ben" <ido_ben@.hotmai.com> wrote in message
> news:e9ImJbhXFHA.3140@.TK2MSFTNGP14.phx.gbl...
>