PostgreSQL中双触发器更新同一表失效及合并方案求助

一、触发器冲突与合并失效的原因 #

1. 多触发器执行顺序问题 #

多个触发器绑定到关联操作链时，数据库的触发器执行顺序可能导致后创建的触发器被阻塞或覆盖。比如订单触发器执行时锁定了CustomerDepositBalance表，导致退货触发器的更新请求无法写入；或是数据库默认的触发器执行优先级让其中一个触发器的操作覆盖了另一个。

2. 合并触发器的TG_TABLE_NAME判断错误 #

合并后的函数可能存在表名判断偏差：比如数据库默认将表名存储为小写，但你用了大写表名做判断；或是触发器没有同时绑定到OrderDetails和DepositReturnDetails两张明细表上，导致函数根本没被触发。

二、修复方案 #

1. 保留独立触发器并调整执行逻辑 #

给两个触发器的函数统一使用UPSERT（INSERT ... ON CONFLICT DO UPDATE）语法，避免锁冲突，同时指定执行优先级（以PostgreSQL为例）：

订单触发器函数

CREATE OR REPLACE FUNCTION trigger_update_deposit_balance_order()
RETURNS TRIGGER AS $$
BEGIN
  DECLARE
    _qty INT := CASE
      WHEN TG_OP = 'INSERT' THEN NEW.quantity
      WHEN TG_OP = 'UPDATE' THEN NEW.quantity - OLD.quantity
      WHEN TG_OP = 'DELETE' THEN -OLD.quantity
    END;
  BEGIN
    INSERT INTO CustomerDepositBalance (customer_id, sku, total_ordered, total_returned)
    VALUES ((SELECT customer_id FROM Orders WHERE id = NEW.order_id), NEW.sku, _qty, 0)
    ON CONFLICT (customer_id, sku)
    DO UPDATE SET total_ordered = CustomerDepositBalance.total_ordered + _qty;
    RETURN NULL;
  END;
END;
$$ LANGUAGE plpgsql;

退货触发器函数

CREATE OR REPLACE FUNCTION trigger_update_deposit_balance_return()
RETURNS TRIGGER AS $$
BEGIN
  DECLARE
    _qty INT := CASE
      WHEN TG_OP = 'INSERT' THEN NEW.quantity
      WHEN TG_OP = 'UPDATE' THEN NEW.quantity - OLD.quantity
      WHEN TG_OP = 'DELETE' THEN -OLD.quantity
    END;
  BEGIN
    INSERT INTO CustomerDepositBalance (customer_id, sku, total_ordered, total_returned)
    VALUES ((SELECT customer_id FROM DepositReturns WHERE id = NEW.return_id), NEW.sku, 0, _qty)
    ON CONFLICT (customer_id, sku)
    DO UPDATE SET total_returned = CustomerDepositBalance.total_returned + _qty;
    RETURN NULL;
  END;
END;
$$ LANGUAGE plpgsql;

创建带优先级的触发器

-- 订单触发器设为立即执行
CREATE TRIGGER trigger_order_update_balance
AFTER INSERT OR UPDATE OR DELETE ON OrderDetails
FOR EACH ROW
EXECUTE FUNCTION trigger_update_deposit_balance_order()
DEFERRABLE INITIALLY IMMEDIATE;

-- 退货触发器设为延迟执行，避免锁冲突
CREATE TRIGGER trigger_return_update_balance
AFTER INSERT OR UPDATE OR DELETE ON DepositReturnDetails
FOR EACH ROW
EXECUTE FUNCTION trigger_update_deposit_balance_return()
DEFERRABLE INITIALLY DEFERRED;

2. 修复合并后的触发器函数 #

确保触发器绑定到两张明细表，且表名判断匹配数据库实际存储格式（多为小写）：

合并后的函数

