SQL错误-触发器/函数可能看不到它 [英] SQL error - trigger/function may not see it

问题描述

我正在处理我的项目，在这里，当我在一行中插入一些值时出现此错误:

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

第1行的错误:ORA-04091:表SYSTEM.PRODUCTS正在变异，触发器/功能可能看不到它ORA-06512:位于"SYSTEM.PROD_TOTAL"处，第2行ORA-04088:执行触发器期间出错'SYSTEM.PROD_TOTAL'

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'

这是我的插入语句:

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

产品表:

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

我不知道为什么会收到此错误，请帮助我解决此问题...

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

推荐答案

语句级别的触发器(即没有 FOR EACH ROW 子句)将始终更新Payments中的所有记录表，我认为这不是必需的.要仅更新相关产品，请使用以下触发器:

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错误-触发器/函数可能看不到它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！