0

My goal is to create trigger, which will update the value in Project in case of Task update. Every task has an estimated time, project has column where the average estimated time is stored. What I want is that if the estimated time of one task is updated, the trigger will change average time of project.

I can't figure out how to do this, I have come up with code below, but it is not working.

CREATE OR REPLACE TRIGGER trig_task_update
AFTER update
ON Task
FOR EACH ROW
BEGIN
  UPDATE Project p SET (avg_task_estimated_time) = (SELECT AVG(estimatedTime) FROM TASK t WHERE t.project_idProject= p.idProject) WHERE p.idProject = :NEW.project_idProject;
END;
/

After update on table TASK I am getting the error: 

ORA-04091: table TASK is mutating, trigger/function may not see it
BSMP
  • 4,596
  • 8
  • 33
  • 44
Pogasta
  • 117
  • 1
  • 14

1 Answers1

0

You cannot query the table which triggers the code (at row level trigger). Workaround example: https://stackoverflow.com/questions/5805986/oracle-after-delete-trigger-how-to-avoid-mutating-table-ora-04091

Jokke Heikkilä
  • 918
  • 1
  • 8
  • 17