Dear SQLers:

I am working with error handling. I have Erlang's code for error
reporting. He suggests
if @@tranlevel>0
rollback
I have removed this from my code, and yet the rollbacks still occur. I
also have transaction levels being automatically created. I would
like to understand what is going on. Documentation that I have found
does not deal with this.

At the outer level of my code, I have an insert statement wrapped
in a try block. Note that I do not define a transaction here. At the
first statement of the try block, @@trancount=0. The insert is
executed.

The insert trigger fires. At the first statement of the insert
trigger's try block, @@trancount=1. Why 1?

A begin transaction is executed. Now, @@trancount=2.

Irrelevant stuff happens.

A call to NiceString() with bad data is done. NiceString()
returns an error code which the trigger code checks for. A
raiserror(50001,16,1) is done. (I have defined error 50001.)

Now, in the catch block of the trigger, @@trancount=2.

A lightly-modified (formatting mainly) version of Erlang's error
handler is executed. @@trancount is unaffected. Then, a raiserror()
is done to propagate the error to the caller.

Back at the caller, @@trancount=0, but no rollback statement was
executed! The error message is then printed.

Supposedly, set xact_error on causes auto-rollbacks. Fine, but
the above happens with set xact_error off (unless I am actually not
setting it in the right place.)

According to
http://msdn.microsoft.com/en-us/library/ms188792.aspx, "Depending upon
the severity of the error, the entire transaction may be rolled back
even when SET XACT_ABORT is OFF." What circumstances? If the trigger
raiserror()'s severity is not high enough, the catch will not fire
anyway. Is this what is meant here?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
When you are in a trigger, you are always in a transaction. If you did not
start one explicitly, there is still an implicit one defined by the
statement that fired the trigger. This is an essential point: the trigger is
part of the INSERT statemnet, and if the trigger fails, the INSERT statement
should also be rolled back.


When you are in a trigger XACT_ABORT is ON by default. Furthermore, if you
roll back the transaction in the trigger, the batch is aborted.

Error handling in SQL Server is a patchwork of inconsistencies. A lot
inherited from Sybase, but Microsoft has been careful to add their own
madness. Up to SQL 2000, an error in a trigger always aborted the batch, but
starting with SQL 2005 you can prevent that with an explicit SET XACT_ABORT
OFF in the trigger.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Wed, 18 May 2011 07:27:20 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Let me see if I follow.

Had I not been checking trigger code, there would not have been
an implicit transaction created, and it would have been correct (and a
good idea) to have a BEGIN TRANSACTION. Since the code in question is
a trigger, there is no need for a BEGIN TRANSACTION, but it does not
hurt.

Correct?

In the trigger, because a raiserror() caused the catch block to
be executed, a rollback will automatically occur.

Correct?

Were it not trigger code, then the setting of SET XACT_ABORT
would matter.

Correct?

Does the setting for SET XACT_ABORT get restored when a trigger
finishes execution? How does this work when there are other
procedures called? Same question, but is it any different if the
lower-level procedure raises an error (as does your error handler)?

Eä Cthulhu!

Nothing that I have read has mentioned when to set SET
XACT_ABORT. Is any time before an error occurs acceptable? From your
last paragraph, the answer to this appears to be yes.

Correct?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Actually, I recommend against using BEGIN and COMMIT TRANSACTION in
triggers; only ROLLBACK makes sense.

This is getting confusing even for me! You see, normally RAISERROR does not
terminate the batch, even if XACT_ABORT is on. But yes, in a trigger.
This is probably for compatibility reasons.

Actually, XACT_ABORT matters also in triggers. It is just that the
default is different.

Yes. The effect of any SET command is reverted when the scope in which the
SET command was executed in exists. (With one single exception SET
CONTEXT_INFO.)

XACT_ABORT ON extends into these procedures, and errors in the procedures
will bubble up to the trigger. There is however a completely horrendeous
exception. Normally if you something like:

CREATE PROCEDURE bad_tran AS BEGIN TRANSACTION

You will get an error about trancount mismatch. But if the procedure is
called from a trigger, the error is suppressed. I reported this as a bug -
it was closed by design! (And I think I've seen it documented in Books
Online.)

Many people put SET NOCOUNT ON in the top of their procedures. That could be
a good place for SET XACT_ABORT ON as well.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Wed, 18 May 2011 23:39:09 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

[snip]

It does not say why it normally does. I think the reason is that
a raiserror() with a severity >=11 triggers the catch block, but it is
not documented (that I can see).

[snip]

Thank you for some of the help. I would write "all", but I feel
I will probably need more later. I would rather have better
documentation instead of all of this educated guessing.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
RAISERROR should invokes the CATCH block, so much is right. This is
applies to both triggers and regular code.

As for what is documented or not is this madhouse I don't know. I only
know that the updated version of my article on error handling is long
overdue...

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Thu, 19 May 2011 23:38:29 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Nope. If the severity is <=10, then raiserror() will not result
in the catch block triggering. This is documented. I do not know why
it is this way, but it is.

Try the following code. Besides labelling, the only difference
between the two procedures is the severity in the raiserror().

***** Start of Code *****
use tempdb
go

if object_id(N'HighSeverity',N'P') is not null
drop procedure HighSeverity
if object_id(N'LowSeverity',N'P') is not null
drop procedure LowSeverity
go

create procedure HighSeverity
as
begin try
print N'In HighSeverity'
raiserror(N'This is a severe error.',11,1)
print N'Catch did not trigger.'
end try
begin catch
print N'Catch triggered.'
end catch

go

create procedure LowSeverity
as
begin try
print N'In LowSeverity'
raiserror(N'This is a mild error.',10,1)
print N'Catch did not trigger.'
end try
begin catch
print N'Catch triggered.'
end catch

go

execute HighSeverity
execute LowSeverity
***** End of Code *****

The output will be:

In HighSeverity
Catch triggered.
In LowSeverity
This is a mild error.
Catch did not trigger.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Sorry, the severity is implied.

The meaing of the severity levels are documented in Books Online somewhere,
but I think you find a quicker summary on
http://www.sommarskog.se/error-handl...severitylevels.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx