Posted by Erland Sommarskog on 06/21/05 00:51

Jen S (skripac@mainstreams.com) writes:
> On MyTableA, there is a trigger that loops through the inserted data and
> stops the insert in certain circumstances, returning an error:
>
> IF (some criteria)
> BEGIN
> ROLLBACK
> RAISERROR('An error occurred in the trigger.',16,1)
> RETURN
> END
>
> When I call the stored procedure from VB (connecting via RDO) with
> error-causing data, the trigger successfully stops the insert, and adds
> the trigger-error-msg to the errors collection, but it does NOT seem to
> create an error situation back in the stored procedure. The procedure
> finishes up with the "Proc Successful" message, so that when I iterate
> through the errors collection back in VB, I have "Proc Successful"
> followed by "An error occurred in the trigger."

I would suspect that that "Proc successful" comes from another invocation.

When an error happens in T-SQL, there are a couple things that can
happen. Sometimes execution continues, and you can check for that
with @@error. But sometimes the batch is aborted on the spot, and
then you cannot catch anyhing in T-SQL. One such condition is a rollback
in a trigger.

Error-handling in SQL 2000 is a very complex matter, see
http://www.sommarskog.se/error-handling-II.html for a longer discussion
on the subject.

SQL 2005 offers vastly improvement in this area.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

[Back to original message]