Postgres/Supabase：如何用触发器维护collections表items_total计数？

先排查你的现有触发器未生效的常见原因：

• 插入的items行中collection_id为NULL，或者该值在collections表中没有匹配的id
• 执行触发器函数的用户没有UPDATE public.collections的权限
• 插入操作处于未提交的事务中，导致计数更新未持久化

如果确认以上问题都不存在，以下是完善后的代码——不仅能处理插入时的计数增加，还能覆盖删除、collection_id变更时的计数调整，完整实现计数维护需求：

CREATE OR REPLACE FUNCTION update_items_total() 
RETURNS trigger AS $$ 
BEGIN
    -- 插入事件：对应集合计数+1
    IF TG_OP = 'INSERT' THEN
        UPDATE public.collections
        SET items_total = items_total + 1
        WHERE id = NEW.collection_id;
    -- 删除事件：对应集合计数-1
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE public.collections
        SET items_total = items_total - 1
        WHERE id = OLD.collection_id;
    -- 更新事件：若collection_id变更，调整两个集合的计数
    ELSIF TG_OP = 'UPDATE' AND OLD.collection_id != NEW.collection_id THEN
        -- 原集合计数-1
        UPDATE public.collections
        SET items_total = items_total - 1
        WHERE id = OLD.collection_id;
        -- 新集合计数+1
        UPDATE public.collections
        SET items_total = items_total + 1
        WHERE id = NEW.collection_id;
    END IF;
    RETURN NULL; -- AFTER触发器无需返回行，返回NULL符合规范
END; 
$$ LANGUAGE plpgsql;

-- 创建覆盖INSERT/DELETE/UPDATE的触发器
CREATE TRIGGER maintain_collection_item_count
AFTER INSERT OR DELETE OR UPDATE OF collection_id ON public.items
FOR EACH ROW
EXECUTE PROCEDURE update_items_total();

关键优化说明： #

• 新增删除、collection_id变更场景的处理，确保计数始终准确
• 补充RETURN NULL，符合PostgreSQL AFTER触发器的执行规范（原函数未返回值可能引发潜在问题）
• 触发器仅在collection_id字段更新时触发，避免无意义的执行消耗

验证步骤： #

1. 插入一条items记录（指定存在的collection_id），检查对应collections的items_total是否+1
2. 删除该items记录，检查计数是否-1
3. 更新该items的collection_id到另一个存在的集合，检查两个集合的计数是否分别-1和+1

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