Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to avoid the ORA-04091: table name is mutating, trigger/function may not see it?

GadoOct 27 2017 — edited Nov 18 2017

Hello experts,

I'm using JDev 11.1.1.7.0

and Oracle XE 11.2.0

In my application i have a Mesure and Mesure_d tables that handle unit conversions.

Mesure(mesure_id PK, name)

Mesure_d(mesure_id FK, mesured_id PK, name , factor , base)

I run into an issue that a user has defined a mesure totally wrong ... and what makes it worse is that some transactions are made on the products using that mesure.

So i tried to build an AFTER UPDATE trigger on the Mesure_d table to handle the correction process.

The trigger is updating all the tables that contains those products (Purchasses , sales , RGNs and Inventory):

create or replace TRIGGER CHANGE_BASE_UNIT

AFTER UPDATE OF BASE ON MESURE_D

FOR EACH ROW

BEGIN

--IF THE UPDATED UNIT CHECKED

IF :NEW.BASE = 1 THEN

--1.UPDATE PRODUCTS USING THIS MESURE PRICES

CHANGE_PROD_UNIT_FACTORS(:NEW.MESURE_ID);

--2.UPDATE PROD IN ALL INVOICES(UNIT_FACTOR , BASE_PRICE)

UP_INVOICE_PROD_PRICE(:NEW.MESURE_ID);

--3.UPDATE PROD(I) IN WARE_ORDERS(UNIT_FACTOR , BASE_PRICE)

UP_WAREORDER_PROD_PRICE(:NEW.MESURE_ID);

--4.UPDATE PROD IN INVENTORY (BASE_UNIT , SOURCE_QNTY , PRODUCT_QNTY)

-- THIS HAS BEEN DONE IMPLISITLY INSIDE THE ABOVE PROCS

--5.UPDATE PROD(I) IN SALES_INVOICES

UP_SALES_INV_BASE_UNIT(:NEW.MESURE_ID);

--6.UPDATE WARE INVENTORY TRANSFER DEDUCTIONS & DISTRUCTION**

UP_DEDUCT_PROD_TRANSFER(:NEW.MESURE_ID); --<--------------

--7. UPDATE WARE INVENTORY SALES DEDUCTIONS

DEDUCT_SOLD_PROD(:NEW.MESURE_ID);

--ELSE IF THE UPDATED UNIT IS UNCHECKED

ELSE

NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

DECLARE

      ERR_CODE VARCHAR2(30):= SQLCODE;

      ERR_MSG VARCHAR2(500):= dbms_utility.format_error_backtrace;-- SQLERRM;

BEGIN

      LOG_ERROR('CHANGE_BASE_UNIT',ERR_CODE, ERR_MSG);

--      RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||ERR_CODE||' -ERROR- '||ERR_MSG);

     RAISE;

      END;

END;

But when i tried to update the Mesure by changing the Base unit ... it raised an error :ORA-04091: table name is mutating, trigger/function may not see it.

A statement in the trigger is trying to get the factor from the updated table (Mesure) to recalculate the quantities that should be added or deducted from the inventory.

So my question is: How can i get values from a "Mutating table"?

Thank you for your time

Gado

Message was edited by: Gado

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 16 2017
Added on Oct 27 2017
26 comments
2,241 views