SQL Server 2008中的代码安全（二）：DDL触发器

MicrosoftSQL Server 提供两种主要机制来强制使用业务规则和数据完整性：约束和触发器。触发器为特殊类型的存储过程，可在执行语言事件时自动生效。SQL Server 包括三种常规类型的触发器： DML 触发器 、 DDL 触发器 和 登录触发器 。

1、当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

关于DML触发器应用最为广泛。这里不再赘述。MSDN官方说明: http://msdn.microsoft.com/zh-cn/library/ms189799.aspx

2、当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用 DDL 触发器。DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。

下面我们用举例说明DDL触发器（ http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx ）的应用：

示例一： 创建一个DDL触发器审核数据库级事件

/*************** 创建一个审核表,其中EventData是一个XML数据列 3w@live.cn *******************/ USE master GO CREATE TABLE dbo.ChangeAttempt (EventData xml NOT NULL, AttemptDate datetime NOT NULL DEFAULT GETDATE(), DBUser char(50) NOT NULL) GO /*************** 在目标数据库上创建一个触发器,以记录该数据库的索引变化动作， 包括Create|alter|Drop 3w@live.cn *******************/ CREATE TRIGGER db_trg_RestrictINDEXChanges ON DATABASE FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX AS SET NOCOUNT ON INSERT dbo.ChangeAttempt (EventData, DBUser) VALUES (EVENTDATA(), USER) GO /*************** 创建一个索引，以测试触发器 3w@live.cn *******************/ CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON dbo.ChangeAttempt(DBUser) GO /*************** 查看审核记录 3w@live.cn *******************/ SELECT EventData FROM dbo.ChangeAttempt --------/*************** --------删除测试触发器和记录表 --------3w@live.cn --------*******************/ ----drop TRIGGER [db_trg_RestrictINDEXChanges] ----ON DATABASE ----go ----drop table dbo.ChangeAttempt ----go

执行结果：

示例二： 创建一个DDL触发器审核服务器级事件

--------/*************** --------在目标数据库服务器上创建一个触发器,以防止添加登录账号， --------3w@live.cn --------*******************/ USE master GO -- Disallow new Logins on the SQL instance CREATE TRIGGER srv_trg_RestrictNewLogins ON ALL SERVER FOR CREATE_LOGIN AS PRINT 'No login creations without DBA involvement.' ROLLBACK GO --------/*************** --------试图创建一个登录账号 --------3w@live.cn --------*******************/ CREATE LOGIN johny WITH PASSWORD = '123456' GO --------/*************** --------删除演示触发器 --------3w@live.cn --------*******************/ drop TRIGGER srv_trg_RestrictNewLogins ON ALL SERVER go

效果：

注意： 要特别谨慎使用DDL触发器。如果设置不当，将会在数据库级甚至服务器级引发不可预知的后果。