CREATE OR REPLACE FUNCTION update_deposit_balance()
RETURNS TRIGGER AS $$
BEGIN
  DECLARE
    _customer_id INT;
    _qty INT;
  BEGIN
    _qty := CASE
      WHEN TG_OP = 'INSERT' THEN NEW.quantity
      WHEN TG_OP = 'UPDATE' THEN NEW.quantity - OLD.quantity
      WHEN TG_OP = 'DELETE' THEN -OLD.quantity
    END;

    -- 注意表名是小写，匹配数据库存储格式
    IF TG_TABLE_NAME = 'orderdetails' THEN
      _customer_id := (SELECT customer_id FROM Orders WHERE id = NEW.order_id);
      INSERT INTO CustomerDepositBalance (customer_id, sku, total_ordered, total_returned)
      VALUES (_customer_id, NEW.sku, _qty, 0)
      ON CONFLICT (customer_id, sku)
      DO UPDATE SET total_ordered = CustomerDepositBalance.total_ordered + _qty;
    ELSIF TG_TABLE_NAME = 'depositreturndetails' THEN
      _customer_id := (SELECT customer_id FROM DepositReturns WHERE id = NEW.return_id);
      INSERT INTO CustomerDepositBalance (customer_id, sku, total_ordered, total_returned)
      VALUES (_customer_id, NEW.sku, 0, _qty)
      ON CONFLICT (customer_id, sku)
      DO UPDATE SET total_returned = CustomerDepositBalance.total_returned + _qty;
    END IF;
    RETURN NULL;
  END;
END;
$$ LANGUAGE plpgsql;

绑定到两张明细表

CREATE TRIGGER trigger_order_update_balance
AFTER INSERT OR UPDATE OR DELETE ON OrderDetails
FOR EACH ROW
EXECUTE FUNCTION update_deposit_balance();

CREATE TRIGGER trigger_return_update_balance
AFTER INSERT OR UPDATE OR DELETE ON DepositReturnDetails
FOR EACH ROW
EXECUTE FUNCTION update_deposit_balance();

三、替代方案：用视图替代实时触发器 #

如果触发器的锁冲突或维护成本过高，可采用视图方案：

1. 普通实时视图 #

适合对实时性要求高、数据量不大的场景：

CREATE VIEW CustomerDepositBalance_VW AS
SELECT
  COALESCE(o.customer_id, dr.customer_id) AS customer_id,
  COALESCE(od.sku, drd.sku) AS sku,
  SUM(CASE WHEN od.id IS NOT NULL THEN od.quantity ELSE 0 END) AS total_ordered,
  SUM(CASE WHEN drd.id IS NOT NULL THEN drd.quantity ELSE 0 END) AS total_returned
FROM Orders o
LEFT JOIN OrderDetails od ON o.id = od.order_id
FULL JOIN DepositReturns dr ON o.customer_id = dr.customer_id
LEFT JOIN DepositReturnDetails drd ON dr.id = drd.return_id AND od.sku = drd.sku
GROUP BY COALESCE(o.customer_id, dr.customer_id), COALESCE(od.sku, drd.sku);

2. 物化视图（定期刷新） #

适合对实时性要求不高的场景，避免触发器锁问题：

CREATE MATERIALIZED VIEW CustomerDepositBalance_MV AS
SELECT
  COALESCE(o.customer_id, dr.customer_id) AS customer_id,
  COALESCE(od.sku, drd.sku) AS sku,
  SUM(CASE WHEN od.id IS NOT NULL THEN od.quantity ELSE 0 END) AS total_ordered,
  SUM(CASE WHEN drd.id IS NOT NULL THEN drd.quantity ELSE 0 END) AS total_returned
FROM Orders o
LEFT JOIN OrderDetails od ON o.id = od.order_id
FULL JOIN DepositReturns dr ON o.customer_id = dr.customer_id
LEFT JOIN DepositReturnDetails drd ON dr.id = drd.return_id AND od.sku = drd.sku
GROUP BY COALESCE(o.customer_id, dr.customer_id), COALESCE(od.sku, drd.sku);

-- 创建刷新函数
CREATE OR REPLACE FUNCTION refresh_deposit_balance_mv()
RETURNS VOID AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CustomerDepositBalance_MV;
END;
$$ LANGUAGE plpgsql;

-- 每天凌晨刷新一次（需提前安装pg_cron扩展）
SELECT cron.schedule('refresh-deposit-balance', '0 0 * * *', 'SELECT refresh_deposit_balance_mv();');

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