Re: Trigger calling Store Procedure
Ray Anderson wrote:
>
> I created a trigger which calls a store procedure and the store
> procedure was to check exception handling. The problem is that the
> trigger does not really have the capability to receive the
> return status from the store procedure. The following was
> an example:
>
> Create Procedure Insert_Proc_Iialg(p_dc_id LIKE iialg.dc_id
> p_whse_id LIKE iialg.whse_id)
> RETURN INTEGER;>
> DEFINE p_prod_id INTEGER;
> DEFINE p_adj_qty DECIMAL(16,2);
> DEFINE p_iarc_id CHAR(3);
> DEFINE error_code INTEGER;
>
> LET error_code = 0;
>
> On exception set error_code ## The syntax of these two lines
> return error_code; ## is not correct, but the idea
> ## is there
>
> SELECT a.whse_id, a.prod_id, a.adj_qty, a.iarc_id
> INTO p_whse_id, p_prod_id, p_adj_qty, p_iarc_id
> FROM iialg a
> WHERE a.dc_id = p_dc_id
> AND a.whse_id = p_whse_id>
> INSERT INTO prod_bal values(p_dc_id, p_whse_id, p_prod_id,
> p_adj_qty, p_iarc_id);>
> RETURN error_code;
>
> END Procedure;
>
> ---
> --- Now the Trigger
> ---
> Create Trigger Insert_Iialg
> Insert on iialg
> Referencing new as new_row
> For each row
> ( Execute Procedure Insert_Proc_Iialg(new_row.dc_id,
> new_row.whse_id));>
> The problem is that any insert into the iialg table fails
> with an error 684, since the procedure returns a value ant the trigger
> is not designed to receive one.
> Is there any way around this problem short of Not checking/returning
> a value fromn the store procedure?
Buried somewhere in either the training manual for SPL or in the Guide
is a statement to the effect that the *only* permissible use of a return
value from a procedure to a trigger is if the trigger will use that
(those) value(s) to effect an additional update on the row you just
inserted/updated. .... Ah! I found it. P- 1-214 of the Guide to SQL:
Syntax, version 7.2.
In short:
1. You can call a stored procedure from a triggered action, but we all
knew that already.
2. If the procedure returns 1 or more values the trigger *must* use
those returned values for an additional update to some column of the
triggering row.
3. Only an UPDATE trigger may do this.
4. The column getting the extr update (from the returned value) may not
be any of the columns that got updated to tickle this trigger.
As an example of item(2) see the usage on page 1-214 in the CREATE
TRIGGER documentation under the heading of "Rules for Stored
Procedures."
--
-- Jake (In pursuit of undomesticated aquatic avians)
+-----------------------------------------------------------+
| Impeccable Logic: A thought process which successfully |
| resists chicken bites |
+-----------------------------------------------------------+