SQL & PL/SQL

Hi,

I have been trying to get a trigger to execute a stored procedure when an update is made to a row in a table.

The procedure works when ran manually and the trigger compiles but when the trigger is fired I get a "table is mutating, trigger/function may not see it" error.

Here's a bit more background:
I have a product table where the cost of a product is calculate based on costs in other tables (e.g. cost of raw materials is in the suppliesrawmat table).

I have written a procedure (called proc_costcalc) which takes in a productID and updates that product's cost in the product table.

I want a trigger to do this for every affected product when a rawmaterial cost is changed.


The code of my trigger is:

CREATE OR REPLACE trigger trig_rawcostupdate
AFTER INSERT OR UPDATE ON suppliesrawmat FOR EACH ROW

DECLARE
cursor c1 is
SELECT p.prodid
FROM process p,
(SELECT s.processid
FROM stage s,
(SELECT stageno
FROM stagerawmat
WHERE prodid = :new.prodid) subquery0
WHERE s.stageno = subquery0.stageno) subquery1
WHERE p.processid = subquery1.processid;

BEGIN
FOR tuple in c1
LOOP
proc_costcalc(tuple.prodid);
END LOOP;
END;
/


The query for the cursor generates a list of productIDs which I need to run through the proc_costcalc procedure.

Can anyone show me where I am going wrong? How can I fix this?

Thanks
Keith