代码改变世界

SQL Server DDL触发器运用

2013-10-11 13:44  听风吹雨  阅读(13097)  评论(8)  编辑  收藏  举报

一.本文所涉及的内容（Contents）

  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]

wps_clip_image-14403_thumb

（Figure1：创建数据库级别的DDL）

wps_clip_image-32149_thumb

（Figure2：返回的提示信息）

wps_clip_image-7628_thumb

（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]

wps_clip_image-11348_thumb

（Figure4：创建服务器级别的DDL）

wps_clip_image-13353_thumb

（Figure5：返回的提示信息）

wps_clip_image-16272_thumb

（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

wps_clip_image-15637_thumb

（Figure7：返回的提示信息）

wps_clip_image-3080_thumb

（Figure8：DatabaseLog表前半部分信息）

wps_clip_image-5273_thumb

（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;

wps_clip_image-30426_thumb

（Figure10：邮件收到的预警）

五.补充说明（Addon）

(一) 关于DML、DDL、DCL、TCL的解释：

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

(二) 关于DML与DDL运用场景的一些区别：

DML 触发器可以看作是一种特殊的存储过程，可以保证系统保持其完整性，在系统中进行级联更新或强行业务规则。通过INSERTED 和 DELETED ，我们可以检索哪些列被更新了。DML触发器的本质就是当这两个发生数据修改时自动运行的存储过程。

DDL 触发器的构建主要是为了安全，或者根据部门的需求对系统所进行的变更进行通报。通过使用 EVENTDATA( ) 函数，可以在触发器中使用XML信息。

(三) 如果是线上的系统，可以考虑做下面的限制：在工作时间，不允许修改任何存储过程，否则回滚，示例代码如下：IF DATEPART(hour, GETDATE()) >=9 AND DATEPART(hour, GETDATE()) <= 17

(四) 一些维护DDL的SQL脚本：

--启用DDL 触发器
ENABLE TRIGGER DDL_TableTrigger ON DATABASE;
--禁用DDL 触发器
DISABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE;
--删除DDL 触发器
DROP TRIGGER ddlDatabaseTriggerLog ON DATABASE;

--禁用当前数据库中所有数据库级别的DDL 触发器
DISABLE TRIGGER ALL ON DATABASE
--禁用服务器实例中所有服务器级别的DDL 触发器
DISABLE TRIGGER ALL ON ALL SERVER

(五) 所有的DDL事件可以查看DDL 事件，也可以通过下面的SQL进行查看：

--获取有关DDL 触发器可触发的事件或事件组的信息
SELECT * FROM sys.trigger_event_types
--查看触发器的依赖关系
SELECT * FROM sys.sql_expression_dependencies
SELECT * FROM sys.dm_sql_referenced_entities
SELECT * FROM sys.dm_sql_referencing_entities
--获取有关数据库范围内的触发器的信息
SELECT * FROM sys.triggers
--获取有关激发触发器的数据库事件的信息
SELECT * FROM sys.trigger_events
SELECT * FROM sys.trigger_events AS a 
LEFT join sys.triggers AS b
ON a.object_id=b.object_id
WHERE name = 'ddlDatabaseTriggerLog'
--获取有关服务器范围内的触发器的信息
SELECT * FROM sys.server_triggers
SELECT * FROM sys.server_trigger_events
--查看数据库范围内的触发器的定义
SELECT * FROM sys.sql_modules

(六) 在执行Script3的时候如果你正在使用SSMS打开这个数据库（SPID）的话，那有可能不是出现Figure5的错误信息，而是出现Figure11的错误，这是因为你没有关闭SPID这些窗口，我还没有在程序连接的情况测试是否会返回这些信息：

wps_clip_image-1961_thumb

（Figure11：Figure5可能出现的）

(七) 如果你想修改DDL触发器的内容，那么你不能直接Alter DDL，而应该是先执行Drop DDL，之后在Create DDL。

(八) 之前已经创建了DDL_TableTrigger和DDL_DatabaseLog触发器，这两个触发器都是在DDL_DB数据库中创建的，当我们需要修改DDL触发器，应该触发对象从小到大进行修改，即DDL_TableTrigger（表）到DDL_DatabaseLog（数据库）进行修改。

如Figure12所示，如果只修改DDL_TableTrigger（Drop、Create），再执行下面的脚本将会出现Figure13的错误（还没找到官方理论描述）。解决办法就是对DDL_DatabaseLog进行创建创建（Drop、Create）。

--测试删除表
USE MASTER
GO
DROP DATABASE [DDL_DB]

wps_clip_image-27698_thumb

（Figure12：DDL触发器列表）

wps_clip_image-170_thumb

（Figure13：错误信息）

七.疑问（Questions）

(一) 删除DDL触发器是否也可以触发一个事件呢？不然如何防止用户先删除DDL触发器之后再做DDL操作呢？难道是用户权限？

解答：第一种方法，可以对DDL触发器进行权限控制；第二种方式就是在服务器级别加一个DROP的触发器，可以监控各个数据库的DDL触发器；下图Figure14是DDL_DatabaseLog被删除时的预警；

wps_clip_image-10302_thumb

（Figure14：删除DDL触发器）

(二) 能对所有数据库进行DDL监控？一条DDL预警能实现？

解答：可以在DDL_DatabaseLog把 ON DATABASE 设置为ON All SERVER，这样就可以监控整个服务器实例，下图Figure15是Logon_DB的DDL预警；

wps_clip_image-16633_thumb

（Figure15：删除DDL触发器）

八.参考文献（References）

MS SQL Server：DDL 触发器

DDL 触发器（msdn）

DDL 触发器（technet）

DML 触发器（technet）

登录触发器（technet）

SQL Server 2005 - Default Trace (默认跟踪)

MS SQL监控数据库的DDL操作（邮件通知）

DDL触发器分为

引用 DDL触发器介绍（Oracle）

DDL触发器

SQL SERVER – What is – DML, DDL, DCL and TCL – Introduction and Examples

管理触发器安全性

使用 EVENTDATA 函数

CREATE TRIGGER (Transact-SQL)

sp_send_dbmail (Transact-SQL)