Returning exceptions from a SP called from a Trigger...

[Paul Reddin]

Hi,

I've implemented calling a SP form a trigger using the
CALL_PROCEDURE()
UDF from
http://www-106.ibm.com/developerwork...402greenstein/

A couple of questions remain for future considerations?

1. This mechanism desn't allow for exceptions/return codes from the
called Stored Procedure to be passed back to the trigger code?
- I think I am going to have to write something to a table in the
SP for the Trigger to then read to determine if any exceptions occured
- seems a bit clumsy?

2. When 'direct' calling of SPs from triggers support is added, will
the Return Status and OUT parameters work directly with variables in
the trigger?

Many thanks.

Paul.

[Knut Stolze]

Paul Reddin wrote:

Hi,

I've implemented calling a SP form a trigger using the
CALL_PROCEDURE()
UDF from
http://www-106.ibm.com/developerwork...402greenstein/
A couple of questions remain for future considerations?

1. This mechanism desn't allow for exceptions/return codes from the
called Stored Procedure to be passed back to the trigger code?
- I think I am going to have to write something to a table in the
SP for the Trigger to then read to determine if any exceptions occured
- seems a bit clumsy?
That's correct. Either you do this by way of a table (not a temp table,
mind you!) or you use the operating system's facilities to propagate
errors, return codes and/or messages through the "system" call.

Alternatively, you can wait for Stinger which provides a native way to call
procedures from triggers. (The Beta is already available, I think.)
2. When 'direct' calling of SPs from triggers support is added, will
the Return Status and OUT parameters work directly with variables in
the trigger?

I just played around with this a bit, and the error is propagated through
the trigger to the application as you would expect.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Paul Reddin]

Thanks Knut,

We'll wait for Stinger before we do anything too fancy.

One other problem we did encounter, which is a show-stopper
is that of course the Call to the SP is outside the transaction
scope of the calling trigger, so we can't read the New Row(in the SP) which
fired the trigger because it isn't committed yet.

I guess I could change the UDF to change the isolation to UR for the call to
the SP, but then there are other app. problems with this ......

Ah well, we'll live with what we have for now, but look to go to Stinger
asap.

Thanks again.

Paul.