Postgres中如何触发AFTER UPDATE及解决插入不触发触发器问题

• 触发器监听事件不匹配：当前配置的触发器仅监听了UPDATE操作，但测试时执行的是INSERT语句，自然不会触发；同时需要覆盖的DELETE操作也未加入监听列表
• 存储过程包含非法事务控制语句：触发器运行在触发它的父事务上下文内，PostgreSQL 不允许在触发器调用的代码中执行ROLLBACK/COMMIT这类事务控制语句，这是触发器调用失败的核心原因
• 额外优化点：如果使用行级触发器（FOR EACH ROW），每次插入/更新/删除一行都会调用一次存储过程，会产生大量不必要的重复执行，更适合用语句级触发器，一次操作仅执行一次

1. 修改存储过程，移除事务控制语句 #

把存储过程里的ROLLBACK替换为抛出异常即可，触发异常后父事务会自动回滚，不需要手动控制：

CREATE OR REPLACE PROCEDURE get_top_ten_rentals()
AS
$$
BEGIN
    -- 清空存量数据刷新榜单
    DELETE FROM report.top_ten_rentals;

    INSERT INTO report.top_ten_rentals (title, inventory_id, rating, length, times_rented, total)
    SELECT f.title AS title,
           r.inventory_id AS inventory_id,
           f.rating,
           fn_transform_length(f.length),
           COUNT(*) AS times_rented,
           SUM(p.amount) AS total
    FROM public.payment AS p
        JOIN public.rental AS r ON p.rental_id = r.rental_id
        JOIN public.inventory AS i ON r.inventory_id = i.inventory_id
        JOIN public.film AS f ON i.film_id = f.film_id
    GROUP BY r.inventory_id, f.title, f.rating, f.length
    ORDER BY total DESC
    LIMIT 10;

    EXCEPTION
        WHEN OTHERS THEN
            -- 直接抛出异常，触发父事务自动回滚
            RAISE;
END;
$$
LANGUAGE plpgsql;

2. 创建支持多事件的语句级触发器 #

先删除旧触发器，再创建支持全操作监听的新触发器：

DROP TRIGGER IF EXISTS tr_new_rentals ON public.rental;

CREATE TRIGGER tr_new_rentals
    AFTER INSERT OR UPDATE OR DELETE ON public.rental
    FOR EACH STATEMENT
    EXECUTE FUNCTION public.fn_rental_trigger();

执行测试插入语句：

INSERT INTO public.rental (rental_date, inventory_id, customer_id, return_date, staff_id, last_update)
VALUES (NOW(), 4030, 459, NOW() + interval '7 day', 1, NOW());

执行完成后查询report.top_ten_rentals表，即可看到数据已经更新。

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