5

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

  • You can simply skip the FOR EACH ROW and where P.ProjectID = :New.ProjectID clause. For given logic it should work fine. – Wernfried Domscheit Sep 27 '17 at 10:51
0

The easy way:

You do not need to recalculate the SUM of expenses, since you already have the SUM of expenses except the new amount, so all you need to do is adding the new amount:

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;
/
| improve this answer | |
  • It will produce wrong result for :New.ExpenseAmount = NULL – Wernfried Domscheit Sep 27 '17 at 10:49
  • @Wernfried Domscheit Indeed. – Balazs Papp Sep 27 '17 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.