如何创建触发器实现Purchases表变动同步更新Total_Expense总金额

• 触发器函数未适配三种操作逻辑：当前代码仅处理了新增场景下累加新金额的逻辑，删除操作没有扣减被删除行的金额，更新操作也没有计算新旧金额的差值做调整
• 空表无匹配行：如果Total_Expense表中没有初始记录，UPDATE语句不会命中任何行，自然不会产生更新结果
• PostgreSQL默认将未加引号的标识符转为小写，如果你建表时指定了大写的表名/字段名，需要用双引号包裹才能正确匹配对象

方案1：增量更新（性能更优，适合数据量大的场景） #

首先先给Total_Expense表添加约束，确保仅存在一条总金额记录：

-- 先给Total_Expense加唯一行约束，避免多数据混乱
ALTER TABLE Total_Expense ADD COLUMN id INT DEFAULT 1 PRIMARY KEY;
-- 初始化总金额（如果还没有数据的话）
INSERT INTO Total_Expense (total_amount) SELECT COALESCE(SUM(amount),0) FROM Purchases ON CONFLICT (id) DO NOTHING;

然后修正触发器函数：

CREATE OR REPLACE FUNCTION update_sum() RETURNS TRIGGER AS 
$$BEGIN
IF TG_OP = 'INSERT' THEN
    UPDATE Total_Expense SET total_amount = total_amount + NEW.amount;
ELSIF TG_OP = 'DELETE' THEN
    UPDATE Total_Expense SET total_amount = total_amount - OLD.amount;
ELSIF TG_OP = 'UPDATE' THEN
    UPDATE Total_Expense SET total_amount = total_amount + (NEW.amount - OLD.amount);
END IF;
RETURN NULL;
END
$$
LANGUAGE plpgsql;

触发器不用修改，保持原来的即可：

CREATE TRIGGER updateSum AFTER INSERT OR DELETE OR UPDATE ON Purchases
FOR EACH ROW
EXECUTE PROCEDURE update_sum();

方案2：全量聚合更新（逻辑简单无误差，适合数据量小的场景） #

不用区分操作类型，每次触发直接重新计算总金额，避免增量计算出现的统计误差：

CREATE OR REPLACE FUNCTION update_sum() RETURNS TRIGGER AS 
$$BEGIN
UPDATE Total_Expense SET total_amount = (SELECT COALESCE(SUM(amount),0) FROM Purchases);
-- 处理Total_Expense无初始记录的情况
IF NOT FOUND THEN
    INSERT INTO Total_Expense (total_amount) SELECT COALESCE(SUM(amount),0) FROM Purchases;
END IF;
RETURN NULL;
END
$$
LANGUAGE plpgsql;

• 增量更新方案需要确保Total_Expense的初始值和Purchases的现有总金额一致，否则会出现统计偏差
• 如果不需要兼容删除、更新场景，可以删掉对应分支的逻辑
• 每次修改触发器函数后，不需要重新创建触发器，修改函数即可生效

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