Exceptions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exceptions

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-04 : 19:55:29

Hey all :),

just a simple query for ya

i have a exception being raised in a sp and i want to call a rollback tran after the error is raised

now the sp is called from a trigger, i understand there is a automatic transaction that beings in a trigger and there isn't one in a stored proc, my question: is there a way to call rollback tran that will rollback everything the sp has done and everything the trigger has done as well?

because if i strated a tran in the sp it would only rollback the sp right?

the only way i can think to do it so far is to just return out of the sp giving back a int -1 if error raised 0 if not something like that

is there a better way?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-05 : 01:39:33

A rollback will rollback everything to the outermosst transaction so a rollback in a sp callled from a trigger will rollback everything the trigger has done.

from bol
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0.

try it and see
create table a ( i int, j int)
drop proc sp1
create proc sp1
as
update a set i = 2
rollback tran
go

create trigger x on a for insert
as
update a set j = 2
exec sp1
go

insert a select 1,1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-05 : 17:15:16

thanks nr sorry it was my own stupidity when i was testing, hehe normaly pick those things up when testing but dont know why, but i just tested the sp :)

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources