编程语言研究书库
SQL Server XML 与 JSON 实例

内建 XML(上)— DDL 触发器与 EVENTDATA() 的审计流水线

内建 XML(上)— DDL 触发器与 EVENTDATA() 的审计流水线

这一章讲什么: 前 10 章把 XML/JSON 当「外来数据」处理;原书第 11 章(全章 8 万字符, 我们拆三篇)调转镜头:SQL Server 自己就是 XML 的头号用户——DDL 触发器的事件、 扩展事件的输出、执行计划、SSIS 包,全是 XML。读懂这些内部 XML,等于拿到数据库的 体检报告阅读权。本篇拆前两个配方:DDL 触发器审计。

1. 先看现象:谁在什么时候改了数据库?

解决什么问题: 表被删了、登录被建了、索引被改了——谁干的、什么时候、执行了什么命令?没有审计,这些问题的答案在生产事故里是「查无此事」。

SQL Server 的答案分两层,书里用一张对照表钉死了它们的边界1:

数据库级触发器服务器级触发器
作用域单个数据库整个实例(所有库)
建在哪各库自己的 Programmability → Database Triggersmaster 库,Server Objects → Triggers
创建语法ON DATABASEON ALL SERVER
典型场景本库的 schema 治理、审计、保护实例级的安全与合规(登录、端点)
XML 来源都是 EVENTDATA()同左

审计流水线的形状(两个级别完全同构):

  DDL 事件发生(建表/删登录/改索引…)
      │
      ▼  触发器同步触发(在 DDL 事务内,报错可回滚整个 DDL)
  EVENTDATA() 返回描述事件的 XML
      │
      ▼  @Event.nodes('EVENT_INSTANCE') + value() 逐个取字段
  写入中央日志表(DBAUtility.dbo.SysChangeLog)
      │
      ▼
  谁、什么时候、对什么对象、执行了什么 SQL

  图说:触发器是钩子,EVENTDATA 是事件报告,日志表是档案柜。

2. 核心原理

2.1 服务器级触发器:实例大门的门禁

怎么做,四步(原书 Listing 11-1):① 建一个中央的 DBAUtility 库和 SysChangeLog 日志表(列含 EventType、ObjectName、LoginName、SqlCommand、EventDataXML 等,登录名默认取 SYSTEM_USER)2;② 建触发器:

CREATE OR ALTER TRIGGER [tr_ServerDDL_Audit]
ON ALL SERVER          -- 作用域:整个实例
FOR DDL_SERVER_LEVEL_EVENTS   -- 事件组:服务器级 DDL 的并集
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Event XML = EVENTDATA();
  INSERT INTO [DBAUtility].[dbo].[SysChangeLog] (…)
  SELECT … FROM @Event.nodes('EVENT_INSTANCE') AS T(C);
END;

③ 用 ENABLE/DISABLE TRIGGER … ON ALL SERVER 开关;④ 在 sys.server_triggers 里核验3。

事件与事件组: 可以挂单个事件(CREATE_LOGIN、DROP_DATABASE…),也可以挂事件组——DDL_SERVER_LEVEL_EVENTS 是超集,涵盖绝大多数服务器级 DDL;常用的还有 DDL_LOGIN_EVENTS、DDL_DATABASE_EVENTS、DDL_SERVER_SECURITY_EVENTS、DDL_ENDPOINT_EVENTS4。

触发器的关键性格(书里列了四条): 建在 master、ON ALL SERVER 声明作用域、同步触发(作为 DDL 事务的一部分——触发器里 ROLLBACK,原 DDL 一起回滚,这就是「阻止」的机制)、以触发器所有者的安全上下文执行5。正因为它在事务里同步跑,触发器体必须瘦——书里建议日志表用独立数据库、生产环境谨慎启用(注释里原话)6。

2.2 EVENTDATA():整个方案的心脏

它是什么: 每次 DDL 事件触发时,EVENTDATA() 返回一份描述该事件的 XML——事件类型、时间、SPID、服务器名、登录名、对象名、执行命令全文,都在里面7。

它的一个关键性质:结构随事件类型而变。 Create Login 的 XML 和 Create Database 的 XML,元素不一样(登录有 DefaultLanguage、SID;数据库有 DatabaseName)——书里特意并排列出两份文档对照8。所以解析代码要取并集:c.value('DefaultLanguage[1]', …) 对数据库事件就是 NULL,无害。

解析,两种风格: 书里的 Listing 11-2 用 nodes('EVENT_INSTANCE') 拆行后逐列 value();Listing 11-3 用 @Event.value('(/EVENT_INSTANCE/LoginName)[1]', …) 单值直取。性能上没有差别——EVENTDATA 的负载本质上是单行报告,怎么读都行,选可读性好的9。

2.3 数据库级触发器:本库的门禁,以及批量部署

