Re: Trigger question

Hi, Jim!

You can't select from po_open in your trigger (because it's 'for each row').
That's a reasonable restriction.
Try 'statement' trigger or some another workaround.

Jim Pistrang wrote:
>
> Oracle experts, please help!
>
> I've got an Oracle Trigger that kicks in on an update to a 'child' table.
> It passes the the parent key to an Oracle Stored Procedure, which sets a
> flag in the parent record based on a calculation involving sum() amounts
> in the children.
>
> Here's the trigger:
>
> CREATE OR REPLACE TRIGGER TRIG_PO_OPEN
> AFTER UPDATE ON PO_LINE
> FOR EACH ROW
> begin
> SP_PO_OPEN(:new.po_id);
> end;
>
> and here's the Stored Procedure:
>
> CREATE OR REPLACE PROCEDURE SP_PO_OPEN (poid IN NUMBER)
> AS
> sum1 NUMBER;
> sum2 NUMBER;
> begin
> select sum(qty_made) into sum1 from po_line where po_id = poid;
> select sum(qty_rec) into sum2 from po_line where po_id = poid;
> IF sum1-sum2=0 and sum1>0 THEN
> update po set po_open = 1
> where po_id = poid;
> END IF;
> IF sum1-sum2<>0 THEN
> update po set po_open = 0
> where po_id = poid;
> END IF;
> end;
>
> If I simply call the Procedure passing a key, it works fine. But when I
> enable the trigger and update the PO_LINE table I get an error saying
> 'table is mutating, trigger/function may not see it...'. It seems to be
> saying that I can't do a sum() in a Stored Procedure on a record that's
> being updated. I thought I could do this sort of thing...what am I doing
> wrong?
>
> Jim

-- 
Regards. Alexey Neckrasow. Leaves Inc. Russia.
e-mail: nec_at_leaves.spb.su