SQL Server数据审计系统：存储过程与触发器实战
|
去年一月份，我主导开发了一套基于SQL Server的数据审计系统——核心模块全靠存储过程和触发器撑着。当时甲方要求记录所有订单表的变更，包括修改前后的值、操作人、时间戳，还得能按字段筛选历史记录。传统做法是写应用层日志，但客户明确要数据库原生方案——毕竟他们有200+个分布式应用，改代码成本太高。 存储过程的设计花了三周——光是参数校验就写了12个条件判断。比如有个`audit_order_update`过程，接收`@old_values XML`和`@new_values XML`参数，用`OPENXML`解析后逐字段比对。最坑的是日期类型，SQL Server的`CONVERT`函数在不同版本里行为不一致，测试时发现2016版和2019版对`datetime2`的格式化结果差了3毫秒——这直接导致审计日志里的时间戳对不上业务系统，最后不得不统一用`SYSDATETIMEOFFSET()`取时间。
文章配图，仅供参考 触发器比存储过程更邪乎——我在`ORDER`表上挂了`AFTER UPDATE`触发器，结果第一次部署就炸了。问题出在递归触发上——触发器里更新审计表时，又触发了审计表的`AFTER UPDATE`，导致无限循环。后来在触发器开头加了`IF UPDATE(audit_flag)`直接返回，才切断递归链。这还没完，有次客户批量更新10万条订单，触发器直接把CPU干到100%，后来改成异步审计——用`SERVICE BROKER`把变更事件推到队列，再由后台进程处理，性能才稳住。新技术？存储过程和触发器可不是啥新玩意儿——但用它们做审计，确实比ETL工具或变更数据捕获（CDC）更轻量。我测过，对1000万行表的DML操作，触发器方案的延迟比CDC低40%，存储过程写的审计日志比应用层日志少占30%空间。最爽的是权限控制——审计表的访问权限可以完全隔离，连DBA都看不到敏感字段的原值，只能通过存储过程提供的`get_audit_detail`函数查询，参数还得带操作人ID的数字签名。 当然也有翻车的时候——有次客户误删了触发器，导致3小时的变更没记录。后来我在系统表`sys.triggers`上加了DDL触发器，谁敢删审计相关的触发器，直接发邮件给DBA组。还有个隐藏坑：`INSTEAD OF`触发器在视图上用时，如果视图包含`WITH CHECK OPTION`，修改数据会报错——这问题连微软官方文档都没明说，我是在Stack Overflow上扒了20页才找到解决方案。 下一步打算把这套方案迁移到Azure SQL Database——但触发器在托管实例里的行为和本地版有点差异，比如`CONTEXT_INFO`的持久性不同。另外，存储过程里的临时表在并行查询下可能会出问题，得用`TABLOCK`提示锁住。说到底，存储过程和触发器做审计，拼的是对SQL Server内部机制的熟悉程度——光会写CRUD可不够，得把执行计划、事务隔离、系统目录视图这些底层东西摸透。 （编辑：91站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
