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