SqlServer审计系统：存储过程与触发器实战
|
去年七月，我接手了一个SqlServer审计系统的重构项目——客户要求记录所有数据变更，且必须精确到字段级，连修改前的值都得存下来。传统方案是用变更数据捕获（CDC），但客户说CDC太吃资源，他们那台老服务器跑不动。于是我把目光转向存储过程和触发器——这玩意儿虽然老，但轻量啊，尤其适合这种“小而美”的审计需求。
文章配图，仅供参考 先说存储过程。我写了个叫`sp_AuditDataChange`的存储过程，参数是表名、主键值、操作类型（INSERT/UPDATE/DELETE），内部用动态SQL拼接出变更前后的数据。测试时发现个坑——如果表里有`NVARCHAR(MAX)`类型的字段，直接拼接会报错，得用`QUOTENAME()`函数转义。最后用了个笨办法：先查`INFORMATION_SCHEMA.COLUMNS`获取所有列名，再循环处理，非文本类型直接拼，文本类型用`CAST(COL AS NVARCHAR(MAX))`转一下。实测下来，单次调用耗时从300ms降到80ms，客户那台4核8G的服务器能扛住每秒200次的变更审计。触发器更刺激——我直接在目标表上建了`AFTER INSERT, UPDATE, DELETE`触发器，调用上面的存储过程。但第一次部署就翻车了：有个表有10个触发器（别问我为啥，历史遗留问题），新加的审计触发器一执行，直接超时。查日志发现是触发器链太长，SqlServer默认触发器嵌套深度是32层，这表已经占了10层，再加个审计触发器就快到极限了。最后解决办法是：把其他触发器能合并的合并，不能合并的改成存储过程调用，审计触发器单独放最后——这才没超时。 新技术？对，这就是新技术——别觉得存储过程和触发器是老古董，在特定场景下，它们比CDC、变更跟踪这些“新玩意儿”更实用。比如CDC需要开启数据库级别的跟踪，还得配置分发服务器，而触发器直接在表上生效，部署简单；变更跟踪只能记录“谁改了什么”，改前的值得自己查，触发器+存储过程能直接存下来。去年七月那次重构，客户用了一个月，反馈说“比CDC省了60%的CPU资源”，这算不算新技术？ 当然，触发器也有坑。有次客户说审计数据不全，查了半天发现是有个表有`INSTEAD OF`触发器——这玩意儿会拦截原操作，直接执行自己的逻辑，导致`AFTER`触发器不触发。最后只能把审计逻辑塞到`INSTEAD OF`触发器里，还得处理原操作的逻辑，代码复杂度直接翻倍。所以用触发器前，得先查清楚表上有没有其他触发器，尤其是`INSTEAD OF`这种“隐藏炸弹”。 下一步我打算试试把审计数据存到JSON字段里——SqlServer 2016开始支持JSON，用`FOR JSON PATH`能把变更前后的数据转成JSON字符串，比存多个字段更灵活。比如客户现在要求记录“谁在什么时间改了什么字段，从什么值改成什么值”，用JSON的话，一条记录就能存完，查的时候用`JSON_VALUE`提取就行。不过得先测性能——JSON操作会不会比直接存字段慢？如果慢太多，可能还是得用老办法。 （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长学院：SQL Server存储过程与触发器实战精讲