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]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

level8 (le****@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 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]

level8 (le****@freemail.hu) writes:

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?

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[level8]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

level8 (le****@freemail.hu) writes:

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?

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 !