如何在SQL中利用触发器实现父子表状态联动自动更新？-SQL-PHP中文网

mysql中before update触发器用old/new获取本行新旧值，子表更新直接用old.id关联；postgresql需return new（before）或null（after）；sql server的instead of需手动更新主表防递归。

如何在sql中利用触发器实现父子表状态联动自动更新？

触发器里怎么拿到父表更新前后的值

MySQL 的 BEFORE UPDATE 触发器能用 OLD.字段名 和 NEW.字段名 分别读取旧值和新值，但仅限当前表。如果要联动子表（比如订单主表 orders 更新 status 时同步更新其下的 order_items），必须在触发器里显式查父表——但注意：OLD 和 NEW 已经就是那条被改的记录，不需要再查父表本身；真正要查的是“这条父记录对应的子记录有哪些”。

常见错误是写成：SELECT * FROM orders WHERE id = OLD.id——这纯属多余，OLD.id 就是它自己。

正确做法是用 OLD.id 去关联子表：

UPDATE order_items 
SET status = NEW.status 
WHERE order_id = OLD.id;

PostgreSQL 中触发器函数必须返回 `NEW` 或 `OLD`

PostgreSQL 的行级触发器（FOR EACH ROW）要求函数必须返回一个 record：在 BEFORE 触发器中，返回 NEW 表示允许修改、返回 OLD 相当于取消本次更新；AFTER 触发器则必须返回 NULL 或忽略返回值（但函数定义仍需声明 RETURNS trigger）。

容易踩的坑：

忘了在函数末尾写 RETURN NEW;，导致 BEFORE UPDATE 触发器静默失败，整条更新被丢弃
在 AFTER 触发器里误写 RETURN NEW;，PostgreSQL 会报错 trigger procedure must return type trigger

示例（简化）：

CREATE OR REPLACE FUNCTION sync_item_status()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE order_items SET status = NEW.status WHERE order_id = NEW.id;
  RETURN NEW; -- BEFORE 触发器必须返回
END;
$$ LANGUAGE plpgsql;

SQL Server 的 `INSTEAD OF` 触发器不自动执行原操作

如果你在 SQL Server 里建的是 INSTEAD OF UPDATE，它会完全替代原始的 UPDATE 语句——也就是说，你得手动执行一次 UPDATE 主表，否则主表数据根本不会变。

典型错误写法：

CREATE TRIGGER tr_order_update ON orders
INSTEAD OF UPDATE
AS
BEGIN
  -- 只更新了子表，忘了更新 orders 自己
  UPDATE order_items SET status = i.status
  FROM inserted i
  WHERE order_items.order_id = i.id;
END

结果是：父表没更新，子表却变了，状态彻底错乱。

正确逻辑必须包含两步：

先执行对 orders 表本身的更新（用 inserted 表）
再用相同条件去更新子表

而且要注意：不能直接 UPDATE orders SET ...，否则会再次触发该触发器，造成无限递归——得用 SET NOCOUNT ON + 显式限定更新范围，或改用 AFTER 触发器更稳妥。

跨表更新性能差？别在触发器里做复杂 JOIN

触发器本质是行级操作，一旦父表批量更新（比如 UPDATE orders SET status='shipped' WHERE created_at > '2024-01-01'），每个匹配的 orders 行都会单独执行一遍触发器里的子表更新语句。如果子表没有 order_id 索引，或者触发器里写了 UPDATE ... SELECT ... FROM order_items JOIN products ... 这类多表关联，I/O 和锁开销会指数级上升。

建议只做最简动作：