先说个真实情况。有次我在系统上线后接到一个需求业务表里的数据被改了要求能查出来这条记录曾经长什么样、是什么时候变的、甚至是谁动的。当时那批表没开启CDC业务代码又分散在好几个服务里接口改了还不一定有人记日志。翻来覆去想了几种方案最后采用的竟然是以前从来没正眼看过的SQL Server触发器。这篇文章就围绕sqlserver触发器示例这个主题把监听insert、update、delete三种操作的前前后后说透包括示例代码、触发原理、实际项目中的注意事项。适合数据库刚入门的同学也适合已经有一堆业务表、想快速加数据审计功能的开发者提供一个可以直接参考复用的样板。1. 先说清楚为什么用触发器做监听比改业务代码更省心很多人一听到触发器第一反应是“性能差、容易失控、少用为好”。这个想法不算错尤其是早期系统里滥用触发器确实容易造成连锁问题。但如果你只想在数据库层监听某张表的insert、update、delete触发器反而是成本最低、侵入性最小的手段。1.1 应用层埋点最大的问题在应用里记录数据变更听起来很简单在更新接口里执行完SQL之后再往日志表里插一条记录。但实际项目里这张表可能被多个接口、多个服务甚至批处理脚本操作。你今天在写更新的地方加了日志明天某个同事在另一个导出流程里直接改了数据日志又没了。而且很多老系统根本没有统一的数据访问层SQL语句散落在存储过程、定时任务、报表程序里靠业务代码去补监听永远补不全。触发器不一样。它挂在表上只要这条表的insert、update、delete动作发生无论操作是从哪个应用、哪个账号、哪种工具发起的触发器都会自动执行。这个特性决定了它非常适合做“底线级别”的数据监控。1.2 触发器恰好补上了这块短板用触发器监听数据变更有几个实打实的好处不修改业务代码接口、存储过程、批处理都不用动对上游完全透明。覆盖所有入口不管是正规业务程序还是临时的手工维护脚本只要是走SQL操作的表都能被捕获。事务一致性触发器和原操作在同一个事务里操作失败回滚时审计记录也不会残留。我在实际项目里用得最多的场景就是数据审计和临时表同步。比如某张核心配置表被误改用户要求查清是哪个环节改的触发器可以把变更前后的值原样保存下来。这种需求靠应用日志还原非常痛苦靠触发器反而一查一个准。当然触发器也有不适合的场景比如用来做复杂的业务校验、跨库实时同步、高频大事务下的逐行处理。这些场景下触发器容易变成性能瓶颈后面我会专门讲避坑。2. 核心机制inserted 表和 deleted 表触发器里最重要的两张表理解触发器怎么工作关键就一句话SQL Server在执行触发器之前会先把受影响的数据分别放进两张特殊的逻辑表里一张叫inserted一张叫deleted。你在触发器里读这两张表就能拿到操作涉及的原始数据。2.1 三种操作下这两张表分别装了什么操作类型inserted 表内容deleted 表内容INSERT新插入的全部行无DELETE无被删除的全部行UPDATE更新后的新值行更新前的旧值行UPDATE这个要特别注意它不是“只放变化的列”而是把整行新值放进了inserted整行旧值放进了deleted。两行之间靠主键对应。所以你要拿“修改前的值”就去查deleted表要拿“修改后的值”就去查inserted表。这两张表不是真正的物理表它们存在于内存中生命周期就是触发器执行期间。所以你不能在另一个单独事件里去访问它们只能在触发器内部通过SELECT读取。这一点和普通临时表不同。2.2 多行操作时别忘了它们是表这是新手最容易忽略的地方。假设一条UPDATE语句同时更新了100行那么inserted和deleted里就各有100行数据。触发器里的逻辑必须按“集合处理”的方式来写不能默认只有一行。很多初学者在触发器里写“SELECT UserName UserName FROM inserted”如果inserted里有多行这个变量只会拿到最后一行或者随机一行结果就是审计记录严重丢失。这种坑我见得太多后面讲性能问题时还会再提。2.3 触发器语法速览看一段最基础的结构CREATE TRIGGER 触发器名称 ON 表名 AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; -- 在这里写逻辑 END;AFTER表示在操作完成之后执行INSERT, UPDATE, DELETE表示监听哪几种操作。如果只想监听一种就写一个想都监听就写全。另外还有INSTEAD OF触发器它会在操作发生前“替换”掉原来的操作适合做视图写入或复杂拦截但日常审计监听基本用AFTER就够了。3. 三个触发器示例INSERT、UPDATE、DELETE 逐个落地接下来进入正题我把三种操作拆开写一遍。为了统一演示先准备一张用户表和一张审计日志表。3.1 需要准备的表结构-- 业务用户表 CREATE TABLE dbo.Users ( UserId INT IDENTITY(1,1) PRIMARY KEY, UserName NVARCHAR(50) NOT NULL, Email NVARCHAR(100) NULL, Status TINYINT NOT NULL DEFAULT 1, CreatedAt DATETIME NOT NULL DEFAULT GETDATE(), UpdatedAt DATETIME NULL ); GO -- 审计日志表 CREATE TABLE dbo.UsersAudit ( AuditId BIGINT IDENTITY(1,1) PRIMARY KEY, UserId INT NOT NULL, UserName NVARCHAR(50) NULL, Email NVARCHAR(100) NULL, Status TINYINT NULL, ActionType NVARCHAR(10) NOT NULL, OldValue NVARCHAR(500) NULL, NewValue NVARCHAR(500) NULL, ChangedAt DATETIME NOT NULL DEFAULT GETDATE(), ChangedBy NVARCHAR(128) NULL DEFAULT SUSER_SNAME() ); GOOldValue和NewValue是审计的核心我习惯把操作前后值得关注的内容拼成一个文本字段方便直接查看。明细字段也保留了这样需要排查时可以按字段条件去查。3.2 INSERT 监听触发器CREATE TRIGGER trg_Users_Insert ON dbo.Users AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.UsersAudit( UserId, UserName, Email, Status, ActionType, OldValue, NewValue ) SELECT UserId, UserName, Email, Status, INSERT, NULL, UserId CONVERT(NVARCHAR(20), UserId) , UserName ISNULL(UserName, ) , Email ISNULL(Email, ) , Status CONVERT(NVARCHAR(10), Status) FROM inserted; END; GO这个触发器执行后往Users表插入任意数据UsersAudit里就会出现对应记录ActionType为INSERTNewValue里拼接了关键字段的值。OldValue为空因为插入前本来就没有旧值。我这里用ISNULL来处理可空字段避免拼接出来一大串NULL字符串。这是审计记录里非常实用的细节。3.3 UPDATE 监听触发器CREATE TRIGGER trg_Users_Update ON dbo.Users AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.UsersAudit( UserId, UserName, Email, Status, ActionType, OldValue, NewValue ) SELECT i.UserId, i.UserName, i.Email, i.Status, UPDATE, UserName ISNULL(d.UserName, ) , Email ISNULL(d.Email, ) , Status CONVERT(NVARCHAR(10), d.Status), UserName ISNULL(i.UserName, ) , Email ISNULL(i.Email, ) , Status CONVERT(NVARCHAR(10), i.Status) FROM inserted i INNER JOIN deleted d ON i.UserId d.UserId; END; GOUPDATE触发器里新旧数据通过主键关联。这里用INNER JOIN来保证每一行更新后的记录能匹配到更新前的旧值。执行一条更新语句比如UPDATE dbo.Users SET Email newmailexample.com, UpdatedAt GETDATE() WHERE UserId 1;审计表里就会多出一条记录OldValue和NewValue分别保存修改前后的关键信息ChangeBy默认填入执行登录名。这个示例已经足够应付大部分业务表的数据追踪需求。如果你想详细记录每个字段的逐个对比可以在触发器里用UNPIVOT或者逐列比较但实际项目中用拼接文本保存前后摘要可读性和存储效率反而更好。3.4 DELETE 监听触发器CREATE TRIGGER trg_Users_Delete ON dbo.Users AFTER DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.UsersAudit( UserId, UserName, Email, Status, ActionType, OldValue, NewValue ) SELECT UserId, UserName, Email, Status, DELETE, UserId CONVERT(NVARCHAR(20), UserId) , UserName ISNULL(UserName, ) , Email ISNULL(Email, ) , Status CONVERT(NVARCHAR(10), Status), NULL FROM deleted; END; GO删除操作没有新值所以NewValue为空。OldValue里保存了删除前完整的关键信息。这个触发器相当于给删除操作留了一条“后悔药”记录数据即使被物理删除审计表里还能查到当时的内容。有了这三个触发器Users表的inset、update、delete都会被完整记录下来。但如果你要维护三套触发器麻烦也随之而来每张业务表都要建三个对象改动审计逻辑要同步改三处。下一步我来展示一个更实用的合并写法。4. 一个触发器监听三种操作INSERT、UPDATE、DELETE 合并写法把三种操作写进同一个触发器逻辑上完全可行而且维护成本更低。前提是你得先判断当前触发到底是由哪种操作引起的。4.1 根据 inserted / deleted 判断操作类型有一个简单可靠的方法看这两张表里是否有数据。inserted有数据、deleted无数据INSERTinserted无数据、deleted有数据DELETEinserted有数据、deleted也有数据UPDATE这个判断思路非常好记也完全符合SQL Server的行为机制。你不需要依赖其他系统变量或函数只需要两个COUNT查询。4.2 合并触发器完整代码下面这个统一触发器监听Users表的三种操作并根据操作类型写入不同的审计记录。CREATE TRIGGER trg_Users_Audit ON dbo.Users AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; DECLARE ActionType NVARCHAR(10); DECLARE InsertCount INT (SELECT COUNT(*) FROM inserted); DECLARE DeleteCount INT (SELECT COUNT(*) FROM deleted); IF InsertCount 0 AND DeleteCount 0 SET ActionType UPDATE; ELSE IF InsertCount 0 SET ActionType INSERT; ELSE IF DeleteCount 0 SET ActionType DELETE; ELSE RETURN; IF ActionType INSERT BEGIN INSERT INTO dbo.UsersAudit( UserId, UserName, Email, Status, ActionType, OldValue, NewValue ) SELECT UserId, UserName, Email, Status, INSERT, NULL, UserId CONVERT(NVARCHAR(20), UserId) , UserName ISNULL(UserName, ) , Email ISNULL(Email, ) , Status CONVERT(NVARCHAR(10), Status) FROM inserted; END ELSE IF ActionType UPDATE BEGIN INSERT INTO dbo.UsersAudit( UserId, UserName, Email, Status, ActionType, OldValue, NewValue ) SELECT i.UserId, i.UserName, i.Email, i.Status, UPDATE, UserName ISNULL(d.UserName, ) , Email ISNULL(d.Email, ) , Status CONVERT(NVARCHAR(10), d.Status), UserName ISNULL(i.UserName, ) , Email ISNULL(i.Email, ) , Status CONVERT(NVARCHAR(10), i.Status) FROM inserted i INNER JOIN deleted d ON i.UserId d.UserId; END ELSE IF ActionType DELETE BEGIN INSERT INTO dbo.UsersAudit( UserId, UserName, Email, Status, ActionType, OldValue, NewValue ) SELECT UserId, UserName, Email, Status, DELETE, UserId CONVERT(NVARCHAR(20), UserId) , UserName ISNULL(UserName, ) , Email ISNULL(Email, ) , Status CONVERT(NVARCHAR(10), Status), NULL FROM deleted; END END; GO这个写法把原来三个触发器合并成了一个对象。以后要调整审计逻辑比如加上一个字段、改一下日志格式只需修改这一处就可以了。4.3 合并写法为什么实用我在实际项目里维护过几十张表的审计触发器如果每张表都拆三个单独触发器对象数量会膨胀到难以管理。合并成一个之后整个数据库的触发器数量直接降到三分之一排查问题时也方便打开表下的触发器列表看到trg_Users_Audit就明白这张表的审计规则在哪里。另外由于操作类型判断逻辑集中在触发器开头逻辑非常直白后来接手的同事也能快速看懂不会因为触发器文件散落各处而忽略某个变更。5. 触发器真正的考验多行批量操作和性能损耗到这里基本的监听示例已经能跑了。但我知道肯定有读者会担心触发器会不会拖慢业务多行数据一起更新时会不会出问题这两个问题确实是触发器能否上生产环境的关键。5.1 错误示范游标逐行处理最典型的反模式就是在触发器里写游标逐行把inserted里的数据拿出来再一条条插入日志。这样做确实最容易理解-- 反模式示例千万别这么写 DECLARE cur CURSOR FOR SELECT UserId, UserName FROM inserted; DECLARE UserId INT, UserName NVARCHAR(50); OPEN cur; FETCH NEXT FROM cur INTO UserId, UserName; WHILE FETCH_STATUS 0 BEGIN INSERT INTO dbo.UsersAudit(UserId, UserName, ActionType) VALUES (UserId, UserName, INSERT); FETCH NEXT FROM cur INTO UserId, UserName; END CLOSE cur; DEALLOCATE cur;一旦inserted里有几千行这个游标就要循环几千次每次循环都涉及额外的上下文切换和日志写入性能极差。更重要的是大量使用游标还会占用更多锁和内存影响同一表上的其他事务。5.2 正确的集合处理思路触发器内部也应该坚持集合操作直接把inserted或deleted整张表作为数据源用一条INSERT...SELECT完成全部写入。前面示例里我用的就是这种写法。SQL Server对这种基于集合的写入有很好的优化几千行的操作也可以很快完成。如果你确实需要针对每行做复杂计算优先考虑能否用窗口函数、CASE表达式或者JOIN来解决而不是一上来就游标。真到了必须逐行处理的场景也要确保循环次数可控并限制在特定的低数据量操作上。5.3 触发器内的事务与锁触发器在原操作所在的事务里运行。这意味着如果触发器里执行了很耗时的操作整个事务的锁持续时间会变长可能阻塞其他会话对同一张表的访问。所以触发器内部尽量不要做以下事情调用存储过程去处理无关的数据等待外部资源比如调用HTTP接口SQL Server不直接支持但有些人会通过扩展存储过程或CLR来做在触发器内做大批量、复杂的跨表更新我的经验是触发器里只做“轻量”的日志写入而且最好让日志表不参与原表的复杂外键关系。审计日志可以允许稍微延迟不需要和主业务表强一致到毫秒级别这样能减少锁竞争。6. 我实际踩过的触发器相关的坑光看示例可能觉得自己会了但真正放到生产环境有几个问题会让你排查到怀疑人生。我挨个说一下我碰到的真实情况和解决思路。6.1 同一个字段执行“无变化更新”触发器照样触发这是最隐蔽的一个问题。假设你执行UPDATE dbo.Users SET Email Email WHERE UserId 1;这条SQL对数据没有实质改变但触发器里的UPDATE分支依然会执行。deleted和inserted中对应行的Email字段看起来完全一样你的审计表里会多出一堆“假变更”记录时间久了日志表全是垃圾数据。解决思路是加一层比较判断。如果只监听特定列的变化可以用UPDATE(column)函数。比如IF UPDATE(Email) OR UPDATE(UserName) BEGIN -- 审计逻辑 END;注意UPDATE(column)返回的是“该列是否出现在UPDATE语句的SET子句中”而不是“该列值是否真的变了”。所以即使SET Email EmailUPDATE(Email)也会返回1。要过滤真正的值变化还是得在连接inserted和deleted后逐列比较。实际项目中我通常会在UPDATE分支里写类似这样的过滤WHERE ISNULL(i.Email, ) ISNULL(d.Email, ) OR ISNULL(i.UserName, ) ISNULL(d.UserName, ) OR i.Status d.Status;这样就能把“改了等于白改”的语句过滤掉审计日志准确很多。6.2 禁用触发器时忘了检查依赖的级联有时候为了做批量数据修复DBA或者开发会习惯性地执行DISABLE TRIGGER trg_Users_Audit ON dbo.Users;然后跑完更新再重新启用。听起来没什么问题但风险在于如果这个表上有多个触发器其中一个禁用了但另一个还在正常运作而它又依赖于前面触发器写入的数据就会出现逻辑不一致。另外SQL Server的触发器还有嵌套和递归行为。比如你的表发了DELETEDELETE触发器里又往另一个历史表做INSERT而那个历史表恰好也有一个INSERT触发器它又会跑一段逻辑。默认情况下SQL Server允许触发器嵌套但过深的嵌套会导致性能下降和死锁风险。这里给两个实际建议批处理脚本里禁用触发器后务必在finally风格的代码块里重新启用或者至少确保脚本执行完手动检查。如果遇到触发器递归调用导致死循环可以在数据库级关闭RECURSIVE_TRIGGERS或者用TRIGGER_NESTLEVEL()函数在触发器内部判断嵌套层级超过两层直接RETURN。6.3 审计值拼接把NULL给拼丢了在触发器里拼接字符串时我一开始很随意直接写Email Email结果Email字段是NULL的时候整条拼接结果直接变成NULL。审计表里可能出现这么一条记录ActionType正常但NewValue文本变成NULL完全没法看。后来所有可空字段的拼接都改成ISNULL包裹前面示例里也是这样处理的。这个教训很简单但排查起来很费时间因为有时候你只一眼扫过去看不见明细字段和拼接文本实际内容不一致。6.4 UPDATE()函数与字段判断的坑刚才提到UPDATE(column)只能判断“SET中有没有这个字段”不能判断值是否变化。有一种场景很容易被误导业务上做了用户名修改但某次操作只是先UPDATE这一行时间去刷新缓存SET里根本不含UserName可是表面上看数据确实变了。结果审计日志没有记录这次修改用户却坚持说他们改了。这种情况确实存在尤其在多系统并发维护的数据里。要彻底规避这个坑我建议把触发器审计当成“操作审计”而不是“字段变化审计”只要这条更新语句涉及了业务关键字段就记录下来如果想判断值真的变了再做值对比。两个维度分开处理才能获得符合预期的效果。7. 如果你只想用触发器做审计最后几点建议如果你正准备在现有表上添加触发器做监听我建议先从小范围开始验证再逐步铺开避免一次改太多表出问题时收不住。可以先找一张非核心、但经常有数据变更的表比如字典类或配置类的表按上面的示例把审计触发器搭好。跑几天数据确认审计记录正常、不影响业务性能再扩展到真正需要做数据追踪的核心业务表。关于审计日志表本身我也有一点经验按时间增加合适的索引比如在ChangedAt和UserId上建非聚集索引。因为审计数据只会越积越多查询时按操作时间和业务ID过滤是常态没有索引的话久了之后翻历史记录会非常慢。还有一个容易被忽略的问题审计表里的ChangedBy默认是SQL登录名。实际业务系统如果用的是连接池登录名常常是同一个服务账号这时候只能知道是应用自己改的不知道具体是哪个用户在页面上操作的。想解决这个问题就得在应用层把用户信息写入会话上下文比如使用sp_set_session_context设置识别字符串然后在触发器里通过SESSION_CONTEXT()读取。这个做法比直接解析字符串更规范也适合存在多套业务共用同一个数据库登录名的情况。触发器这东西用好了就是数据安全的最后一道防线用不好就是性能隐患和无尽麻烦。我的体会是别迷信它也别盲目排斥它。对于监听insert、update、delete这类需求它依旧是一个非常直接、覆盖全面的可靠方案。找一个非核心业务表先做实验把你自己的完整示例搭起来跑一跑比空谈理论更能理解那些细枝末节的坑。