触发器这东西光看文档会觉得很简单但真正在业务里用起来坑一个接一个。我在过去几年维护过不少 SQL Server 库最常被问到的需求之一就是给某张核心表加监听把 insert、update、delete 三种操作全部记录下来。很多人第一反应是改业务代码但业务代码只能记录应用层能感知到的修改数据库管理员随便开个窗口执行一句 UPDATE应用层完全无感。触发器不同它挂在表上只要有人对这表发起 DML它就跑躲都躲不掉。这篇文章我会直接用一套能跑通的示例把监听三种操作、记录变更前后值、处理多行操作、防止误删这些场景全部过一遍。无论你是要做数据审计、变更同步还是纯粹想防着有人乱改数据这套思路都适用。1. 为什么需要监听 insert、update、delete1.1 应用层记日志的三个盲区很多团队的第一版数据审计方案是在业务代码里加日志。接口层每个写入操作之后再往日志表里塞一条记录。这个方案听起来没问题但实际运行一段时间后你会发现三个盲区。第一个盲区是绕过应用层的修改。有人直接用数据库管理工具连上去执行一句 UPDATE业务代码根本没有机会执行日志自然也不会写。这在生产环境里不少见排查问题的时候数据已经变了旧值却找不到。第二个盲区是批处理作业。比如每天晚上跑存储过程做数据回刷、状态批量流转这些作业可能根本没有走正常业务接口如果作业内部不记日志审计就会断档。第三个盲区是改造成本。业务系统里凡是操作过这张表的代码点点都要改改漏一处日志就不完整而且业务代码会和日志逻辑纠缠在一起后期很难维护。触发器的核心价值在于它把监听逻辑放到了数据库引擎这一层跟业务调用方无关。任何连接、任何工具、任何语句只要触发了这张表的 DML触发器就会自动执行。用它可以补上应用层记日志的所有盲区。1.2 监听方案设计的关键决策写触发器之前先别急着敲代码有四个问题要想清楚。第一监听哪些操作。大多数需求是三种都监听但有些表只需要监听删除有些只需要监听价格列的变化。这决定了触发器是写一个还是拆三个以及动作判断怎么写。第二记录旧值还是新值。插入只有新值删除只有旧值更新则两者都有。如果你只记录新值不记录旧值一旦数据被连续改两次中间那次的原始值就永远丢失了。所以要审计就尽量把旧值和新值一起留下来宁可多存不要漏存。第三操作类型怎么识别。SQL Server 触发器里通过 inserted 和 deleted 两张逻辑表来判断不同存在组合代表不同操作。识别逻辑写错日志里的类型就会全部错乱。第四日志表的结构。是只记主键和时间还是要把整行快照都存下来是每个字段一行还是一行塞一个 JSON 字符串。这直接影响触发器的复杂度和后期查询体验。我自己的经验是先用 JSON 整行快照一份旧值一份新值简单可靠后续需要做明细对比时再拆字段也来得及。1.3 触发器与替代方案的边界说完触发器能做什么也得说清楚它的边界。SQL Server 也有 CDC变更数据捕获、Change Tracking、临时表等方案。CDC 能异步捕获变更并写入系统表适合大数据量同步到数仓的场景但配置复杂版本也有要求。Change Tracking 更适合做“哪些行变了”的判断不适合保存历史值。临时表只是临时存储不解决长期审计问题。如果需求只是“这张表的每次变更留个证据”触发器是最直接、最轻量的方案。但如果表特别大、写入并发非常高触发器里的额外写入会拉长事务时间这时候优先考虑 CDC 或走消息队列异步记录。实际选型时要看你是要精确历史值还是要高吞吐下的近似追踪。2. 写触发器前必须搞懂的基础概念2.1 触发器分类AFTER 与 INSTEAD OFSQL Server 触发器主要分两类。AFTER 触发器在 DML 操作成功之后执行适合做审计、日志、统计更新。INSERT、UPDATE、DELETE 触发了原始操作已经完成你在触发器里查询 inserted、deleted 看到的还是真实变更数据。如果触发器里报错整个事务会回滚所以 AFTER 触发器也可以当校验工具用。INSTEAD OF 触发器则完全替换了原始操作。比如对一张表执行 DELETE如果存在 INSTEAD OF DELETE 触发器数据库不会直接删而是执行触发器里的代码。这个特性适合做删除保护、复杂校验、逻辑删除。审计场景偶尔也会用它比如拦截不允许删除的数据并写日志。监听 insert、update、delete 最常用的还是 AFTER 触发器因为它不干扰正常业务只是额外记录。判断逻辑写在一个触发器里比三个触发器分别创建更好维护后面我会按这个思路写。2.2 INSERTED 和 DELETED 逻辑表触发器的心脏每次触发 DMLSQL Server 会在内存里生成两张逻辑表inserted 和 deleted。inserted 表保存新增行的新镜像。INSERT 操作时里面是新插入的行UPDATE 操作时里面是更新后的行。deleted 表保存受影响行的旧镜像。DELETE 操作时里面是被删除的行UPDATE 操作时里面是更新前的行。两张表都只存在于触发器会话期间语句结束后自动消失。所以判断操作类型的规则很简单INSERT只有 inserted 有数据deleted 没数据DELETE只有 deleted 有数据inserted 没数据UPDATEinserted 和 deleted 同时有数据需要注意这两张表的结构和原表完全一致但它们是逻辑表不能直接建索引。如果一次更新涉及很多行触发器的逻辑必须按多行来处理不能假设只有一行。2.3 如何准确区分 insert、update、delete最简单的区分方式是用 IF EXISTS 判断 inserted 和 deletedIF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted) BEGIN -- 更新操作 END ELSE IF EXISTS (SELECT 1 FROM inserted) BEGIN -- 插入操作 END ELSE IF EXISTS (SELECT 1 FROM deleted) BEGIN -- 删除操作 END这种写法要注意分支顺序。先判断“同时存在”再判断“只有 inserted”最后判断“只有 deleted”否则更新操作会被误判成插入或删除。另外还有 UPDATE() 函数和 COLUMNS_UPDATED() 函数。UPDATE() 只能判断某列是否被 UPDATE 语句引用了不能判断操作是 INSERT 还是 DELETE。COLUMNS_UPDATED() 返回一个二进制位掩码可以组合判断多列变化。但这属于更精细的列级监控后面我会讲实测用法。基础监听先记住上面三段式判断就够了。2.4 FOR JSON PATH 记录整行快照触发器里要保存旧值和新值最方便的方法是把整行数据转成一个字符串存进日志表。从 SQL Server 2016 开始可以直接用 FOR JSON PATH。比如SELECT ProductId, ProductName, Price, Stock, UpdatedBy FROM dbo.Product WHERE ProductId 1 FOR JSON PATH执行结果是 JSON 数组字符串。把这个结果塞进日志表的 OldRow 或 NewRow 字段整行信息就完整保留下来了。老版本没有 FOR JSON可以用 FOR XML PATH 做类似效果但 JSON 更直观查询时也可以用 JSON_VALUE 提取字段。这个方案的优势是通用性高不用为每个字段单独建列劣势是没法直接按某个字段的旧值做 SQL 条件过滤。但对于审计来说整行快照已经能回答绝大多数问题。3. 完整示例从建表到监听触发器一次跑通3.1 准备业务表和审计日志表以商品表为例先建一张业务表字段包含商品编号、名称、价格、库存、最后更新人CREATE TABLE dbo.Product ( ProductId INT PRIMARY KEY, ProductName NVARCHAR(100), Price DECIMAL(18, 2), Stock INT, UpdatedBy NVARCHAR(50) );再建一张审计日志表。我设计的字段包括日志自增编号、业务主键、操作类型、旧值、新值、记录时间和操作人CREATE TABLE dbo.Product_AuditLog ( LogId BIGINT IDENTITY(1,1) PRIMARY KEY, ProductId INT, ActionType NCHAR(6), OldRow NVARCHAR(MAX), NewRow NVARCHAR(MAX), LogTime DATETIME2(3) DEFAULT SYSDATETIME(), LogUser SYSNAME DEFAULT SUSER_SNAME() );ActionType 字段用 NCHAR(6)是因为 INSERT、UPDATE、DELETE 最长是 6 个字符。LogUser 默认取登录名如果想记录业务系统里的操作员可以用 CONTEXT_INFO 传值这个放到后面进阶部分。3.2 创建监听三种操作的审计触发器创建一个 AFTER 触发器监听三种操作CREATE TRIGGER dbo.trg_Product_Audit ON dbo.Product AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; -- 更新inserted 和 deleted 同时有数据 IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted) BEGIN INSERT INTO dbo.Product_AuditLog(ProductId, ActionType, OldRow, NewRow) SELECT i.ProductId, NUPDATE, (SELECT ProductId, ProductName, Price, Stock, UpdatedBy FROM deleted d WHERE d.ProductId i.ProductId FOR JSON PATH), (SELECT ProductId, ProductName, Price, Stock, UpdatedBy FROM inserted i2 WHERE i2.ProductId i.ProductId FOR JSON PATH) FROM inserted i; END -- 插入只有 inserted ELSE IF EXISTS (SELECT 1 FROM inserted) BEGIN INSERT INTO dbo.Product_AuditLog(ProductId, ActionType, OldRow, NewRow) SELECT t.ProductId, NINSERT, NULL, (SELECT ProductId, ProductName, Price, Stock, UpdatedBy FROM inserted i2 WHERE i2.ProductId t.ProductId FOR JSON PATH) FROM inserted t; END -- 删除只有 deleted ELSE IF EXISTS (SELECT 1 FROM deleted) BEGIN INSERT INTO dbo.Product_AuditLog(ProductId, ActionType, OldRow, NewRow) SELECT d.ProductId, NDELETE, (SELECT ProductId, ProductName, Price, Stock, UpdatedBy FROM deleted d2 WHERE d2.ProductId d.ProductId FOR JSON PATH), NULL FROM deleted d; END END;几个关键点说一下。SET NOCOUNT ON 是必须写的否则每次 DML 会返回多余的行计数很多客户端程序会报错。子查询里我都带了 WHERE 条件关联到外层的主键目的是只取当前字段对应的那一行数据。FOR JSON PATH 在没有 WHERE 的简单子查询里也能用但多行更新时容易把多行拼成一个 JSON 数组导致日志关联错误。我习惯在每条子查询里明确关联主键这样最稳。如果一次 UPDATE 改了主键本身关联就会出问题。生产环境里主键应该永远不应该被修改所以在设计业务表时就固定好主键不要在业务中出现“更新主键”这种操作。3.3 插入、更新、删除三连测试触发器建好之后按正常 DML 顺序做测试。先插入一条商品INSERT INTO dbo.Product(ProductId, ProductName, Price, Stock, UpdatedBy) VALUES (1, N挂耳咖啡, 25.00, 100, N张三);再更新这条数据UPDATE dbo.Product SET Price 28.50, Stock 80, UpdatedBy N李四 WHERE ProductId 1;最后删除这条数据DELETE FROM dbo.Product WHERE ProductId 1;然后查询审计日志SELECT LogId, ProductId, ActionType, OldRow, NewRow, LogTime, LogUser FROM dbo.Product_AuditLog ORDER BY LogId;正常情况下日志表里会有三条记录第一条 ActionType 是 INSERTNewRow 有值而 OldRow 为 NULL第二条 ActionType 是 UPDATEOldRow 和 NewRow 都有值第三条 ActionType 是 DELETEOldRow 有值而 NewRow 为 NULL。3.4 查看审计结果JSON 快照怎么读直接看 NVARCHAR(MAX) 里的 JSON内容是一串数组字符串初看会有点唬人。比如 UPDATE 那行的 NewRow 可能是[{ProductId:1,ProductName:挂耳咖啡,Price:28.50,Stock:80,UpdatedBy:李四}]想要单独取某个字段可以用 JSON_VALUE 配合路径。由于 FOR JSON PATH 默认生成数组字段路径要带数组下标比如SELECT JSON_VALUE(NewRow, $[0].Price) AS NewPrice, JSON_VALUE(OldRow, $[0].Price) AS OldPrice FROM dbo.Product_AuditLog WHERE ActionType NUPDATE;如果不想数组包里套一个对象可以使用 WITHOUT_ARRAY_WRAPPER但前提是子查询只能返回一行。多行更新时会报错所以我在上面的触发器里没有用这个选项保证通用性。4. 触发器进阶多行、防误删、错误处理4.1 多行更新必须用集合思维很多人第一次写触发器都会下意识把单行逻辑写上。比如声明一个 ProductId 变量从游标里循环取值或者直接用 SELECT Stock Stock FROM inserted。这在大批量操作时会出大问题因为 inserted 和 deleted 里可能同时有几百行数据取到的只是其中一行日志会漏成筛子。正确做法是全部写成集合操作。上面的触发器代码里INSERT INTO ... SELECT 天然就是多行插入一次 UPDATE 影响多少行日志就写多少行。测试一下批量更新UPDATE dbo.Product SET Stock Stock 10 WHERE Stock 100;只要 affected rows 大于 1日志就会对应出现多条 UPDATE 记录。你可以用 GROUP BY ProductId 验证每个受影响的主键都会生成一条日志。我踩过最深的坑就是子查询里没做主键关联导致批量更新时所有日志行循环引用同一行旧值。后来排查很久才发现是子查询少了一个 WHERE 条件。现在写触发器我有个习惯凡是用到 inserted 或 deleted 的子查询第一件事就是检查主键关联是否写全。4.2 用 THROW 和 ROLLBACK 让触发器当守门员触发器不仅能监听还能阻止操作。比如商品表里有个关键商品不允许删除可以在 AFTER DELETE 触发器里这么做CREATE TRIGGER dbo.trg_Product_ProtectDelete ON dbo.Product AFTER DELETE AS BEGIN SET NOCOUNT ON; IF EXISTS ( SELECT 1 FROM deleted WHERE ProductName N核心赠品 ) BEGIN THROW 50001, N核心赠品不允许删除, 1; END END;由于 AFTER 触发器运行在原始事务中一旦 THROW 抛出错误原始 DELETE 语句会自动回滚数据依然保留在表里。这个机制也可以用来做“有未清库存不允许删除”之类的业务校验比应用层校验更可靠因为无论谁发起 DELETE 都会被拦截。THROW 的 message 参数最长 2047 个字符错误号建议用 50000 以上的自定义号码避免和系统错误码冲突。事务里报错后要注意如果外层事务还有别的语句需要做统一回滚处理否则会产生逻辑上的部分成功。4.3 UPDATE() 和 COLUMNS_UPDATED() 实测对比有时你只关心某一列是否发生变化。比如价格列变了才记录库存变了就不记。这种需求用 UPDATE() 函数最简单。IF UPDATE(Price) BEGIN -- 写日志 ENDUPDATE() 只判断当前触发器中 UPDATE 语句是否引用了 Price 列它不关心值有没有真的变化。也就是说你写SET Price Price它也会判断为 True。如果要判断“值真的和旧值不同”得用实际比较IF EXISTS ( SELECT 1 FROM inserted i JOIN deleted d ON d.ProductId i.ProductId WHERE i.Price d.Price ) BEGIN -- 价格真的变了 ENDCOLUMNS_UPDATED() 返回的是位掩码可以用来精确判断多个列的组合变化。比如第 1 列是 ProductId第 3 列是 Price那 Price 的位掩码就是 4。判断 Price 和 Stock 是否被同时更新IF (COLUMNS_UPDATED() 0x14) 0x14 BEGIN -- Price 和 Stock 都在 UPDATE 语句的 SET 列表中 END这个函数调试时比较抽象建议只在复杂列级校验时使用。日常监听我还是优先用 IF EXISTS inserted/deleted 实际值比较因为它反映的是真实数据变化而不是“语句写没写这一列”。5. 常见故障排查与维护5.1 触发器没触发先查这四个开关触发器写完测试时发现一条日志都没写这个问题我遇到无数次。按顺序排查第一确认语句真的影响了行。UPDATE 条件如果匹配不到数据inserted 和 deleted 里面就是空的触发器执行了但什么都没记录。这不是触发器的锅。第二查触发器有没有被禁用。SQL Server 支持单独禁用触发器DISABLE TRIGGER dbo.trg_Product_Audit ON dbo.Product;禁用后 DML 照常执行但触发器完全不会跑。可以用系统视图确认状态SELECT name, is_disabled FROM sys.triggers WHERE parent_id OBJECT_ID(dbo.Product);第三检查数据库是否设置了限制。有些环境会通过 ALTER TABLE 禁止某些触发器或者触发器被多个对象依赖语法检查过了但未绑定成功。第四用 PRINT 或临时表排查。在触发器开头加一句PRINT trigger fired在 SSMS 的消息窗口里看能不能打印出来。生产环境不要加 PRINT否则每条 DML 都会刷消息我用的是简单临时表记录测试完删除。5.2 递归与嵌套触发差点把测试库跑死最危险的情况是触发器在内部又更新了同一张表然后递归触发自己。比如我在审计日志表上再挂了一个监听触发器然后日志触发器写日志时又去更新业务表形成循环。SQL Server 默认递归触发器设置是 OFF但嵌套触发器默认 ON所以要特别注意不要在多张表的触发器之间形成环。循环发生的典型现象是性能急剧下降大量会话阻塞日志以指数形式增长。排查时可以运行SELECT OBJECT_NAME(parent_id) AS TableName, name AS TriggerName, is_disabled FROM sys.triggers;如果确实需要限制递归层数可以在触发器开头判断IF TRIGGER_NESTLEVEL() 1 RETURN;这样只要触发器嵌套超过 1 层就立即退出避免循环写入。另一个办法是在数据库级别关闭递归ALTER DATABASE 你的数据库名 SET RECURSIVE_TRIGGERS OFF;5.3 触发器导致的锁等待和性能监控触发器里的所有操作都在原始 DML 事务内这意味着日志表写入越快主事务越短。如果日志表没有主键索引、没有合理聚集索引大量插入会引发页拆分拖慢整个事务。我常用两个办法监控触发器性能。第一个是观察阻塞链接当发现某个 UPDATE 卡住时调用EXEC sp_who2看 Blocking 列第二个是使用扩展事件捕获 sqlserver.dml_trigger_fired 事件看每个触发器的执行时长。如果发现某个触发器执行超过几百毫秒就该考虑简化逻辑或者把日志写成异步。需要注意简化逻辑不等于去掉日志。老项目经常为了速度把审计触发器的旧值记录删掉只记一个“更新了”事后出问题根本没法定位是谁改的。这不是性能问题是审计问题。5.4 常见错误速查表现象主要原因处理建议日志一条都没写触发器被禁用、语句影响行数为 0、触发器没创建成功检查 is_disabled、执行 SELECT COUNT 验证用 PRINT 定位ActionType 全部判断错误IF EXISTS 分支顺序写错先判断 UPDATE再判断 INSERT最后 DELETE多行更新只记了一行使用了变量或游标而不是集合式 INSERT...SELECT全部改成集合写法子查询必须按主键关联旧值永远是空的DELETE/UPDATE 分支没用 deleted 表检查子查询来源是否为 deleted批量导入特别慢FOR JSON 转换开销大、日志表无索引导入时临时禁用触发器导入完成后手工补齐日志更新一行反而报“子查询返回多行”主键关联缺失子查询匹配到多行在子查询 WHERE 中加主键条件6. 长期维护与避坑心得6.1 触发器管理操作一览日常维护触发器经常用这几条语句。查看表上的所有触发器EXEC sp_helptrigger dbo.Product;查看触发器定义适合排查某段逻辑是哪个版本改的EXEC sp_helptext dbo.trg_Product_Audit;禁用和启用DISABLE TRIGGER dbo.trg_Product_Audit ON dbo.Product; ENABLE TRIGGER dbo.trg_Product_Audit ON dbo.Product;删除DROP TRIGGER dbo.trg_Product_Audit;禁用触发器要特别注意时间窗口。如果在生产库上禁用了一个小时这个小时里的所有变更都不会记日志事后审计会出现空窗。我通常的做法是禁用触发器前先书面记录起止时间恢复后立即补一张说明日志把空窗期明确标注出来。6.2 命名规范、注释和文档触发器这东西最怕时间长了没人看得懂。触发器名字我统一用trg_表名_动作例如trg_Product_Audit、trg_Product_ProtectDelete。日志表统一用表名_AuditLog。这样一看就知道是哪个表上的什么触发器。每个触发器头部必须写清楚三件事用途、什么时候创建、有哪些注意事项。比如我写审计触发器时会加一段注释-- 用途商品表变更审计 -- 创建2024-01-15 -- 说明所有DML都会写Product_AuditLog禁止批量禁用超过10分钟这套注释一开始觉得多余等三个月后自己回来看代码才知道有多救命。文档不用长但至少要把“这个触发器为什么要存在、不能删、删了会有啥影响”写明白。6.3 日志表膨胀与清理策略监听触发器一旦上线日志表只增不减。商品表每天可能产生几万条变更一年下来日志表会非常庞大。不清理的话未来触发器的 INSERT 会因为日志表索引维护越来越慢拖累主表 DML。我的策略是在日志表上建一个 LogTime 的索引然后定期归档。归档最简单的方式是每个月建一张按月分表或者直接把超过 6 个月的日志搬走DELETE FROM dbo.Product_AuditLog WHERE LogTime DATEADD(MONTH, -6, SYSDATETIME());删除时要注意控制批次数一次删太多会放大事务日志。可以写一个循环一次删 10000 行避免长时间锁表。归档机制最好通过作业每天跑而不是等日志爆了再人工处理。最后再分享一个小技巧如果要在触发器里记录业务系统的真实操作人不要指望 SUSER_SNAME()它只会拿到数据库登录名。正确做法是在应用层启动会话时设置 CONTEXT_INFO在触发器里读出来这样日志才能真正追踪到是哪个业务用户在操作。我做过的最完善的一套审计就是业务用户、应用模块、客户端 IP、数据库登录名全部记录看起来麻烦但真出事的时候能省出一整个下午的排查时间。