Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

RAISERROR and ROLLBACK

1,965 views
Skip to first unread message

Tumurbaatar S.

unread,
May 10, 2002, 3:22:35 AM5/10/02
to
If in trigger or procedure I need to cancel current operation
(insert/update/delete),
I use RAISERROR function. But do I need to call ROLLBACK TRANSACTION
inside trigger or proc? Or sql engine automatically rolls back transaction
if it encounters
error?

Darren Brinksneader

unread,
May 10, 2002, 7:18:18 AM5/10/02
to
Only if you use severity levels between 19 and 25 will it terminate the
current batch process, which will cause a ROLLBACK. So, typically you will
have to put a ROLLBACK TRANSACTION if you need it.

Make sure you put the RAISERROR and ROLLBACK TRANSACTION in a BEGIN END
block, or it will always ROLLBACK.

ex.

Don't do..

IF <<condition>>
RAISERROR('You are a Bonehead!',10,1)
ROLLBACK TRANSACTION

this will do the RAISERROR if the condition is met, but will always do the
ROLLBACK TRANSACTION

Do this...

IF <<condition>>
BEGIN
RAISERROR('You are a Bonehead!',10,1)
ROLLBACK TRANSACTION
END

--
Darren Brinksneader
MCDBA, MCSE, CCDA, MCT, CTT+, CCA, CNE
Chief Technologist
A Technological Advantage, Inc.

"Tumurbaatar S." <tu...@datacom.mn> wrote in message
news:#Z2K8N$9BHA.1516@tkmsftngp02...

Jorge Escalera

unread,
May 10, 2002, 5:48:18 AM5/10/02
to
Please tell me who to cancel a Store Procedure From Visual Basic

TKS
"Tumurbaatar S." <tu...@datacom.mn> escribió en el mensaje
news:#Z2K8N$9BHA.1516@tkmsftngp02...

Brandon Lilly

unread,
May 10, 2002, 3:45:26 PM5/10/02
to
"Darren Brinksneader" <dbrink...@nospam.atai.com> wrote in message
news:#rrcsRB#BHA.2308@tkmsftngp02...

> Only if you use severity levels between 19 and 25 will it terminate
the
> current batch process, which will cause a ROLLBACK. So, typically
you will
> have to put a ROLLBACK TRANSACTION if you need it.

Is it recommended to ROLLBACK manually in the trigger, or to RAISERROR
with a severity of 19-25?

Brandon
--
"In the beginning the universe was created. This has made a lot of
people very angry, and has been widely regarded as a bad move." -
Douglas Noel Adams (1952-2001)
[Please remove "nospam_" from email address to reply.]

Darren Brinksneader

unread,
May 10, 2002, 4:13:59 PM5/10/02
to
Always! put the ROLLBACK.

Severity of 19 will still process the delete (if no ROLLBACK is there).

Severity of 20 - 25 will break the connection. (Make sure you specify WITH
LOG with the RAISERROR).

So, I say ALWAYS!!! (just in case you put the wrong severity level)


HTH

--
Darren Brinksneader
MCDBA, MCSE, CCDA, MCT, CTT+, CCA, CNE
Chief Technologist
A Technological Advantage, Inc.


"Brandon Lilly" <blilly@nospam_medevolve.com> wrote in message
news:u#PbGuF#BHA.1868@tkmsftngp04...

Tumurbaatar S.

unread,
May 11, 2002, 3:54:05 AM5/11/02
to
Thank you!
And here is one question related to this topic.
Do I need to check for SQL errors after each SQL
statement? Currently I do so:

...some Transact-SQL expression, e.g. SELECT/UPDATE/INSERT/DELETE/EXEC...
SET @err = @@ERROR
IF @err <> 0
BEGIN
RAISERROR(@err, 16, 1) WITH SETERROR
ROLLBACK TRANSACTION
RETURN
END

In this case, about half of my script code repeats
above check block. What do you think I do unnecessary
thing?


"Darren Brinksneader" <dbrink...@nospam.atai.com> wrote in message
news:#rrcsRB#BHA.2308@tkmsftngp02...

Tibor Karaszi

unread,
May 13, 2002, 3:13:59 AM5/13/02
to
To get the code down a bit, you can jump to a label with GOTO. Something along the lines of:

INS...
IF @@ERROR <> 0 GOTO ERR
UPD...
IF @@ERROR <> 0 GOTO ERR
DEL...
IF @@ERROR <> 0 GOTO ERR
RETURN
ERR:
RAISERROR...
ROLLBACK
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver


"Tumurbaatar S." <tu...@datacom.mn> wrote in message news:#r3uLEM#BHA.2040@tkmsftngp04...

0 new messages