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

Question

So, I'm a french student in IT, I've got this work like 4 weeks before, and I really can't get around this problem (I mainly worked on PostGRE and it's way different for me).

I've got those two tables : link

To resume, I've got one table LIVRES (which is book in French), where there's two attributes :

-"refl", the primary key,

-"note_moy", a calculated attribute, later on that.

And a table AVIS (Rating I guess) with :

-"refl" as part Primary key (the other part is irrelevant), and foreign key references to LIVRES

-"note", with is rating from 0 to 20.

So the idea, is that "note_moy" on LIVRES is the average of all "note" ON AVIS, where the LIVRES.refl = AVIS.refl. So a book has an average rating, and I needed to do a TRIGGER, so every time there's a UPDATE or INSERT successfull, I would use the procedure "Maj_note_moy", who take a "refl", make the AVG of "note" on AVIS, and update the "note_moy" on LIVRES with that AVG.

Here the procedure :

CREATE OR REPLACE PROCEDURE maj_note_moy(vrefl livres.refl%type) IS
v_note_moy livres.note_moy%type;
BEGIN
SELECT AVG(note) INTO v_note_moy 
FROM AVIS
WHERE refl = vrefl;
UPDATE Livres SET note_moy = v_note_moy WHERE refl = vrefl;
--RETURN v_note_moy;
END;
/

When I use it with a PLSQL block (like this), it works no problem.

Now, back to the trigger, I try this :

CREATE OR REPLACE TRIGGER trigger_note_moy
AFTER INSERT ON Avis 
FOR EACH ROW
BEGIN
Maj_note_moy(:new.refl);
END;
/

And here it is : "Oracle : ORA-04091: table AVIS is mutating, trigger/function may not see it"

I understand what's the problem is, that I trying to do a SELECT on the table AVIS while I insert something into it. But that's what I can't get, I want the trigger after my insert or update, so why is it an issue ? I'm pretty sure I do something like this on PostGRE, and it didn't cause any problems.

So I check, I do saw the COMPOUND TRIGGER, but I really don't understand how it works, nor if it's relevant for my problem.

Justin_Cave_10397 · Answer

As @ThorsetnKettner points out in the comments, storing data redundantly via a trigger is rarely a good architectural approach. Embed the calculation in a view or even a materialized view and then you don't have to write code to maintain the data or debug issues when your code has bugs (and in a multi-user system, aggregate values maintained by triggers will almost always have bugs).

Since you're a student, though, I'm guessing that this is part of a homework assignment. If so

CREATE OR REPLACE TRIGGER trigger_note_moy
  AFTER INSERT ON Avis 
  FOR EACH ROW
BEGIN
  SELECT AVG(note) 
    INTO :new.note_moy
    FROM AVIS
   WHERE refl = :new.refl;
END;

or

CREATE OR REPLACE FUNCTION maj_note_moy(vrefl livres.refl%type) 
  RETURN livres.note_moy%type
IS
  v_note_moy livres.note_moy%type;
BEGIN
  SELECT AVG(note) INTO v_note_moy 
    FROM AVIS
   WHERE refl = vrefl;
  RETURN v_note_moy;
END;

CREATE OR REPLACE TRIGGER trigger_note_moy
  AFTER INSERT ON Avis 
  FOR EACH ROW
BEGIN
  :new.note_moy := maj_note_moy( :new.refl );
END;

would avoid the mutating table exception without needing to use a compound trigger.

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

1 Answers