SQL错误-触发器/函数可能看不到它

Question

我在我的项目中工作，在这里我得到了这个错误，而插入一些值在一行:

ERROR at line 1: ORA-04091: table SYSTEM。产品正在发生变异，触发器/函数可能看不到它ORA-06512: at "SYSTEM.PROD_TOTAL"第2行ORA-04088:执行触发器时出现错误的系统。PROD_TOTAL '

这是我的insert语句:

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

Productstable:

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
);

触发代码:

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

最后我的Payment表:

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
);

我不知道为什么我得到这个错误，请帮助我解决这个问题…

Answer 1

语句级触发器(即没有FOR EACH ROW子句)将始终更新所有付款表中的记录，我认为不需要。对于仅更新相关产品，请使用此触发器:

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;