Oracle calculate average using a trigger

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 th开发者_JAVA百科is 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 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.

How flexible is your data model ?

Rather than the storing the average rating on the recipe, can you store the total of all the rating plus the number of ratings.

An insert trigger on ratings would take the values or the new row to update the parent recipe row to add the rating to the total and 1 to the number/count of ratings.

An update trigger would add the difference between the :NEW and :OLD values to the total (and not update the count).

Neither trigger has to query other rows on the ratings table preventing the mutating table error, and making it much safer to use in an environment with multiple concurrent users.

The query (or a view or a derived column) would determine the average simply by dividing the total by the count.

This article gives one means of avoiding these errors.

Another thought - would a 'normal' trigger, rathen than a FOR EACH ROW trigger be more suitable here? If there are multiple recipe_rating updates for the same recipe in one statement you're calculating the average multiple times otherwise (hence the mutation warning).