IF @@ERROR <> 0 ROLLBACK TRANSACTION not working

[chillay]

Hello

I am testing my query to ensure that it rolls back to the first insert statement. It does not seem to be working correctly.

In the following query I do not understand why the first insert statement does not insert a record into the ServicePlans table when I cause a syntax error in the second insert statement.

I thought the following query would still run and save the first insert statement even if the second insert statement had an error.

When I cause an error in the second insert statement the first insert statement does not work even though I have a save point right after it (Insert1).

BEGIN TRANSACTION

INSERT INTO ServicePlans (PlanID, PlanName,
PlanDescription, PlanPrice, DiscountedPrice)
VALUES ('XXX1', 'Tele-Training Course',
'Course on Telecommunications', 45.00, 35.00);

SAVE TRANSACTION Insert1;

INSERT INTO Orders (CustomerID, PlanID,
OrderDate, CancelationDate)
VALUES (10, 'XXX1', '11/27/01', NULL);

IF @@ERROR <> 0 ROLLBACK TRANSACTION Insert1;

COMMIT TRANSACTION


Thank you

Chillay

 

[ousoonerjoe]

You might want to look at the MSDN. The examples and explanations they show are in a different order than what you have displayed here. I'd offer more suggestions, but I have never needed to use a "save point" and thus am not all that familiar with them. I always approach a process with an "All or Nothing" mentality. That way it helps prevent duplicates entries and half entered records. I would also suggest looking at a TRY/CATCH wrapper to help manage the BEGIN/COMMIT/ROLLBACK Transactions.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[chillay]

Good advice.

Thank you.

Chillay