Advertisement

06.24.2008 at 12:28PM PDT, ID: 23512274
[x]
Attachment Details

ORA-04091: table ... is mutating, trigger/function may not see it

Asked by badtz7229 in Oracle 10.x, Oracle 8.x

Tags: oracle

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

DECLARE
v_count            number(20);

BEGIN  
  IF (:old.status = 1 and :new.status = 2)  then
        
            select count(*)
            into v_count
            from OL b
            where b.orderid = :new.orderid and b.status = 1;
            
            IF v_count = 0 THEN                   
                  UPDATE OF
                  SET FORMSTATUS = 4
                  WHERE ORDERID  = :new.orderid;
                  commit;                        
            END IF;            
 END IF;            
END;
/Start Free Trial
 
Loading Advertisement...
 
[+][-]06.25.2008 at 06:47AM PDT, ID: 21865543

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.25.2008 at 06:00PM PDT, ID: 21871024

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.26.2008 at 12:38AM PDT, ID: 21872451

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.26.2008 at 06:23AM PDT, ID: 21874487

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.02.2008 at 02:28PM PDT, ID: 21920899

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Oracle 10.x, Oracle 8.x
Tags: oracle
Sign Up Now!
Solution Provided By: sdstuber
Participating Experts: 2
Solution Grade: B
 
 
[+][-]07.03.2008 at 07:02PM PDT, ID: 21930585

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 07:29PM PDT, ID: 21930681

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628