和服务器级同构,差别只在三处:ON DATABASE、DDL_DATABASE_LEVEL_EVENTS(或单个事件)、建在每个要监控的库里10。它管的事也更「身边」:表、视图、存储过程、函数、索引、schema 的增删改,CREATE USER、ALTER ROLE 这类库内安全变更11。

书里的实用增强:一个脚本生成所有库的触发器。 用游标(或直接查询 sys.databases)拼出每个库的 USE 库名 GO CREATE TRIGGER … 文本(触发器体里的事件解析代码要把单引号写成两个单引号),输出后整批执行;只监控用户库就 WHERE database_id > 4 AND name != 'DBAUtility'12。

2.4 为什么这一切是 XML 的胜利

把这套流水线换成本书前半部分的词汇,它的每个零件都熟:EVENTDATA 是类型化 XML(可以直接 .nodes()/.value()),nodes+value 是拆单文档,日志表多了一列 EventDataXML 把原始 XML 存档(解析代码取列替代取变量即可)13。换句话说:第 04 章学的 shredding,在这里不是「处理外来数据」,而是读数据库自己写下的操作日志——这是本章开篇那句话的实证:「理解 SQL Server 内部过程,今天需要 XML 和 JSON 的流利读写」。

3. 作者的判断与证据

产品行为级事实(有官方文档背书): 两级触发器的差异表、触发器四特性、事件组清单、EVENTDATA 结构随事件而变——这些书里都有界面截图或官方链接(DDL 事件清单链到 learn.microsoft.com)45。

作者的操作建议(经验型): 日志表放独立库、触发器体保持精简、生产启用要谨慎6;「行内 XML 解析在别处是性能隐患,但 EVENTDATA 是单行负载,无妨」9;批量部署脚本值得收藏12。

治理主张(价值判断): 「两级触发器叠加 = 分层治理模型:实例级集中执法 + 库级精准管控」14——这是本章小结的立场,合理,但前提是触发器性能开销受控;书里没有量化「多胖算胖」的触发器体,实战要自己测(补充判断,不在书里)。

4. 边界与局限

  • 同步触发的代价:DDL 事务要等触发器跑完——触发器体里的插入要是卡住,所有人的 DDL 都卡住。审计表独立库正是为了隔离。
  • EVENTDATA 元素的完整性:不同事件的元素并集式解析会产出一堆 NULL 列——书里明说「我的目标是帮读者简化流程,哪些列该留由你决定」9。
  • 本篇不含触发器回滚做「阻止」的完整演示(提到机制,没给配方)——真正的变更阻止(blocking trigger)还要自己写 ROLLBACK(补充,不在书里展开)。
  • DBAUtility 库名是示例约定,迁移时按环境的库名替换6。

5. 可带走的

主线一行走完: 实例谁改了 → 建 DBAUtility.SysChangeLog 日志表 → 服务器级触发器 ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS → EVENTDATA() 拿事件 XML → nodes('EVENT_INSTANCE') 拆出 EventType/LoginName/ObjectName/CommandText 写日志 → 单库场景换 ON DATABASE + DDL_DATABASE_LEVEL_EVENTS → sys.databases 拼脚本批量部署 → 两级叠加 = 分层治理。

  1. 两级触发器一张表记住:单库 ON DATABASE,全实例 ON ALL SERVER(建在 master)1。
  2. EVENTDATA() 返回事件描述的 XML,结构随事件类型而变——解析取并集,多余的列是 NULL8。
  3. 触发器同步执行:ROLLBACK 能连原 DDL 一起回滚(阻止变更的机制),所以触发器体必须瘦56。
  4. 事件组 DDL_SERVER_LEVEL_EVENTS 是超集;常用组背四个:LOGIN / DATABASE / SERVER_SECURITY / ENDPOINT4。
  5. 开关:ENABLE/DISABLE TRIGGER … ON ALL SERVER;核验:sys.server_triggers(库级在 sys.triggers)3。
  6. 批量部署:查 sys.databases 拼每个库的 CREATE TRIGGER 文本,注意触发器体里单引号要双倍12。
  7. 解析风格任选:nodes 拆行 vs 单值直取,单行负载性能无差别,选可读性9。
  8. 原始 XML 存档进日志表(EventDataXML 列),日后解析改取列即可13。

6. 原文地图

主题原书章原文位置
DDL 触发器的内部分类Chapter 11: XML and JSON As Internal SQL Server Processestext/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:34(搜「XML in SQL Server Internals」)
服务器级触发器四特性同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:163(搜「Created in the master database」)
事件组清单同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:176(搜「DDL_LOGIN_EVENTS」)
EVENTDATA 结构随事件而变同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:208(搜「varies depending on the event type」)
解析并集与风格选择同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:244(搜「every column needs to be persisted」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:264(搜「alternative approach」)
日志表与生产建议同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:100(搜「STEP 2」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:117(搜「production environment」)
数据库级触发器的范围同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:336(搜「fires in response to DDL events」)
两级对照与设计差异同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:369(搜「Server-Level vs. Database-Level」)
批量部署脚本同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:393(搜「across all user databases」)
分层治理结论同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:438(搜「layered governance model」)

Footnotes

  1. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 369–389 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:369,搜「Server-Level vs. Database-Level」)。对照表含 Scope、Created with(ON DATABASE / ON ALL SERVER)、Typical use、Events 等行;设计差异:「Server-level triggers are ideal for centralized enforcement」「Database-level triggers provide localized control, making them safer and easier to manage」。 ↩ ↩2

  2. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 93–114 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:100,搜「STEP 2」)。SysChangeLog 表的创建代码,登录名列带默认值 DEFAULT (SYSTEM_USER)。 ↩

  3. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 91–147 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:119,搜「CREATE OR ALTER TRIGGER」)。完整触发器代码;开关示例 ENABLE TRIGGER [tr_ServerDDL_Audit] ON ALL SERVER;核验 SELECT name, is_disabled… FROM sys.server_triggers 见第 146–147 段。 ↩ ↩2

  4. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 172–196 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:176,搜「DDL_LOGIN_EVENTS」)。事件组六个;「The DDL_SERVER_LEVEL_EVENTS group is a superset that includes most server-scoped DDL activity」;官方事件清单链接见第 194–196 段。 ↩ ↩2 ↩3

  5. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 158–170 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:163,搜「Created in the master database」)。四条:Created in the master database、Scoped using ON ALL SERVER、Fire synchronously as part of the DDL transaction、Execute under the security context of the trigger owner。 ↩ ↩2 ↩3

  6. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 116–117 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:117,搜「production environment」)。注释建议 DBAUtility 独立库、生产环境谨慎;「the database names used in this example are specific to the current example…substitute the appropriate database names」见第 312–316 段。 ↩ ↩2 ↩3 ↩4

  7. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 203–206 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:203,搜「foundation of all DDL triggers」)。原文:「The EVENTDATA() function serves as the foundation of all DDL triggers, providing Internal Metadata as XML that describes the event」。 ↩

  8. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 207–213 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:208,搜「varies depending on the event type」)。原文:「the XML returned by EVENTDATA() varies depending on the event type」;Create Login 与 Create Database 两份 XML 的对照见 Figure 11-2。 ↩ ↩2

  9. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 243–270 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:244,搜「every column needs to be persisted」)。原文:「My goal is simply to streamline the process for readers, leaving it to you to decide which columns are essential」;「the XML payload is effectively a single-row report. As a result, the performance impact is negligible. Choose whichever parsing style you find most readable and maintainable」见第 264–270 段。 ↩ ↩2 ↩3 ↩4

  10. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 279–333 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:329,搜「created within each monitoring database」)。原文:「A database-level DDL trigger is created within each monitoring database」;EVENTDATA 一致性:「the XML returned by EVENTDATA() uses the same elements and attributes for both server-level and database-level DDL triggers」见第 326–328 段。 ↩

  11. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 335–368 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:336,搜「fires in response to DDL events」)。数据库级事件清单:表/视图/存储过程/函数/索引/schema 的 CREATE/ALTER/DROP,CREATE USER、ALTER ROLE 等;「Unlike DML triggers, DDL triggers: Do not fire per row; Are statement-level; Execute synchronously」。 ↩

  12. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 391–431 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:393,搜「across all user databases」)。原文:「I've included a SQL script that generates CREATE TRIGGER statements across all user databases」;过滤条件 where database_id > 4 and name != 'DBAUtility';触发器体单引号双写见第 424 段。 ↩ ↩2 ↩3

  13. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 246–251 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:246,搜「retain the EventDataXML column」)。原文:「If you choose to retain the EventDataXML column in the SysChangeLog table and want to parse the XML stored there, update the FROM clause…so that it references the column instead of a variable」。 ↩ ↩2

  14. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 434–440 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:438,搜「layered governance model」)。原文:「When used alongside server-level DDL triggers, DBAs gain a layered governance model, instance-wide control at the server level, and precise enforcement at the database level」。 ↩

修改 JSON — JSON_MODIFY 一个函数打天下,以及 XML/JSON 对比实测

Previous Page

内建 XML(中)— 扩展事件、死锁图与执行计划里的 CONVERT_IMPLICIT

Next Page

On this page

内建 XML(上)— DDL 触发器与 EVENTDATA() 的审计流水线1. 先看现象:谁在什么时候改了数据库?2. 核心原理2.1 服务器级触发器:实例大门的门禁2.2 EVENTDATA():整个方案的心脏2.3 数据库级触发器:本库的门禁,以及批量部署2.4 为什么这一切是 XML 的胜利3. 作者的判断与证据4. 边界与局限5. 可带走的6. 原文地图Footnotes