oracle - SQL error - trigger/function may not see it

Question

I was working on my project, here I got this error while inserting some values in a row:

ERROR at line 1: ORA-04091: table SYSTEM.PRODUCTS is mutating, trigger/function may not see it ORA-06512: at "SYSTEM.PROD_TOTAL", line 2 ORA-04088: error during execution of trigger 'SYSTEM.PROD_TOTAL'

This is my insert statement:

insert into products 
values (1, 1001, 'Medical', 20, 4, 1, 1, 1);

Products table :

create table Products
(
    ProdId number primary key,
    ProdNum number not null unique,
    ProdType varchar2(15),
    ProdPrice int,
    ProdQuantity int,
    ProdCustId int references Customers,
    ProdOrdId int references Orders,
    ProdStoreId int references Stores
);

Trigger code:

create trigger PROD_TOTAL
after insert ON Products
for each row
begin
    update Payments
    set ProdTotal = (select Products.ProdPrice * Products.ProdQuantity from Products);
end;
/

And finally my Payment table:

create table Payments
(
    PayId int primary key,
    PayDate date,
    ProdTotal int,
    FinalTotal int,
    PayOrdId int references orders,
    PayProdId int references Products,
    PayCustId int references Customers
);

I don't know why I am getting this error, please help me in solving this issue...

question from:https://stackoverflow.com/questions/65857565/sql-error-trigger-function-may-not-see-it

Answer 1

A statement level trigger (i.e. without FOR EACH ROW clause) will update always all records in Payments table, I don't think that's needed. For an update of only related products, use this trigger:

create trigger PROD_TOTAL
after insert ON Products
for each row
begin
    update Payments
    set ProdTotal = :new.ProdPrice * :new.ProdQuantity
    WHERE PayProdId = :new.ProdId ;
end;