ORA-04091: table ... is mutating, trigger/function may not see it
Posted on 2008-06-24
I'm trying to fire this trigger but I get ORA-04091: table ... is mutating, trigger/function may not see it
since I'm querying the same table the trigger is firing on.
I tried declaring pragma autonomous_transaction; but that wouldn't update the status on OF. it did however fix the mutation error.
The OL table can contain many records with a status of 1 or 2.
This trigger should fire whenever these statuses are changed and check if all are =2. If so then udpate the status of OF.
How else can I count(*) OL without querying it so as to avoid mutation error & not use a pragma declaration?
CREATE OR REPLACE TRIGGER OL_SPLIT
AFTER UPDATE OF TOORDERID,STATUS ON OL FOR EACH ROW
IF (:old.status = 1 and :new.status = 2) then
from OL b
where b.orderid = :new.orderid and b.status = 1;
IF v_count = 0 THEN
SET FORMSTATUS = 4
WHERE ORDERID = :new.orderid;