电影评级触发器

Question

我想创造一个扳机，平均评分电影。我试图创建一个触发器，但是当我添加新的数据进行评级时，我会从触发器中得到一个错误

ORA-04091:表评等正在变化，触发器/函数可能看不到它

因为在触发器中使用了select，所以我一直在寻找解决该问题的方法。但是我不知道该如何修改我的代码，有什么解决方案吗？

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

Answer 1

您可以更喜欢使用语句级触发器，而不是行级触发器，从删除行FOR EACH ROW开始，以便获得新的触发器主体，如

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;
/

这样你就不会有tabe变异错误了。

Answer 2

为了解决这个有缺陷的设计，因为我最初的评论似乎不为OP所理解，下面是我所讲的内容的演示。数据设计的一个基本规则是不计算和存储可以在运行时计算的值。在这个特定的应用程序中，这意味着您不会不断更新和存储多个电影收视率的平均值。相反，在需要时计算它。

SQL> show user
USER is "SCOTT"
SQL> -- -------- create the tables
SQL> create table movies
  2     (    movie_id number not null enable,
  3          movie_title varchar2(30) not null enable,
  4           constraint movies_pk primary key (movie_id)
  5    )
  6  ;

Table created.

SQL> --
SQL> create table ratings
  2     (    rating_id number not null enable,
  3          movie_id number not null enable,
  4          rating_source varchar2(30) not null enable,
  5          movie_rating number not null enable,
  6           constraint ratings_pk primary key (rating_id, movie_id),
  7           constraint ratings_chk1 check (movie_rating in (1,2,3,4,5)) enable,
  8           constraint ratings_fk1 foreign key (movie_id)
  9            references scott.movies (movie_id) on delete cascade enable
 10     )
 11  ;

Table created.

SQL> -- -------- load tables
SQL> insert into movies values (1,'To Kill A Mockingbird');

1 row created.

SQL> insert into ratings values (1,1,'Rotten Tomatoes',2);

1 row created.

SQL> insert into ratings values (2,1,'ImDB',2);

1 row created.

SQL> insert into ratings values (3,1,'Amazon User',3);

1 row created.

SQL> --
SQL> insert into movies values (2,'Saving Private Ryan');

1 row created.

SQL> insert into ratings values (4,2,'Rotten Tomatoes',4);

1 row created.

SQL> insert into ratings values (5,2,'ImDB',5);

1 row created.

SQL> insert into ratings values (6,2,'Amazon User',5);

1 row created.

SQL>
SQL> -- -------- Do the query
SQL> select * from movies;

  MOVIE_ID MOVIE_TITLE
---------- ------------------------------
         1 To Kill A Mockingbird
         2 Saving Private Ryan

2 rows selected.

SQL> select * from ratings;

 RATING_ID   MOVIE_ID RATING_SOURCE                  MOVIE_RATING
---------- ---------- ------------------------------ ------------
         1          1 Rotten Tomatoes                           2
         2          1 ImDB                                      2
         3          1 Amazon User                               3
         4          2 Rotten Tomatoes                           4
         5          2 ImDB                                      5
         6          2 Amazon User                               5

6 rows selected.

SQL> select m.movie_title,
  2         avg(r.movie_rating)
  3  from movies m
  4    join ratings r on m.movie_id = r.movie_id
  5  group by m.movie_title
  6  order by m.movie_title
  7  ;

MOVIE_TITLE                    AVG(R.MOVIE_RATING)
------------------------------ -------------------
Saving Private Ryan                     4.66666667
To Kill A Mockingbird                   2.33333333

2 rows selected.

SQL> -- -------- clean up
SQL> drop table ratings purge;

Table dropped.

SQL> drop table movies purge;

Table dropped.

也就是说，最初的问题(如何写触发器)有一个家庭作业的所有指定用途问题。现在，如果任务是非常具体地编写这样一个触发器，人们会希望它的目的是传授一些编码技术。这将不是我们第一次看到一个基于一个非常有缺陷的设计的家庭作业，问题所暗示的设计是非常有缺陷的。当我看到这种情况时，我会认真地质疑讲师的资格，做一项违背基本数据设计原则的作业。它们应该已经涵盖了数据设计的基本原理(实际上，在一个专用于这类赋值的类中，以及对这类赋值的预定义)，包括但不限于数据规范化、第一范式、第二范式和第三范式(查找它们)。