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...

This is clearly oracle - removed the unnecessary sql-server tag — marc_s
you cannot select from table products in a row trigger on table products. remove the 'for each row', and it should work (at least from what we see as your requirements) — gsalem
As an aside, you should NOT be creating your application objects in the SYSTEM schema (able SYSTEM.PRODUCTS, SYSTEM.PROD_TOTAL). They should be in an application-specific schema - a schema created specifically for the application's objects. — EdStevens
@ EdStevens I m sorry but I didn't get u. Actually, I am a beginner, so... — Sushmita shukla
"I didn't get u" Your table SYSTEM.PRODUCTS, belongs to user/schema SYSTEM. This is a pre-defined schema within oracle and has a very special use. When you create objects for your own applications, you should create your own application-specific user to own them, then create them accordingly. It appears you connect as SYSTEM, then started creating tables, etc within that schema. You should 'CREATE USER MYAPP ....', then connect as MYAPP, then CREATE TABLE MYAPP.MYTABLE', etc. — EdStevens

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2021-01-23T11:55:49

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;

SQL error - trigger/function may not see it

1 Answers