November 5, 2003 at 3:46 am
Dear All
I was wondering if an expert could have a look at this to see what is wrong.
I am trying to get a rollback to work, do it I am purposely putting in an error then attempting a rollback. What is happening is that all the non unique records are being inserted, when the should of been rolled back.
create table tblTempTableTwo
(
ID int not null PRIMARY KEY,
col1 int
)
BEGIN TRANSACTION
INSERT INTO tblTempTableTwo values (2, 1)
if @@ERROR <> 0 GOTO TRAN_ABORT
INSERT INTO tblTempTableTwo values (3, 56)
if @@ERROR <> 0 GOTO TRAN_ABORT
INSERT INTO tblTempTableTwo values (3, 56)
commit transaction
goto FINISH
TRAN_ABORT:
ROLLBACK TRANSACTION
FINISH:
GO
SELECT * FROM tblTempTableTwo
GO
RESULTS...
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_tblTempTableTwo'. Cannot insert duplicate key in object 'tblTempTableTwo'.
The statement has been terminated.
(2 row(s) affected)
Thanks
J
November 5, 2003 at 3:55 am
You've missed out the "if @@ERROR <> 0 GOTO TRAN_ABORT" check after the last insert.
November 5, 2003 at 4:09 am
I think the term 'DUH' comes to mind, sorry for wasting your time, and thanks.
J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply