Movies rating trigger-CodePudding

I would like to create a trigger that average rating movies. I have tried to create a trigger but when I'm adding new data for rating, I get an error from the trigger

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

I have searched for solutions for that problem because using select in the trigger. But I don't know how I should change my code, is there any solution for this case?

CREATE OR REPLACE TRIGGER AvgRate_trig
AFTER INSERT ON Rating
FOR EACH ROW
BEGIN
    UPDATE TB_MOVIES 
    SET AvgRating = (SELECT AVG(RATE) FROM RATING 
                     WHERE tb_movies.movieid = rating.movieid)
    WHERE movieid = new.movieid;
END

CodePudding user response：

You can prefer using a statement level trigger rather than a row level trigger starting by removing the line FOR EACH ROW so as to get new trigger body such as

CREATE OR REPLACE TRIGGER AvgRate_trig
  AFTER INSERT ON rating
BEGIN
  UPDATE tb_movies t
     SET AvgRating =
         (SELECT AVG(rate)
            FROM rating r
           WHERE t.movieid = r.movieid)
   WHERE movieid IN 
         (SELECT movieid
            FROM rating
           GROUP BY movieid)   
END;
/

this way you don't get tabe mutating error.