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

1
  • You can simply skip the FOR EACH ROW and where P.ProjectID = :New.ProjectID clause. For given logic it should work fine. Sep 27 '17 at 10:51
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...

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;
/
2
  • It will produce wrong result for :New.ExpenseAmount = NULL Sep 27 '17 at 10:49
  • @Wernfried Domscheit Indeed. 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.