ORA-04091: 表 ExpenseTable 正在變異，觸發器/函式可能看不到它

我有兩個表和一個觸發器，並且正在ProjectsTable像這樣插入值：

CREATE TABLE ProjectsTable
(
 ProjectID     NUMBER(6) NOT NULL,
 ProjectName   VARCHAR2(200) NOT NULL,
 Cost      NUMBER(10,2),
 ExpenseTotal  NUMBER(10,2),
 CostRemaining     NUMBER(10,2),
 PRIMARY KEY (ProjectID)
);

CREATE TABLE ExpenseTable
(
 ID            NUMBER(6) NOT NULL,
 ProjectID     NUMBER(6) NOT NULL,
 ExpenseAmount NUMBER(10,2),
 ExpenseDate   NUMBER(4),
 CONSTRAINT fk
 FOREIGN KEY (ProjectID)
 REFERENCES ProjectsTable(ProjectID)  
); 

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
   UPDATE ProjectsTable P
   SET ExpenseTotal = 
   (SELECT SUM(ExpenseAmount) from ExpenseTable
   where ExpenseTable.ProjectID= P.ProjectID)
   where P.ProjectID = :New.ProjectID;
END;
/

INSERT INTO ProjectsTable VALUES (101,'AAA',5000,0,5000);
INSERT INTO ProjectsTable VALUES (102,'BBB',3000,0,3000);
INSERT INTO ProjectsTable VALUES (103,'CCC',2000,0,2000);

但是當我嘗試將值插入ExpenseTable表中時，它給了我以下錯誤：

第 1 行的錯誤：ORA-04091：表 ExpenseTable 正在發生變化，觸發器/函式可能看不到它

ORA-06512：在 ExpenseSum，第 2 行

ORA-04088：執行觸發器 ExpenseSum 期間出錯

正如觸發器所說，您不能從觸發器正在處理的同一個表中讀取數據，因為它會將一致性拋到窗外。

您是否嘗試將ExpenseTotal列放在如下視圖中？

CREATE VIEW ProjectsTable_V AS
SELECT
 P.ProjectID,
 P.ProjectName,
 P.Cost,
 (
    SELECT SUM(ExpenseAmount)
      FROM ExpenseTable
     WHERE ExpenseTable.ProjectID = P.ProjectID
 ) ExpenseTotal,
 P.CostRemaining
FROM
 ProjectsTable P

如果CostRemaining是Cost - ExpenseTotal，您也可以將其從ProjectsTable觸發器中取出:)

如果就性能而言這已經足夠了，那麼我隨時都喜歡它而不是基於觸發器的方法……

簡單的方法：

您不需要重新計算費用總和，因為您已經有了除新金額之外的費用總和，因此您只需添加新金額即可：

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
   UPDATE ProjectsTable P
   SET ExpenseTotal =  ExpenseTotal + :New.ExpenseAmount
   where P.ProjectID = :New.ProjectID;
END;
/

引用自：https://dba.stackexchange.com/questions/186397