Posted by Jen S on 06/20/05 18:28

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

[Back to original message]