PostgreSQL触发器函数无法同步新增数据至汇总表问题排查

核心问题 #

你的触发器函数完全没用到detailed_report表——当前汇总数据是从category、film_category、film三张表聚合计算的，所以往detailed_report插入的新数据根本不会被纳入统计，自然不会出现在total_revenue_by_category里。

解决方案 #

根据业务需求，分两种情况修改触发器函数：

情况1：仅基于detailed_report表统计汇总

如果total_revenue_by_category的数据源就是detailed_report，直接修改聚合逻辑：

CREATE OR REPLACE FUNCTION update_summary_trigger_function()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
    DELETE FROM total_revenue_by_category;
    INSERT INTO total_revenue_by_category (name, total_revenue)
    SELECT name, SUM(rental_rate) AS total_revenue
    FROM detailed_report
    GROUP BY name;
    RETURN NEW;
END;
$$;

情况2：合并原有film数据与detailed_report数据统计

如果需要把detailed_report的补充数据和原有的影片数据一起统计，用UNION ALL合并两个数据源后再聚合：

CREATE OR REPLACE FUNCTION update_summary_trigger_function()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
    DELETE FROM total_revenue_by_category;
    INSERT INTO total_revenue_by_category (name, total_revenue)
    SELECT name, SUM(rental_rate) AS total_revenue
    FROM (
        -- 原有影片分类收入数据
        SELECT c.name, f.rental_rate
        FROM category c
        JOIN film_category fc ON c.category_id = fc.category_id
        JOIN film f ON fc.film_id = f.film_id
        -- 合并detailed_report的新增数据
        UNION ALL
        SELECT dr.name, dr.rental_rate
        FROM detailed_report dr
    ) combined_data
    GROUP BY name;
    RETURN NEW;
END;
$$;

额外说明 #

当前使用FOR EACH STATEMENT触发器的逻辑没问题，批量插入时也能正常触发。但全量清空再插入的方式在数据量大时性能一般，学校项目场景下可以接受；如果后续要优化，也可以改成增量更新（比如只更新新增类别或对应类别的总和）。

内容的提问来源于stack exchange，提问作者Luke Ruiz