Transaction rollback question 2

[Brice]

I have a scenario where I will be updating (incrementing) a field in an MS SQLServer table multiple times within a transaction. If I call SetAbort, will all of the updates be rolled back?

Basically, the scenario goes like this:

I have a collection of order items that need to have an item# assigned to them before inserting the record in the order table. To do this, I have a lookup table that contains the batch# assigned to these orders and the most recently used item# in the batch + 1 . When the SP is called to insert the new order, I grab the item#, increment it in the lookup table (so it's ready for the next order item), and then insert the order record.

I realize all of the inserted orders should be rolled back, but what I'm wondering is if I call SetAbort, will all of the increments that were done to item# in the lookup table be rolled back so that the item# value is the first one I started with. It seems to me they should, but I want to make sure.

Let me know if this doesn't make sense and I'll try and elaborate.

Thanks,
Brice

 

[zallen]

Yes! All of the updates will be rolled back .Because updating the lookup table is in same transaction as inserting into order tabble.

Regards!

zallen@cmmail.com
Long live of freedom!

 

[AkshayB]

This will cause roll back all the transaction as it is
in a single transaction.

 

[Brice]

Thanks.

 

[Guest_imported]

Hi,
The Funda of using this is either the transaction should commit or rollback, here you are having update & insert in the same transaction so SetAbort will rollback all the changes upto that mark
Regards
BG