怎样在SQL触发器中捕获被修改数据的修改前后完整差量-SQL-PHP中文网

SQL Server中AFTER UPDATE触发器通过deleted和inserted逻辑表获取新旧数据，需用主键或唯一键显式JOIN；MySQL用OLD/NEW字段；PostgreSQL推荐jsonb差量；均需注意NULL比较、LOB字段及性能问题。

怎样在sql触发器中捕获被修改数据的修改前后完整差量

SQL Server 的 `AFTER UPDATE` 触发器中如何拿到修改前后的完整行数据

SQL Server 是少数原生支持在触发器中直接访问新旧数据的主流数据库。关键在于使用 deleted 和 inserted 两个临时逻辑表：deleted 包含更新前的原始行（对 UPDATE 和 DELETE 有效），inserted 包含更新后的新行（对 INSERT 和 UPDATE 有效）。它们结构与原表一致，且按主键/唯一约束隐式关联——但注意：没有内置行级顺序保证，必须用主键或唯一列显式 JOIN。

常见错误是直接 SELECT * FROM deleted，结果发现多行时无法对应到 inserted 中哪一行。正确做法是：

始终基于主键（如 id）或业务唯一键（如 order_no）做 INNER JOIN，例如：

SELECT d.id, d.name AS old_name, i.name AS new_name, d.status AS old_status, i.status AS new_status FROM deleted d INNER JOIN inserted i ON d.id = i.id

如果表无主键，必须先确认存在能唯一标识行的列组合，否则差量不可靠
deleted 和 inserted 在单条 UPDATE 语句影响多行时，各自都可能有多行；不能假设只有一行

MySQL 的 `BEFORE UPDATE` 触发器里怎么记录“修改前”和“修改后”

MySQL 不提供 deleted/inserted 表。在 BEFORE UPDATE 触发器中，你可以读取 OLD.字段名（修改前值）和 NEW.字段名（即将写入的新值），这是唯一可靠方式。但注意：AFTER UPDATE 触发器中 OLD 和 NEW 同样可用，只是时机不同——BEFORE 可修改 NEW 值，AFTER 则不能。

要生成完整差量，需手动拼字段。例如：

INSERT INTO audit_log (table_name, row_id, field_name, old_value, new_value, updated_at) VALUES
('users', OLD.id, 'email', OLD.email, NEW.email, NOW()),
('users', OLD.id, 'status', OLD.status, NEW.status, NOW());

必须为每个需要审计的字段写一行 INSERT，无法用 * 或动态列名
字段类型影响可比性：比如 DATETIME 和 TIMESTAMP 在时区处理上可能表现不一致，建议统一转为字符串再比较
OLD 和 NEW 中的 NULL 值需用 IS NULL 判断，不能用 = NULL

PostgreSQL 的 `FOR EACH ROW` 触发器中如何安全提取变更字段

PostgreSQL 使用 OLD 和 NEW 记录（类似 MySQL），但更灵活：支持在触发器函数中用 hstore 或 jsonb 差量化整行。最简方案是用 jsonb_diff（需 jsonb 类型支持）或手写字段级对比。

推荐用 jsonb 提取差量，避免漏字段：

SELECT jsonb_each_text(NEW::jsonb - OLD::jsonb) AS diff;

该表达式返回所有被修改字段的 (key, value) 对，其中 value 是新值；未变字段自动被减掉
但 OLD 或 NEW 含有 jsonb、数组、几何类型等时，::jsonb 强转可能失败，需提前过滤或封装异常处理
若只要变化字段名列表，可用：(SELECT array_agg(key) FROM jsonb_each_text(NEW::jsonb - OLD::jsonb))

跨数据库通用陷阱：NULL、LOB 字段和性能隐患

所有数据库在差量捕获中都会遇到三类典型问题：NULL 值比较失效、大文本/二进制字段（LOB）导致触发器延迟、以及高并发下日志表写入成为瓶颈。

NULL 值永远不等于 NULL，所以 OLD.col != NEW.col 在任一为 NULL 时恒为 FALSE；必须写成 (OLD.col IS DISTINCT FROM NEW.col)（PostgreSQL）或 ISNULL(OLD.col,'') != ISNULL(NEW.col,'')（SQL Server）
MySQL 中 TEXT/BLOB 字段在触发器中不可直接用于 OLD/NEW 比较，会报错；应改用长度判断或跳过审计
审计日志表务必建好主键和按时间分区；否则千万级日志后，INSERT 触发器本身会拖慢主表 DML 性能