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

insertoracleoracle-11gtrigger

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

Best Answer

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...

Related Question