Re: rollback transaction not rolling back
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 10/21/04
- Next message: Adam Machanic: "Re: View won't save, but will execute"
- Previous message: Adam Machanic: "Re: = VS <>"
- In reply to: Mike D: "Re: rollback transaction not rolling back"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 21 Oct 2004 21:43:15 +0200
On Thu, 21 Oct 2004 11:09:02 -0700, Mike D wrote:
>Thanks for the reply and the link. So from that article I added the Print
>line and when I get an error it doesn't print 'Error Occured' So does that
>mean I will not be able to rollback this transaction?? This doesn't seem
>right.
Hi Mike,
You should save the error code DIRECTLY adter the statement that may cause
an error. Your current logic looks like this
WHILE ...
BEGIN
do something
do something
INSERT statement that might fail
-- now, @@error might be non-zero.
END
SET @err = @@error -- @@error already reset to 0.
IF @err > 0
...
You shouyld change this to
WHILE ..
AND @err = 0 -- THIS LINE IS ADDED!!!!
BEGIN
do something
do something
INSERT statement that might fail
SET @err = @@error -- MOVED THIS LINE!!
END
IF @err > 0
...
> I am trying to trap a Cannot insert duplicate key in object
>'Tab_Inst_Schedules' error. Do I query to see if it exists first??
Either that, or you change the INSERT .. VALUES to an INSERT ... SELECT
and include a WHERE NOT EXISTS clause to prevent the duplicate key error
from happening. Good code should always _prevent_ errors (and it should
still trap errors - but only to handle _unforeseen_ situations).
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Adam Machanic: "Re: View won't save, but will execute"
- Previous message: Adam Machanic: "Re: = VS <>"
- In reply to: Mike D: "Re: rollback transaction not rolling back"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
