Trigger question

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 Received on Tue Apr 15 1997 - 00:00:00 CDT