473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Raising Error from Trigger to Stored Proc

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
1 4307
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Alvin | last post by:
Hi All I need opinions on how to approach my task. I currently have 3 tables: the master table, the archive and a temp table. MASTER: has 3 fields ProductID and ProductNo and Released ARCHIVE:...
1
by: Bill S. | last post by:
Hi, I a stored procedure that inserts a record into a table as below. The insert works OK, but if the insert violates a unique indewx constraint on one of the columns, the proc terminates...
0
by: Rick | last post by:
I wrote a medium length stored proc that uses both temp tables and one cursor. I compiled and it ran fine. However, we reboot our machine every weekend, and on Mondays the stored proc gets the...
4
by: Nyul | last post by:
Gurus, I have a verb big problem which I'm unable to explain. We have a DB2 V6.1.0 on AIX 4.3 I want to make a C stored procedure which at the end will be called by a PHP script. The...
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
1
by: Eric Land | last post by:
Help! I'm trying to call a parameterized stored proc in ASP.NET in VB. I am creating a command object and creating a parametr list, and assigning a value from a session variable (this is working)...
1
by: Marc Rondeau | last post by:
Hi every expert. I very need Help! I don't know what is wrong in my code. When i try to execute this code on my access , it give me give this weard error The stored proc have no parameters, i...
1
by: central_scrutinizer | last post by:
I have an ASP that has been working fine for several months, but it suddenly broke. I wonder if windows update has installed some security patch that is causing it. The problem is that I am...
0
by: dragonGoget | last post by:
Hi, I try to upload store procedure from ant build.xml... My problem is that i have wrote a simple store proc as a test example (which is just a select count on a table) from DB2 Development...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.