PostgreSQL触发器实现Payroll关联金额聚合不生效问题咨询

现有触发器逻辑失效的核心原因是触发时机和挂载表完全错误：

1. 业务侧创建薪资单的执行顺序固定为：先插入Payroll主表记录，拿到自增ID后，再插入所有关联的PayrollRow明细记录
2. 你把触发器绑在Payroll表的AFTER INSERT节点，触发器执行时关联的PayrollRow明细还没写入数据库，此时对PayrollRow做聚合查询只能得到空结果，自然算不出正确的汇总值
3. 硬编码payrollId=2能正常运行，本质是ID=2对应的PayrollRow记录早就持久化在库中，刚好绕开了触发时机不对的问题。

另外原有逻辑只覆盖了Payroll新建的场景，后续PayrollRow明细的金额修改、行删除、关联薪资单变更等操作都不会同步更新汇总值，上线后必然出现数据不一致。

不需要修改现有表结构，直接调整触发器逻辑即可，实现后所有对明细的写操作都会自动同步主表汇总值。

1. 清理原有无效触发器 #

DROP TRIGGER IF EXISTS new_payroll_creation ON "Payroll";

2. 创建通用汇总计算函数 #

函数自动适配明细的插入、更新、删除三种操作场景，识别受影响的薪资单ID重新计算总额，空聚合结果默认返回0避免空值：

CREATE OR REPLACE FUNCTION sync_payroll_total_amount()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
DECLARE
  target_payroll_id INT;
BEGIN
  -- 根据操作类型取需要更新的薪资单ID
  IF TG_OP = 'DELETE' THEN
    target_payroll_id := OLD."payrollId";
  ELSE
    target_payroll_id := NEW."payrollId";
  END IF;

  -- 重算对应薪资单的总应付金额
  UPDATE "Payroll"
  SET "amountEurToPay" = COALESCE(
    (SELECT SUM("paybackEur") FROM "PayrollRow" WHERE "payrollId" = target_payroll_id),
    0
  )
  WHERE "id" = target_payroll_id;

  RETURN NULL;
END;
$$;

3. 在PayrollRow明细表上绑定触发器 #

所有触发器设置为写操作完成后触发，保证聚合计算基于已落盘的明细数据：

-- 新增明细后同步总额
CREATE TRIGGER trigger_sync_payroll_on_row_insert
AFTER INSERT ON "PayrollRow"
FOR EACH ROW
EXECUTE FUNCTION sync_payroll_total_amount();

-- 修改明细金额、变更关联薪资单后同步总额
CREATE TRIGGER trigger_sync_payroll_on_row_update
AFTER UPDATE OF "paybackEur", "payrollId" ON "PayrollRow"
FOR EACH ROW
EXECUTE FUNCTION sync_payroll_total_amount();

-- 删除明细后同步总额
CREATE TRIGGER trigger_sync_payroll_on_row_delete
AFTER DELETE ON "PayrollRow"
FOR EACH ROW
EXECUTE FUNCTION sync_payroll_total_amount();

• 该实现完全适配你提供的Prisma生成的表结构，不管是单条操作明细还是Prisma嵌套写入创建薪资单（主表+明细一同提交），都能保证amountEurToPay字段值和明细总和一致
• 如果业务存在单批次批量操作成百上千条PayrollRow的场景，可以将行级触发器替换为语句级触发器配合过渡表使用，能大幅提升计算性能，常规业务规模下上述行级触发器完全可以满足性能要求
• 原有触发器定义里调用的函数名和实际创建的函数名存在大小写拼写差异，虽然PostgreSQL默认会把未加双引号的标识符转成全小写不会直接报错，但复杂场景下容易触发标识符找不到的问题，建议后续定义数据库对象时统一使用蛇形命名规避这类问题。

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