ORA-04091 tableT is mutating, trigger/function may not see it

Question

create or replace trigger discount
after insert or update
on product
for each row

declare
 newcost number;
 quant number;
 id number;

BEGIN
id:= :NEW.pid;
quant:= (30/100)*:NEW.req_quant;
newcost:= :NEW.pcost - (10/100)*:NEW.pcost;

if :NEW.act_quant>quant then
  update product set pcost=newcost where pid=id;
end if;
dbms_output.put_line('success');
end;

while writing query insert into product values(107,20,20,1000); i get an this error ORA-04091 tableT is mutating, trigger/function may not see it

my table is

CREATE TABLE  "PRODUCT" 
   (    "PID" NUMBER(5,0), 
    "ACT_QUANT" NUMBER(5,0), 
    "REQ_QUANT" NUMBER(5,0), 
    "PCOST" NUMBER(10,0), 
     PRIMARY KEY ("PID") ENABLE
   )

After inserting or updating trigger must check that whether the actualquantity of product is greater than 30% of requaired quantity if it is true we need to give discount of 10% on that product

Add the "while writing query insert..." at the beginning, so people know what the question is about early on. Share some data examples so people can try to replicate your error. — Mariane Reis, Dec 28 '22 at 13:57

score 1 · Answer 1 · answered Dec 27 '22 at 14:43

Don't literally update table which is just being modified (which caused trigger to fire) because trigger can't see it; that's the good, old mutating table error.

  if :NEW.act_quant > quant then
     :new.pcost := newcost;                               --> use this
     -- update product set pcost=newcost where pid=id;    --> not this
  end if;

Though, the whole trigger can be shortened to

create or replace trigger discount
  after insert or update on product
  for each row
begin
  :new.pcost := case when :new.act_quant > (30/100) * :new.req_quant then 0.9 * :new.pcost
                     else :new.pcost
                end;
end;

ORA-04091 tableT is mutating, trigger/function may not see it

1 Answers1