Hello all,
we have a table Sales. In this table when data inserted, profit calculated with a stored procedure.
Trying to create a trigger to update the profit whenever record got inserted to the sales table.
As the table is same it is throwing error
exception : ORA-20001: An error was encountered - -4091 -ERROR- ORA-04091: table SALES.SALES is mutating, trigger/function may not see it ORA-06512: at "SALES.FORMAT_SYSTEM", line 153 ORA-06512: at "SALES.UPDATE_PROFIT_TRIGGER", line 6 ORA-04088: error during execution of trigger 'SALES.UPDATE_PROFIT_TRIGGER'
CREATE TABLE "SALES"."SALES"
( "PROD_ID" NUMBER(6,0) CONSTRAINT "SALES_PRODUCT_NN" NOT NULL ENABLE,
"CUST_ID" NUMBER CONSTRAINT "SALES_CUSTOMER_NN" NOT NULL ENABLE,
"QUANTITY_SOLD" NUMBER(20,4) CONSTRAINT "SALES_QUANTITY_NN" NOT NULL ENABLE,
"AMOUNT_SOLD" NUMBER(20,4) CONSTRAINT "SALES_AMOUNT_NN" NOT NULL ENABLE,
"TIME_ID" DATE,
"SALE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"BRANCH_ID" NUMBER,
"PROFIT" NUMBER,
"MARGIN" NUMBER,
"COMMISSION" NUMBER
)
CREATE OR REPLACE TRIGGER "SALES"."UPDATE_PROFIT_TRIGGER"
AFTER INSERT
ON sales
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
format_system.profit_calculation;
END;
/
ALTER TRIGGER "SALES"."UPDATE_PROFIT_TRIGGER" ENABLE;
and a procedure
procedure profit_calculation
IS
vmargin number;
vprofit number;
vmarginpct number;
cursor d1 is
SELECT b.sale_id,b.prod_id,b.branch_id,a.cost_price,amount_sold,amount_sold/NVL(quantity_sold,1)sale_price,NVL(b.commission,0)/NVL(b.quantity_sold,1) commissionper,
b.quantity_sold,b.margin,b.profit,b.commission
FROM find_cost a,sales b where a.prod_id=b.prod_id and a.branch_id=b.branch_id and (b.profit is NULL or b.profit=0);
BEGIN
for c1 in d1
Loop
vmargin:=c1.sale_price-(c1.cost_price+c1.commissionper);
vprofit:=vmargin*c1.quantity_sold;
vmarginpct:=(NVL(vprofit,0)/NVL((c1.cost_price*c1.quantity_sold),1))*100;
update sales set profit=vprofit,margin=vmarginpct where sale_id=c1.sale_id;
commit;
End Loop;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END