SqlServer存储过程与触发器实战：构建高可用数据审计系统

去年5月，我接手一个电商平台的审计系统重构项目——用户投诉订单数据被篡改，但现有日志只能记录操作时间，根本查不出是谁在什么时间改了哪个字段。传统方案要么用第三方审计工具（成本高），要么靠应用层打日志（容易被绕过），最后决定用SqlServer的存储过程+触发器硬刚——毕竟干了12年电商运营，数据库层的东西最可控。

存储过程的核心是“封装审计逻辑”。比如订单表（Orders）的更新操作，我写了个叫`usp_AuditOrderChange`的存储过程，参数直接传`@OldData XML`和`@NewData XML`——把修改前后的整行数据转成XML格式塞进去，这样不管哪个字段被改，都能在审计表（OrderAuditLog）里完整记录。测试时发现个坑：XML字段最大能存2GB，但实际单次更新可能只改几个字段，用`CAST(@OldData AS NVARCHAR(MAX))`反而更稳——别问为什么，实测发现XML解析在某些版本有性能问题，直接存字符串更靠谱。

触发器的“坑”比存储过程多得多。最初我在`AFTER UPDATE`触发器里直接写`INSERT INTO OrderAuditLog(...) SELECT ... FROM inserted`，结果发现如果批量更新1000条订单，触发器会触发1000次，每次插一条记录——这哪受得了？后来改成用表变量临时存储`inserted`和`deleted`的数据，在触发器里循环处理，把1000次插入合并成1次批量插入，性能直接提升30倍——测试数据是5万条订单的批量更新，原方案耗时47秒，优化后只要1.5秒。

但最头疼的是“递归触发”。有次测试时发现，更新订单后，审计表里居然有两条记录——一条是订单更新的触发器插的，另一条是审计表自身有`AFTER INSERT`触发器，又触发了一次！这循环下去不得炸？最后在触发器开头加了个`IF UPDATE(AuditFlag)`判断——给审计表加个`AuditFlag`字段，默认0，触发器里先检查这个字段，如果是1就不再执行，避免递归。这招是看SqlServer官方文档里的“嵌套触发器”章节想到的，文档里还提醒“最多32层嵌套”，但实际根本不敢用到那么深。

文章配图，仅供参考

新技术的好处是“可控性强”。比如审计日志里要记录操作人，应用层传的是用户ID，但存储过程里可以再加一层校验——通过`SUSER_NAME()`获取当前登录的SqlServer账号，和传入的用户ID做交叉验证，防止应用层伪造用户ID。去年11月还遇到个安全漏洞，黑客通过应用接口直接调用存储过程（绕过应用层权限检查），结果因为存储过程里加了`IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID=@UserID AND IsActive=1) RETURN`，直接拦截了非法调用——这要是靠应用层打日志，根本防不住。

现在这套系统跑了8个月，审计表已经攒了2.3亿条记录——用存储过程+触发器的方案，比第三方工具便宜70%，比应用层打日志可靠3倍（应用层打日志可能因为异常没执行，但触发器只要事务提交就必然执行）。不过也有局限——比如审计表太大后，查询变慢，后来加了个`@RetentionDays`参数，存储过程里自动删30天前的数据，用`WHERE CreateTime < DATEADD(DAY, -@RetentionDays, GETDATE())`，每天凌晨跑一次，目前没出现过性能问题。

下一步打算把审计日志和ElasticSearch打通——毕竟2.3亿条记录，直接查SqlServer太慢，用ES做全文检索，存储过程里插数据时同步写到ES集群。不过这得改存储过程的代码，加`OPENQUERY`调ES的API，或者用SqlServer的Service Broker异步推送——还没想好哪种更稳，先在小环境测测看。

（编辑：站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!