SqlServer存储过程与触发器实战：零基础构建高可用数据审计系统
|
去年十二月，我接到一个紧急需求——某金融客户的核心业务系统需要实现全表级数据变更审计，要求记录每条数据的修改时间、操作人、旧值新值，且不能影响现有业务性能。传统方案要么用应用层日志（容易被绕过），要么用CDC（需要额外许可），最后我们选了SqlServer存储过程+触发器——零依赖、零成本，两周就上线了。 触发器是这场实战的“钩子”。比如对客户信息表（CustomerInfo）的UPDATE操作，我们建了个AFTER UPDATE触发器，它会在每次修改后自动调用存储过程。这里有个关键细节：触发器里必须用INSERTED和DELETED虚拟表，这两个表的结构和原表一致，分别存新数据和旧数据。我曾见过有人直接SELECT FROM CustomerInfo，结果只拿到当前数据，旧值全丢——这审计还审个啥？ 存储过程才是核心“加工厂”。我们写了个叫[Audit_InsertChangeLog]的存储过程，参数是@TableName、@OperationType（INSERT/UPDATE/DELETE）、@ModifiedBy（从SESSION_USER取），然后通过动态SQL拼接INSERT语句，把INSERTED/DELETED里的数据塞进审计表（ChangeLog）。这里有个坑：动态SQL里表名必须用QUOTENAME函数包裹，不然遇到带空格或特殊字符的表名会报错——我测试时用[Order Detail]表卡了半小时，最后发现是少了这对方括号。 性能优化才是硬仗。客户系统每天有20万次数据变更，直接触发器+存储过程会导致事务阻塞。我们的解法是：在触发器里只做最轻量的操作——把变更数据存进一个临时表（#TempAudit），然后每5分钟用存储过程批量处理临时表数据到正式审计表。测试时发现，这样处理后，主业务事务的平均等待时间从120ms降到15ms——这数据，客户CTO看了直接拍板全量上线。 但失败案例也扎心。有个同行用类似方案做审计，结果审计表没建索引，三个月后表涨到1亿行，查询变更记录直接超时。我们的做法是：按日期分区表（Partition by CreateDate），每天凌晨用存储过程自动创建新分区，旧分区归档到单独文件组。去年双十一峰值时，审计表日增500万行，查询最近7天数据仍能在2秒内返回——这性能，新技术（比如CDC）都不一定做得到。
文章配图，仅供参考 主观判断：SqlServer触发器+存储过程的组合，在数据审计场景下比很多“新技术”更实在。它不需要额外许可（CDC要企业版）、不依赖应用层代码（避免被绕过）、性能可优化（通过批量处理+分区表），尤其适合传统行业那些“不能停、不能改”的核心系统。去年十二月那个项目，客户原本打算花50万买第三方审计工具，最后用我们的方案省了这笔钱——这，才是真正的“降本增效”。当然，这方案也有局限——比如触发器会增加事务开销，对高并发写入场景（每秒万级以上）可能不适用。下一步我打算研究下：能不能用Service Broker把审计操作异步化，进一步降低对主业务的影响？有相关经验的兄弟，欢迎私信交流——这坑，我一个人踩就够了。 （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长学院：SQL Server存储过程与触发器高可用实战