By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,403 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,403 IT Pros & Developers. It's quick & easy.

Raising Error from Trigger to Stored Proc

P: n/a
I feel like I'm missing something obvious here, but I'm stumped...

I have a stored procedure with code that looks like:

INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)

IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('An error occurred in the stored proc.', 16, 1);
RETURN(1);
END

--FORCING AN ERROR AT THE END FOR TESTING PURPOSES
RAISERROR('Proc Successful',16,1)

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."

Is there some way I'm not finding to have the calling procedure recognize
that a raiserror occurred in the trigger and behave appropriately for an
error situation?

Jen

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Jen S (sk*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.