December 5, 2003 at 4:18 am
Hi, I am having a problem with rolling back transactions in my database. I want to update a couple of tables using a stored procedure, but cancel any transaction that occurs WITHOUT incrementing or changing anything.
I tried using a ROLLBACK transaction which prevents any INSERT, but for some reason it increments the identity field.
e.g. ID = 4, then a failure, next ID = 6
the code is something like...
CREATE PROC proc (@inputvals ...)
AS
BEGIN TRANSACTION
SAVE TRANSACTION tran
insert table1() VALUES()
insert table2() VALUES()
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION tran
END
COMMIT TRANSACTION
I would appreciate any input or ideas to where I am going wrong...
Thanks
December 6, 2003 at 12:18 pm
You're not doing anything wrong; that's how the identity property works. For this reason, one should not rely on the identity property always incrementing without gaps. Think of it instead as a guaranteeing a unique number that increases (or decreases) in the direction of its increment.
--Jonathan
--Jonathan
December 6, 2003 at 12:22 pm
correct, it appears the identity is not part of the transaction. The main reason is that if someone else were to run this with you, you wouldn't want them to be blocked from insrting a row because you held a "lock" on the identity area.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply