Problem with rollback trigger

Hi, Everybody,

I'm a Hungarian SQL user and I need a little help for SQL Server 7 !

I protect my table against bad data with a trigger. I use ROLLBACK and
RAISERROR statement in this trigger. Users can get my error message
after manual input, but the stored procedure always cancel because of
ROLLBACK. So the input program dont't have chance to analyze the error
message. I could work without ROLLBACK, but it wouldn't be the best
solution.

What's the correct solution with ROLLBACK statement?Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93D032354DC9Yazorman@.127.0.0.1>...
> level8 (level8@.freemail.hu) writes:
> > I protect my table against bad data with a trigger. I use ROLLBACK and
> > RAISERROR statement in this trigger. Users can get my error message
> > after manual input, but the stored procedure always cancel because of
> > ROLLBACK. So the input program dont't have chance to analyze the error
> > message. I could work without ROLLBACK, but it wouldn't be the best
> > solution.
> > What's the correct solution with ROLLBACK statement?
> I don't really understand what you mean with "the input program don't
> have a change to analyse the error message". If you with the input
> program mean the stored procedure, yes, this is correct. Once the
> trigger exits with @.@.trancount = 0, the batch is bye-bye. On the
> other hand the procedure cannot get the message anyway, because only
> the client gets the text of the message.
> And if the input program is the client program, then the rollback
> does not prevent the client from getting the message.
> Note that the ROLLBACK itself does not terminate the batch. Execution
> continues to the end of the trigger. It is the condition @.@.trancount = 0
> on trigger exit that cancels the batch.

Thanks, Erland!

My name is Lszl - from Hungary. Yes, input program is a stored
procedure. I write the trigger and another person writes the input sp.
Yes, trigger continues to the end and it can create an error message
in a log table, but a I would like to use ROLLBACK statement. Can I
set @.@.trancount? Or is it wrong idea?

Lszl|||level8 (level8@.freemail.hu) writes:
> My name is Lszl - from Hungary. Yes, input program is a stored
> procedure. I write the trigger and another person writes the input sp.
> Yes, trigger continues to the end and it can create an error message
> in a log table, but a I would like to use ROLLBACK statement. Can I
> set @.@.trancount? Or is it wrong idea?

Well, you could do:

IF <test for bad data>
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Found bad data', 16, 1)
INSERT errorlog (...)
BEGIN TRANSACTION
END

That is, you can start a new transaction at the end of the trigger. I would
strongly discourage you from doing this, because the consequences are
unclear, least to say. The caller will be left with an open transaction
that he needs to take care of.

So, leave it to the client to handle the error.

I have two articles on by web site of error handling in SQL Server, which
will not really help to solve this problem, but gives some information
about what you can and cannot do. You may know some of the stuff already.
The link to the first article is
http://www.algonet.se/~sommar/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93D05E7181F35Yazorman@.127.0.0.1>...
> level8 (level8@.freemail.hu) writes:
> > My name is Lszl - from Hungary. Yes, input program is a stored
> > procedure. I write the trigger and another person writes the input sp.
> > Yes, trigger continues to the end and it can create an error message
> > in a log table, but a I would like to use ROLLBACK statement. Can I
> > set @.@.trancount? Or is it wrong idea?
> Well, you could do:
> IF <test for bad data>
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Found bad data', 16, 1)
> INSERT errorlog (...)
> BEGIN TRANSACTION
> END
> That is, you can start a new transaction at the end of the trigger. I would
> strongly discourage you from doing this, because the consequences are
> unclear, least to say. The caller will be left with an open transaction
> that he needs to take care of.
> So, leave it to the client to handle the error.
> I have two articles on by web site of error handling in SQL Server, which
> will not really help to solve this problem, but gives some information
> about what you can and cannot do. You may know some of the stuff already.
> The link to the first article is
> http://www.algonet.se/~sommar/error-handling-I.html.

Thanx !

Problem with rollback statement

Hi,

I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.

Does anyone see my error?

Thanks,

poc1010

Create proc AddProducts

@.dcint=null,
@.pcint=null,
@.imagepathvarchar(50)=null,
@.typevarchar(2)=null,
@.descriptionvarchar(1000)=null,
@.gendervarchar(8)=null,
@.productidint=null,
@.pccodevarchar(2)=null,
@.weightvarchar(80)=null,
@.pricemoney=null,
@.activevarchar(1)=null

as

declare @.errorsave int
set @.errorsave=0
declare @.dg int

Begin transaction

insert productdescription(
designercategory,
productcategory,
imagepath,
type,
[description],
gender)
values(@.dc,
@.pc,
@.imagepath,
@.type,
@.description,
@.gender)

if @.@.error <> 0
set @.errorsave=@.@.error

set @.dg = @.@.identity

begin
insert Products(
productid,
designergroup,
designercategory,
productcategory,
pccode,
weight,
price,
active)
values(@.productid,
@.dg,
@.dc,
@.pc,
@.pccode,
@.weight,
@.price,
@.active)

if @.@.error <> 0
set @.errorsave=@.@.error
end

if @.errorsave <> 0
begin
print 'Insert into Products tables failed'
rollback transaction
return -5--Insert into Products tables failed
end

commit transaction
print 'Success'
return 0 --SuccessYou have begins and ends in useless spots. Whats the acual error message?|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||None of you guys used ELSE. Your BEGIN/END's are a little whacked out. Honestly, I'm against returning in mid procedure if it's not necessary. You can easily follow through the entire procedure using an ELSE, then returning a specified value.|||Pierre,

Thanks for your example. I saw what I was doing wrong. Works great.

Thank you for your help.

poc1010|||That's just personal taste Lee. No real argument either way. Not in this case.|||You're right. That's why I said that I prefer the other way. Didn't say you were wrong, because it works fine.