为了在数据库结构发生变动而出现问题时，能够跟踪问题，定位问题的根源，我们可以利用DDL触发器来记录类似“用户建立表”这种变化的操作，这样可以大大减轻跟踪和定位数据库模式的变化的繁琐程度。
1、DDL触发器介绍
DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。
使用 DDL 触发器，可以达到以下几种目的：
要防止对数据库架构进行某些更改。 希望数据库中发生某种情况以响应数据库架构中的更改。 要记录数据库架构中的更改或事件。
与标准的DML触发器一样，DDL 触发器在响应事件时执行存储过程。 但与标准的DML触发器不同的是，它们并不在响应对表或视图的 UPDATE、INSERT 或 DELETE 语句时执行存储过程。 它们主要在响应数据定义语言 (DDL) 语句执行存储过程。 这些语句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等语句。 执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。
2、如何使用DDL触发器
第一步，需要建立一个表，用来记录数据库范围内所有DDL操作。
下面的代码在AdventureWorks范例数据库中创建一个表，用于保存所有DDL操作记录：
USE AdventureWorks
GO
CREATE TABLE AuditLog
(ID INT PRIMARY KEY IDENTITY(1,1),
Command NVARCHAR(1000),
PostTime NVARCHAR(24),
HostName NVARCHAR(100),
LoginName NVARCHAR(100)
)
GO用于保存DDL事件的表在创建好之后，还需要建立一个DDL触发器，监控AdventureWorks数据库中DDL_DATABASE_LEVEL_EVENTS级别的所有事件：
CREATE TRIGGER Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'NVARCHAR(1000)')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)', 'NVARCHAR(24)')
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)', 'nvarchar(6)')
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)',
'NVARCHAR(100)')
SET @hostname = HOST_NAME()
INSERT INTO dbo.AuditLog(Command, PostTime,HostName,LoginName)
VALUES(@cmd, @posttime, @hostname, @loginname)
GO共2页。 1 2 :
使用 EVENTDATA 函数，可以捕获有关激发 DDL 触发器的事件的信息，并将其保存到我们的AuditLog日志表中。EVENTDATA 函数的是返回 xml 值，采用以下的命令进行解析：
SET@cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))需要LTRIM和RTRIM是所有的左边的前导空格和右边的尾随空格，而REPLACE函数是用来消除使用SSMS脚本向导时所带来的回车。
一旦建立了表和触发器，您就可以用以下的操作，来测试看看DDL触发器是否正常运行：
UPDATE STATISTICS Production.Product
GO
CREATE TABLE dbo.Test(col INT)
GO
DROP TABLE dbo.Test
GO
-- View log table
SELECT *
FROM bo.AuditLog
GO执行后，查询结果如下所示：
图1: AuditLog日志表查询结果
3、小结
通过创建一个日志表来保存所有DDL操作以及创建数据库级别的DDL触发器，我们能够成功地捕获我们的数据库中所有DDL级的变化，为DBA跟踪和监视任何改变提供了更强大的功能。
