Sql Server2005 Transact-SQL 新兵器学习总结之-DDL触发器

1.简介：Sql Server2005新增加了DDL触发器。与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。相反，它们将为了响应各种数据定义语言(DDL)事件而激发。这些事件主要与以关键字CREATE、ALTER和DROP开头的Transact-SQL语句对应。执行DDL式操作的系统存储过程也可以激发DDL触发器。2.作用：如果要执行以下操作，可以使用DDL触发器： (1)要防止对数据库架构进行某些更改。(2)希望数据库中发生某种情况以响应数据库架构中的更改。 (3)要记录数据库架构中的更改或事件。3.DDL 触发器作用域：(1)数据库范围:数据库范围内的DDL触发器都作为对象存储在创建它们的数据库中。(2)服务器范围:服务器范围内的DDL触发器作为对象存储在master数据库中。例如:当数据库中发生CREATE TABLE事件时，都会触发为响应CREATE TABLE事件创建的数据库范围DDL触发器。每当服务器上发生CREATE Index事件时，都会触发为响应CREATE Index事件创建的服务器范围DDL触发器。4.与DML触发器比较不同处：(1)DML触发器在INSERT、UPDATE和DELETE语句上操作。(2)DDL触发器在CREATE、ALTER、DROP和其他DDL语句上操作。(3)只有在完成Transact-SQL语句后才运行DDL触发器。DDL 触发器无法作为 INSTEAD OF 触发器使用。(4)DDL触发器不会创建插入(inserted)的和删除(deleted)的表.但是可以使用EVENTDATA函数捕获有关信息。5.例题：

-- 例题1 -- 服务器范围的DDL触发器 IF   EXISTS  (  SELECT    *              FROM     sys.server_triggers             WHERE    name  =   ' TR_CREATEDATABASE '  )      DROP   TRIGGER  TR_CREATEDATABASE  ON   ALL  SERVER ; GO CREATE   TRIGGER  TR_CREATEDATABASE ON   ALL  SERVER  FOR  CREATE_DATABASE  AS       PRINT   ' Database Created '      PRINT   CONVERT  ( nvarchar  ( 1000 ),EventData()) GO -- 创建数据库db1 CREATE   DATABASE  db1;

--收到下列消息Database Created<EVENT_INSTANCE><EventType>CREATE_DATABASE</EventType><PostTime>2008-09-01T20:17:35.170</PostTime><SPID>52</SPID><ServerName>YANFA0</ServerName><LoginName>YANFA0/Administrator</LoginName><DatabaseName>db1</DatabaseName><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/><CommandText>CREATE DATABASE db1;</CommandText></TSQLCommand></EVENT_INSTANCE>

-- 例题2 -- 服务器范围的DDL触发器 IF   EXISTS  (  SELECT    *              FROM     sys.triggers             WHERE    name  =  N ' TR_DROPINDEX '                      AND  parent_class  =   0  )      DROP   TRIGGER   [ TR_DROPINDEX ]   ON   DATABASE GO CREATE   TRIGGER  TR_DROPINDEX ON   DATABASE   FOR  DROP_INDEX AS      PRINT   ' DROP INDEX '      ROLLBACK GO -- 建立表 CREATE   TABLE  TableTest ( ids  INT , i  NVARCHAR ( 100 ) ) GO -- 建立唯一索引 CREATE   UNIQUE   INDEX   index_TableTest  ON  TableTest ( ids ) GO -- 删除索引 DROP   INDEX  index_TableTest ON  TableTest GO

--收到下列消息--我们从消息可以看到删除索引不成功！DROP INDEX消息 3609，级别 16，状态 2，第 2 行事务在触发器中结束。批处理已中止。

-- 例题3 -- 当不再需要某个DDL触发器时，可以禁用或删除该触发器。  -- 禁用DDL触发器不会将其删除。该触发器仍然作为对象存在于当前数据库中。 -- 但是，当运行编写触发器程序所用的任何DDL语句时，不会激发触发器。 -- 可以重新启用禁用的DDL触发器。 DISABLE  TRIGGER   [ TR_DROPINDEX ]   ON   DATABASE GO ENABLE  TRIGGER   [ TR_DROPINDEX ]   ON   DATABASE GO