Problem with rollback trigger

[level8]

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?

[level8]

Re: Problem with rollback trigger

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns93D032 354DC9Yazorman@ 127.0.0.1>...[color=blue]
> level8 (level8@freemai l.hu) writes:[color=green]
> > 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?[/color]
>
> 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.[/color]

Thanks, Erland!

My name is László - 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?

László

[Erland Sommarskog]

Re: Problem with rollback trigger

level8 (level8@freemai l.hu) writes:[color=blue]
> My name is László - 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?[/color]

Well, you could do:

IF <test for bad data>
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Foun d 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/techinfo/productdoc/2000/books.asp

[level8]

Re: Problem with rollback trigger

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns93D05E 7181F35Yazorman @127.0.0.1>...[color=blue]
> level8 (level8@freemai l.hu) writes:[color=green]
> > My name is László - 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?[/color]
>
> Well, you could do:
>
> IF <test for bad data>
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Foun d 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.[/color]

Thanx !