W2k SP2 + SQL Sever 2000...

Some business rules are being implemented in triggers in order to ensure
that they are met regardless of how data enters the system.

In some cases, the insert, update, or delete operation that fired the
trigger must be undone.

In many cases, the insert, update, or delete operation is called from a
stored procedure. If the operation causes a violation of a business rule,
the trigger should raise an error and undo the operation. The stored

As documented in BOL, using ROLLBACK TRANSACTION inside the trigger will
undo the operation (as desired) but has the unwanted side effect of
terminating the batch. The stored procedure does not finish and is unable to
return the appropriate code to the client. The client appears to receive an
unknown return value from the stored procedure (107739176 or 107788544 for
example, instead of -1 as desired), along with the raised error.

Removing the ROLLBACK TRANSACTION and leaving just the RAISERROR statement
(using severity level 2) raises the error and lets the stored procedure
continue, but there are two problems: the operation has not been "undone"

occured.

I think I have a way to undo the transaction without using ROLLBACK TRAN -
in the trigger I could delete all records matching those in the inserted
table and insert all records from the deleted table. I would have to check
TRIGGER_NESTLEVEL( object_ID('dbo.ThisTrigger') ) and if it is greater than
0 I would skip the whole thing to avoid an endless recursive loop.
(Actually, if the original data did not violate business rules, it should
never fall into more than one level of recursion.)

My questions:

1) Is there a better way to "undo" the operation that triggered a TRIGGER
from within the trigger other than ROLLBACK TRAN and the way I just
mentioned?

2) Is there a way to have a stored procedure continue execution after a
TRIGGER does a ROLLBACK TRAN? (I don't think so)

TRIGGER to raise an error, so that the stored procedure that performed the
operation is aware that it failed?

Thanks in advance..

I just figured out the answer to one of these. I can use WITH SETERROR when
I raise the error in the trigger so that the calling procedure will know
about the error.

I guess that means if I delete and insert manually in order to undo the
operation, that should work. The main problem with that being that Timestamp
fields will still be changed afterwords (I believe), which are used for
concurrency checks. That means that if the operation is undone, the
timestamp will have changed and the user will have to refresh their data
before they can re-attempt an update. Not the best solution...

I hope this general issue gets some new options with the next version of SQL
Server...

If I use ROLLBACK from the trigger, then I do not have this issue. But how
can I predict or affect the RETURNed value from the stored procedure in this
case? I can't find anything in BOL on this topic. (Been wading through BOL
for a few hours now.)

Yup. That's the way it works, sad but true. Nevertheless, this is the
way to go. Trying to circumvent this as you discussed, will lead you
into murky waters, and the odds are considerable that you cause more
harm than good.

In case you are not aware of it: there are many types of errors that

errors for instance.

So you need to involve the client. Notice that that client will notice
if you raise an error - then you need to write the client so that it
actually handles the messages.

There is one execption: if you call a stored procedure on another server,
and that procedure runs into a batch-terminating error, the procedure
on the calling server will still be alive.

--
Erland Sommarskog, Abaris AB

SQL Server MVP