Trigger calling Store Procedure
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?