Oracle calculate average using a trigger

Question

For a school project we are forced to have redundant information and update it by using triggers. We have a table called 'recipe_ratings' that contains a 'rating' (numbers 0-100). In our 'recipes' table we have a redundant row called 'rating' that contains the average rating for that specific recipe.

We tried to create an Oracle trigger like this:

CREATE OR REPLACE TRIGGER trigger_rating 
AFTER UPDATE 
  ON recipe_ratings 
  FOR EACH ROW

DECLARE 
  average_rating NUMBER;

BEGIN
  SELECT ROUND(AVG(rating))
  INTO average_rating
  FROM recipe_ratings
  WHERE rid = :new.rid;

  UPDATE recipe SET rating = average_rating
  WHERE rid = :new.rid 

END;

But this gives us: ORA-04091: table name is mutating, trigger/function may not see it. We are experimenting with 'autonomous transaction' but it feels like we're drifting away from our trigger.

How can we make this trigger work?

I'm hoping the only reason for this school project is to give you the experience to know to avoid this sort of redundancy like the plague :) — Jeffrey Kemp

Justin Cave Justin Cave · Accepted Answer · 2011-05-03T22:55:47

I hope the professor is not leading you down the path of using autonomous transactions which would be a hideous misuse of autonomous transactions in addition to using an invalid data model.

In the real world, in order for this sort of thing to work, you would need

A package with a collection of RID values
A before statement trigger that initializes this collection
A row-level trigger that inserts the :new.rid values into the collection
An after statement trigger that reads through the collection and issues the updates on the RECIPE_RATINGS table

Obviously, that sort of thing gets quite cumbersome quite quickly which is why storing redundant data is so problematic.

If you only had to handle inserts and you could guarantee that all inserts would be single-row inserts using the INSERT ... VALUES, you could query the RECIPE_RATINGS table in your query. That doesn't work in the real world, but it may suffice in a classroom.

If you don't mind re-computing the average rating for every recipe every time a single row in RECIPE_RATINGS is updated-- something that would be catastrophic in practice but may work on a sufficiently small data set-- you could have an after statement trigger that does a correlated update on every row of the RECIPE table.

Oracle calculate average using a trigger

3 Answers