分类目录

链接

2012年 3月
 1234
567891011
12131415161718
19202122232425
262728293031  

近期文章

热门标签

新人福利,免费薅羊毛

现在位置:    首页 > SQL Server > 正文
SQL SERVER DDL触发器
SQL Server 暂无评论 阅读(3,274)

DDL触发器

DDL触发器是在SQL Server 2005中推出的,不像表中的数据改变时所执行的Transact-SQL代码的DML触发器,一个DDL触发器是在表的结构改变时才触发。这是一个很好的跟踪和审查对数据库schema的结构化改变的方法。

这些触发器的句法类似于DML触发器的语法。DDL触发器是AFTER 触发器,它对DDL语言事件作出响应;它们不对执行DDL等的类似操作的系统存储过程作出响应。它们完全是事务型的,所以你可以回滚一个DDL操作。你可 以在一个DDL触发器中运行Transact-SQL或CLR 代码。DDL触发器还类似于其它的模块一样支持EXECUTE AS 条件语句。

SQL Server 提供了关于触发器事件作为非强类型的XML的信息。它可以通过一个叫做EVENTDATA()的新的输出XML的内置功能来实现。你可以使用XQuery 表达式来解析EVENTDATA() XML ,以便能够发现像schema名称、目标对象名称、用户名称还有触发触发器的整个Transact-SQL DDL语句等的事件属性。

数据库级别的DDL触发器触发于数据库级别和之下的DDL语言事件。例如CREATE_TABLE、ALTER_USER 等等。服务器级别的DDL触发器触发于服务器级别的DDL语言事件,例如CREATE_DATABASE、ALTER_LOGIN等等。为了管理方便,你 可以使用事件组,像DDL_TABLE_EVENTS作为所有CREATE_TABLE、ALTER_TABLE和DROP_TABLE事件的总的简称。 这些广泛的DDL事件组和事件类型,以及它们所关联的XML EVENTDATA(),在SQL Server 在线书籍中有描述。

和DML触发器的名称不一样,DML触发器的名称是schema 范围的,而DDL触发器名称是数据库范围的或服务器范围的。

使用这个新的目录视图来找出用于DML触发器和数据库级别的DDL触发器的触发器元数据:

  SELECT*FROM sys.triggers ;   GO

如果parent_class_desc 字段具有一个‘DATABASE’值,那么它是一个DDL触发器,并且名称是数据库本身范围内的。Transact-SQL 触发器的代码体在sys.sql_modules 目录视图中可以看到,你可以将它连接到sys.triggers 的object_id 字段上。关于一个CLR触发器的元数据可以在sys.assembly_modules 目录视图中看到,你可以将它连接到sys.triggers的object_id字段上。

使用目录视图来找出服务器范围的DDL触发器的元数据:

SELECT*FROM sys.server_triggers ;   GO

Transact-SQL 服务器级别的触发器的代码体可以在sys.server_sql_modules 目录视图中看到,你可以将它连接到sys.server_triggers的object_id字段上。关于一个CLR服务器级别的触发器的元数据可以在 sys.server_assembly_modules目录视图中找到,你可以将它连接到sys.server_triggers的object_id 字段上。

你可以使用DDL触发器来捕捉和审查数据库中的DDL活动。创建一个具有非强类型的XML字段的审查表。为DDL事件或你感兴趣的事件组创建一个 EXECUTE AS SELF DDL 触发器。这个DDL触发器的代码体可以简单地将EVENTDATA() XML插入到审查表中。

DDL触发器的另一个有趣的使用是触发于CREATE_USER 事件然后添加代码到自动权限管理中去。例如,假设你想让所有的数据库用户获得一个对存储过程P1、P2和P3的GRANT EXECUTE 权限。DDL触发器可以从EVENTDATA() XML中提取用户名称,动态地生成一个语句,像‘GRANT EXECUTE ON P1 TO someuser’,然后对它EXEC()。

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
CREATETABLE dbo.ChangeAttempt
(EventData xml
NOTNULL,
AttemptDate
datetimeNOTNULLDEFAULTGETDATE(),
DBUser
char(50) NOTNULL)
GO

/***************
在目标数据库上创建一个触发器,以记录该数据库的索引变化动作,
包括Create|alter|Drop
3w@live.cn
******************
*/

CREATETRIGGER db_trg_RestrictINDEXChanges
ONDATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
SET NOCOUNT ON
INSERT dbo.ChangeAttempt
(EventData, DBUser)
VALUES (EVENTDATA(), USER)
GO

/***************
创建一个索引,以测试触发器
3w@live.cn
******************
*/

CREATENONCLUSTEREDINDEX 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
CREATETRIGGER srv_trg_RestrictNewLogins
ONALL 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
--
------*******************/

dropTRIGGER srv_trg_RestrictNewLogins
ONALL SERVER
go

 

 

============ 欢迎各位老板打赏~ ===========

本文版权归Bruce's Blog所有,转载引用请完整注明以下信息:
本文作者:Bruce
本文地址:SQL SERVER DDL触发器 | Bruce's Blog

发表评论

留言无头像?