ORA-04091: table ExpenseTable is mutating, trigger/function may not see it

Question

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

Answer 1

As the trigger says, you cannot read data from the same table as the trigger is working on as it would throw consistency out of the window.

Have you tried to put ExpenseTotal column in a view like below?

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

If CostRemaining is Cost - ExpenseTotal you can take it out of your ProjectsTable trigger, too :)

If this is sufficient with regard to the performance, I'd prefer it over the trigger based approach any time...

Answer 2

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