CODEWITHSUNDEEP
sqlplsqltriggersmutating-tableora-04091
Abhishek Nikam
Abhishek Nikam

Reputation: 11

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

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

Upvotes: 1

Views: 73

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

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;

Upvotes: 1

Related Questions