September 6, 2004 at 6:52 am
I have a stored procedure doing some inserts/updates and containing rollback
statements in case something goes wrong:
DECLARE @tc integer
SET @tc = @@TRANCOUNT
IF @tc = 0 BEGIN TRANSACTION
do an insert
IF @@error <> 0 GOTO ERROR
do another
IF @@error <> 0 GOTO ERROR
OK:
IF @tc = 0 COMMIT TRANSACTION
RETURN 0
ERROR:
IF @tc = 0 ROLLBACK TRANSACTION
RETURN 1
My problem:
If the second insert fails due to for example
Server: Msg 2627, Level 14, State 1, Procedure BrokersSave, Line 104
Violation of PRIMARY KEY constraint ...
the first insert is never rollbacked since Level is above 10 and I gets
thrown out of the procedure and the rollback statement is never executed.
How in earth does one do to have a transaction in a stored procedure in a
case like this?
How in earth do I return a returncode from the procedure in cases like this?
September 7, 2004 at 12:08 am
Precheck your referential integrity before your second insert with a IF EXISTS. eg:
IF EXISTS (Select * from primarykeytable1 WHERE typeX = @typeImAboutToInsert)
INSERT INTO foreignkeytable1 ....
ELSE
GOTO ERROR -- The primary key record doesn't exist and we're preventing an error
Julian Kuiters
juliankuiters.id.au
September 7, 2004 at 3:26 am
If XACT_ABORT is set then the batch aborts, rather than the error being trapped, that sounds like what's happening here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply