I have two tables and a trigger and am inserting values in ProjectsTable
like this:
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);
But when I tried to insert values into the ExpenseTable
table, it gave me the following errors:
ERROR at line 1: ORA-04091: table ExpenseTable is mutating, trigger/function may not see it
ORA-06512: at ExpenseSum , line 2
ORA-04088: error during execution of trigger ExpenseSum
FOR EACH ROW
andwhere P.ProjectID = :New.ProjectID
clause. For given logic it should work fine. – Wernfried Domscheit Sep 27 '17 at 10:51