SQL Server DDL触发器运用

1. 本文所涉及的内容（Contents）
2. 背景（Contexts）
3. 基础知识（Rudimentary Knowledge）
4. DDL运用场景（DDL Scene）
5. 补充说明（Addon）
6. 疑问（Questions）
7. 参考文献（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：邮件收到的预警）