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
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