设计DDL触发器

11.15  2005新增功能：设计DDL触发器

只要注意到DDL触发器和DML触发器的区别，设计DDL触发器与设计DML触发器也很类似，下面详细讲述一下要怎么去设计一个DDL触发器。

11.15.1  建立DDL触发器的语句

建立DDL触发器的语法代码如下：

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

用中文取代一下英文可以看得更明白：

CREATE TRIGGER 触发器名

ON  ALL SERVER或DATABASE

FOR 或AFTER

激活DDL触发器的事件

AS

         要执行的SQL语句

其中：

l  ON后面的All Server是将DDL触发器作用到整个当前的服务器上。如果指定了这个参数，在当前服务器上的任何一个数据库都能激活该触发器。

l  ON后面的Database是将DDL触发器作用到当前数据库，只能在这个数据库上激活该触发器。

l  For或After是同一个意思，指定的是After触发器，DDL触发器不能指定的Stead Of触发器。

l  激活DDL触发器的事件包括两种，在DDL触发器作用在当前数据库情况下可以使用以下事件：

CREATE_APPLICATION_ROLE	ALTER_APPLICATION_ROLE	DROP_APPLICATION_ROLE
CREATE_ASSEMBLY	ALTER_ASSEMBLY	DROP_ASSEMBLY
ALTER_AUTHORIZATION _DATABASE
CREATE_CERTIFICATE	ALTER_CERTIFICATE	DROP_CERTIFICATE
CREATE_CONTRACT	DROP_CONTRACT
GRANT_DATABASE	DENY_DATABASE	REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION	DROP_EVENT_NOTIFICATION
CREATE_FUNCTION	ALTER_FUNCTION	DROP_FUNCTION
CREATE_INDEX	ALTER_INDEX	DROP_INDEX
CREATE_MESSAGE_TYPE	ALTER_MESSAGE_TYPE	DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION	ALTER_PARTITION_FUNCTION	DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME	ALTER_PARTITION_SCHEME	DROP_PARTITION_SCHEME
CREATE_PROCEDURE	ALTER_PROCEDURE	DROP_PROCEDURE
CREATE_QUEUE	ALTER_QUEUE	DROP_QUEUE
CREATE_REMOTE_SERVICE _BINDING	ALTER_REMOTE_SERVICE _BINDING	DROP_REMOTE_SERVICE _BINDING
CREATE_ROLE	ALTER_ROLE	DROP_ROLE
CREATE_ROUTE	ALTER_ROUTE	DROP_ROUTE
CREATE_SCHEMA	ALTER_SCHEMA	DROP_SCHEMA
CREATE_SERVICE	ALTER_SERVICE	DROP_SERVICE
CREATE_STATISTICS	DROP_STATISTICS	UPDATE_STATISTICS
CREATE_SYNONYM	DROP_SYNONYM	CREATE_TABLE
ALTER_TABLE	DROP_TABLE
CREATE_TRIGGER	ALTER_TRIGGER	DROP_TRIGGER
CREATE_TYPE	DROP_TYPE
CREATE_USER	ALTER_USER	DROP_USER
CREATE_VIEW	ALTER_VIEW	DROP_VIEW
CREATE_XML_SCHEMA _COLLECTION	ALTER_XML_SCHEMA _COLLECTION	DROP_XML_SCHEMA _COLLECTION

在DDL触发器作用在当前服务器情况下，可以使用以下事件：

ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE	ALTER_DATABASE	DROP_DATABASE
CREATE_ENDPOINT	DROP_ENDPOINT
CREATE_LOGIN	ALTER_LOGIN	DROP_LOGIN
GRANT_SERVER	DENY_SERVER	REVOKE_SERVER

例三，建立一个DDL触发器，用于保护数据库中的数据表不被修改，不被删除。具体操作步骤如下：

（1）启动Management Studio，登录到指定的服务器上。

（2）在如图11.1所示界面的【对象资源管理器】下选择【数据库】，定位到【Northwind】数据库上。

（3）单击【新建查询】按钮，在弹出的【查询编辑器】的编辑区里输入以下代码：

CREATE TRIGGER 禁止对数据表操作

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

   PRINT '对不起，您不能对数据表进行操作'

   ROLLBACK ;

（4）单击【执行】按钮，生成触发器。

例四，建立一个DDL触发器，用于保护当前SQL Server服务器里所有数据库不能被删除。具体代码如下：

CREATE TRIGGER 不允许删除数据库

ON all server 

FOR DROP_DATABASE

AS

   PRINT '对不起，您不能删除数据库'

   ROLLBACK ;

GO

例五，建立一个DDL触发器，用来记录数据库修改状态。具体操作步骤如下：

（1）建立一个用于记录数据库修改状态的表：

CREATE TABLE 日志记录表(

         编号 int IDENTITY(1,1) NOT NULL,

         事件 varchar(5000) NULL,

         所用语句 varchar(5000) NULL,

         操作者 varchar(50) NULL,

         发生时间 datetime NULL,

 CONSTRAINT PK_日志记录表 PRIMARY KEY CLUSTERED

(

         编号 ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

（2）建立DDL触发器：

CREATE TRIGGER 记录日志

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @log XML

SET @log = EVENTDATA()

INSERT  日志记录表

   (事件, 所用语句,操作者, 发生时间)

   VALUES

   (

   @log.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

   @log.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),

   CONVERT(nvarchar(100), CURRENT_USER),

   GETDATE()

   ) ;

GO

其中Eventdata是个数据库函数，它的作用是以XML格式返回有关服务器或数据库事件的信息。@log.value是返回log这个XML结点的值，结点的位置是括号里的第一个参数。