一.本文所涉及的内容（Contents）
- 本文所涉及的内容（Contents）
- 背景（Contexts）
- 基础知识（Rudimentary Knowledge）
- DDL运用场景（DDL Scene）
- 补充说明（Addon）
- 疑问（Questions）
- 参考文献（References）
二.背景（Contexts）
说到触发器，大家都会想到这样的使用场景：当一个表的数据修改了，运用DML触发插入或者更新到其它表中；那DDL触发器（SQL Server 2005引入的新功能）会运用到什么场景中呢？本文将为你讲述4种运用DDL触发器的场景：
1) 禁止用户修改和删除表；
2) 禁止用户删除数据库；
3) 记录和监控某数据库所有的DDL操作；
4) 把DDL操作信息以邮件的形式主动发送通知和预警；
三.基础知识（Rudimentary Knowledge）
DDL触发器是由修改数据库对象的 DDL 语句（如以 CREATE、ALTER 或 DROP）激发。
DDL触发器支持BEFORE和AFTER事件触发器，并在数据库或模式级运行。通常，DDL触发器用于监控数据库中的重要事件。有时用它们来监控错误代码。错误代码可能会执行破坏数据库或使数据库不稳定的活动。更常见的情况是：在开发、测试和stage系统中用它们来了解和监控数据库活动的动态。
当监控GRANT和REVOKE权限语句时，它们也是有效的安全工具。
四.DDL运用场景（DDL Scene）
(一) 首先我们来看一个简单的例子：创建数据库DDL_DB和一个名为DatabaseLog的表，现在创建一个DDL触发器：禁止用户修改和删除表，并进行提醒。执行下面的SQL脚本进行测试。
--Script1： --创建测试数据库 USE MASTER GO CREATE DATABASE DDL_DB --创建DDL触发器记录表 USE DDL_DB GO CREATE TABLE [dbo].[DatabaseLog]( [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL, [PostTime] [datetime] NOT NULL, [ServerName] [sysname] NOT NULL, [LoginName] [sysname] NOT NULL, [DatabaseUser] [sysname] NOT NULL, [DatabaseName] [sysname] NOT NULL, [Schema] [sysname] NULL, [Object] [sysname] NULL, [TSQL] [nvarchar](max) NOT NULL, [Event] [sysname] NOT NULL, [XmlEvent] [xml] NOT NULL, CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED ( [DatabaseLogID] ASC ) ON [PRIMARY] ) ON [PRIMARY] --Script2： --创建DDL触发器：禁止修改或者删除数据表 CREATE TRIGGER DDL_TableTrigger ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT ‘对不起，您不能对数据表进行操作，请联系DBA‘ ROLLBACK ; --测试删除表 USE DDL_DB GO DROP TABLE [DatabaseLog]
（Figure1：创建数据库级别的DDL）
（Figure2：返回的提示信息）
（Figure3：SSMS返回的提示信息）
创建数据库级别的DDL之后会出现在数据库触发器列表中，如Figure1；当执行删除表的Drop等DDL命令的时候，就会出现Figure2的提示信息；如果是在SSMS中删除表则会出现Figure3的提示信息。
(二) 在上面的基础上再进行扩展，创建一个DDL触发器：禁止用户删除数据库，并进行提醒。
--Script3： --禁止SQL Server服务器里删除数据库 CREATE TRIGGER DDL_DataBaseTrigger ON ALL SERVER FOR DROP_DATABASE AS PRINT ‘对不起，您不能删除数据库，请联系DBA‘ ROLLBACK; --测试删除数据库 USE MASTER GO DROP DATABASE [DDL_DB]
（Figure4：创建服务器级别的DDL）
（Figure5：返回的提示信息）
（Figure6：SSMS返回的提示信息）
创建服务器级别的DDL之后会出现在服务器对象-触发器的列表中，如Figure4；当执行删除数据库的Drop等DDL命令的时候，就会出现Figure5的提示信息；如果是在SSMS中删除数据库则会出现Figure6的提示信息。
(三) 很多时候在程序开发阶段是不会禁用对数据库的修改的，这些时候我们更希望是记录数据库的修改信息，方便对信息进行跟踪检查。使用 EVENTDATA 函数，可以捕获有关激发 DDL 触发器的事件的信息，此函数返回 xml 值。
前面已经创建了数据表DatabaseLog，创建下面的DDL_DatabaseLog触发器，每当数据库发生DDL事件，DDL触发器就会把相关的DDL信息插入到DatabaseLog表，信息包括操作的时间，操作人，操作的SQL等。
执行Script5测试脚本，返回Figure7的信息，查询DatabaseLog表，返回的记录有2条，一条是创建表信息，一条是删除表信息，如Figure8、Figure9所示。
--Script4： --创建当前数据库的DDL触发器 USE DDL_DB GO -- ============================================= -- Author: <听风吹雨> -- Create date: <2013.05.03> -- Description: <记录数据库DDL操作> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [DDL_DatabaseLog] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON; DECLARE @data XML; DECLARE @schema sysname; DECLARE @object sysname; DECLARE @eventType sysname; SET @data = EVENTDATA(); SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘sysname‘); SET @schema = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]‘, ‘sysname‘); SET @object = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘, ‘sysname‘) IF @object IS NOT NULL PRINT ‘ ‘ + @eventType + ‘ - ‘ + @schema + ‘.‘ + @object; ELSE PRINT ‘ ‘ + @eventType + ‘ - ‘ + @schema; IF @eventType IS NULL PRINT CONVERT(nvarchar(max), @data); INSERT [DDL_DB].[dbo].[DatabaseLog]( [PostTime], [ServerName], [LoginName], [DatabaseUser], [DatabaseName], [Schema], [Object], [TSQL], [Event], [XmlEvent]) VALUES( GETDATE(), @data.value(‘(/EVENT_INSTANCE/ServerName)[1]‘, ‘sysname‘), @data.value(‘(/EVENT_INSTANCE/LoginName)[1]‘, ‘sysname‘), CONVERT(sysname, CURRENT_USER), @data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]‘, ‘sysname‘), CONVERT(sysname, @schema), CONVERT(sysname, @object), @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(max)‘), @eventType, @data ); END; --Script5：测试DDL记录 --禁用DDL 触发器 DISABLE TRIGGER DDL_TableTrigger ON DATABASE; GO CREATE TABLE TestTable (a int) GO DROP TABLE TestTable; GO SELECT * FROM [DatabaseLog]; GO
（Figure7：返回的提示信息）
（Figure8：DatabaseLog表前半部分信息）
（Figure9：DatabaseLog表后半部分信息）
(四) 我们可以使用DDL触发器主动监控DDL语句的执行，当有对数据库执行DDL就会触发，我们把这些信息保存到表中，并且把操作用户的HostName和修改的T-SQL以邮件的形式发送到指定的邮件。关于设置数据库邮件可以参考：SQL Server 数据库邮件。发送邮件的效果如Figure10。邮件部分参考：MS SQL监控数据库的DDL操作
--Script5： --创建当前数据库的DDL触发器 USE DDL_DB GO -- ============================================= -- Author: <听风吹雨> -- Create date: <2013.05.03> -- Description: <记录数据库DDL操作，发送邮件预警> -- Blog: <http://www.cnblogs.com/gaizai/> -- ============================================= CREATE TRIGGER [DDL_DatabaseLog] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON; DECLARE @data XML; DECLARE @schema sysname; DECLARE @object sysname; DECLARE @eventType sysname; DECLARE @databaseName sysname; DECLARE @tableHTML NVARCHAR(MAX); SET @data = EVENTDATA(); SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘sysname‘); SET @schema = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]‘, ‘sysname‘); SET @object = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘, ‘sysname‘); SET @databaseName = @data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]‘, ‘sysname‘); IF @object IS NOT NULL PRINT ‘ ‘ + @eventType + ‘ - ‘ + @schema + ‘.‘ + @object; ELSE PRINT ‘ ‘ + @eventType + ‘ - ‘ + @schema; IF @eventType IS NULL PRINT CONVERT(nvarchar(max), @data); INSERT [DDL_DB].[dbo].[DatabaseLog]( [PostTime], [ServerName], [LoginName], [DatabaseUser], [DatabaseName], [Schema], [Object], [TSQL], [Event], [XmlEvent]) VALUES( GETDATE(), @data.value(‘(/EVENT_INSTANCE/ServerName)[1]‘, ‘sysname‘), @data.value(‘(/EVENT_INSTANCE/LoginName)[1]‘, ‘sysname‘), CONVERT(sysname, CURRENT_USER), @databaseName, CONVERT(sysname, @schema), CONVERT(sysname, @object), @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(max)‘), @eventType, @data ); SET @tableHTML = N‘<H1>DDL Event</H1>‘ + N‘<table border="0">‘ + N‘<tr><th>PostTime</th><th>ServerName</th><th>LoginName</th><th>DatabaseUser</th><th>DatabaseName</th><th>Object</th>‘ + N‘<th>TSQL</th></tr>‘ + CAST((SELECT td = [PostTime],‘‘, td = [ServerName],‘‘, td = [LoginName],‘‘, td = [DatabaseUser],‘‘, td = [DatabaseName],‘‘, td = [Object],‘‘, td = TSQL,‘‘ FROM [DDL_DB].[dbo].[DatabaseLog] WHERE DatabaseLogID =(SELECT MAX(DatabaseLogID) FROM [DDL_DB].[dbo].[DatabaseLog]) FOR XML PATH(‘tr‘), TYPE) AS NVARCHAR(MAX)) + N‘</table>‘; DECLARE @subjectStr NVARCHAR(MAX); SET @subjectStr = ‘DDL Event - DataBaseName: ‘ + @databaseName; EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘DataBase_DDL_Event‘, @recipients=‘bbspediy@126.com‘, @subject = @subjectStr, @body = @tableHTML, @body_format = ‘HTML‘; END;
（Figure10：邮件收到的预警）