插入watch表新条目时通过触发器更新movie表平均评分问题求助

原有方案的核心问题 #

• 计算平均的时机错误：在BEFORE EACH ROW阶段查询评分表，此时新插入的评分还未写入WATCH表中，计算出的平均值不包含本次新数据，结果完全不准确。
• 触发变异表错误：行级触发器执行时，WATCH表正处于修改状态，Oracle不允许在行级触发器中直接读取触发DML的表，会抛出ORA-04091变异表错误。
• 复合触发器使用逻辑错误：没有利用复合触发器的语句级阶段处理读表逻辑，错误地在两个行级阶段拆分计算和更新操作，完全没有发挥复合触发器解决变异表问题的作用。

正确实现方案 #

方案1：增量更新（推荐，性能最高，无变异表问题） #

先给Movie表新增两个冗余字段：总评分total_rating（数值类型）、评分人数rating_count（整数类型），不需要每次全表扫描计算平均，更新效率为O(1)，适合数据量较大的场景：

CREATE OR REPLACE TRIGGER update_movie_avg_rating
AFTER INSERT ON WATCH
FOR EACH ROW
BEGIN
    UPDATE Movie 
    SET total_rating = total_rating + :NEW.rating,
        rating_count = rating_count + 1,
        avg_rating = (total_rating + :NEW.rating) / (rating_count + 1)
    WHERE movie_ID = :NEW.movie_ID;
END;
/

方案2：复合触发器全量计算（适用于不允许修改Movie表结构的场景） #

利用复合触发器的语句级执行阶段读取WATCH表，避免变异表错误，同时对同个电影的多次批量插入做去重更新优化：

CREATE OR REPLACE TRIGGER new_avg
FOR INSERT ON WATCH
COMPOUND TRIGGER
    -- 定义集合存储本次插入涉及的movie_id，自动去重避免重复更新
    TYPE t_movie_ids IS TABLE OF WATCH.movie_ID%TYPE INDEX BY PLS_INTEGER;
    g_movie_ids t_movie_ids;

    AFTER EACH ROW IS
    BEGIN
        -- 存入本次插入关联的电影ID
        g_movie_ids(:NEW.movie_ID) := :NEW.movie_ID;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_avg REAL;
    BEGIN
        -- 语句级阶段所有插入已完成，遍历所有涉及的电影计算最新平均
        FOR i IN 1..g_movie_ids.COUNT LOOP
            SELECT AVG(rating) INTO v_avg 
            FROM WATCH 
            WHERE movie_ID = g_movie_ids(i);
            
            UPDATE Movie 
            SET avg_rating = v_avg 
            WHERE movie_ID = g_movie_ids(i);
        END LOOP;
    END AFTER STATEMENT;
END;
/

内容的提问来源于stack exchange，提问作者Alex Lab