修改DML触发器

在Management Studio中修改触发器之前，必须要先查看触发器的内容，通过 11.10.1 节的第（1）步到第（4）步，细心的读者可以已经发现，如图14所示，在【查询编辑器】对话框里显示的就是用来修改触发器的代码。编辑完代码之后，单击【执行】按钮运行即可。修改触发器的语法如下：

ALTER  TRIGGER 触发器名

   ON  数据表名或视图名

   AFTER INSERT或DELETE或UPDATE

AS

BEGIN

         --这里是要运行的SQL语句

END

GO

如果只要修改触发器的名称的话，也可以使用存储过程“sp_rename”。其语法如下：

sp_rename ‘旧触发器名’,’新触发器名’

值得一提的是修改触发器名称有可能会使某些脚本或存储过程运行出错。

 删除DML触发器

在Management Studio中删除触发器，必须要先查到触发器列表，通过 11.10.1 节的第（1）步到第（3）步，可以查看到数据表下的所有触发器列表，右击其中一个触发器，在弹出快捷菜单中选择【删除】选项，此时将会弹出【删除对象】对话框，在该对话框中单击【确定】按钮，删除操作完成。用以下SQL语句也对可删除触发器：

Drop Trigger 触发器名

注意：如果一个数据表被删除，那么SQL Server会自动将与该表相关的触发器删除。

禁用与启用DML触发器

禁用触发器与删除触发器不同，禁用触发器时，仍会为数据表定义该触发器，只是在执行Insert、Update或Delete语句时，除非重新启用触发器，否则不会执行触发器中的操作。

在Management Studio中禁用或启用触发器，也必须要先查到触发器列表，触发器列表里，右击其中一个触发器，在弹出快捷菜单中选择【禁用】选项，即可禁用该触发器。启用触发器与上类似，只是在弹出快捷菜单中选择【启用】选项即可。

用以下Alter Table语句也禁用或启用触发器，其语法如下：

Alter table 数据表名

  Disable或Enable trigger 触发器名或ALL

用Disable可以禁用触发器，用Enable可以启用触发器；如果要禁用或启用所有触发器，用“ALL”来代替触发器名。

  2005新增功能：DDL触发器

DDL触发器是SQL Server 2005新增的一个触发器类型，是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发。一般用于数据库中执行管理任务。

与DML触发器一样，DDL触发器也是通过事件来激活，并执行其中的SQL语句的。但与DML触发器不同，DML触发器是响应Insert、Update或Delete语句而激活的，DDL触发器是响应Create、Alter或Drop开头的语句而激活的。一般来说，在以下几种情况下可以使用DDL触发器：

l  数据库里的库架构或数据表架构很重要，不允许被修改。

l  防止数据库或数据表被误操作删除。

l  在修改某个数据表结构的同时修改另一个数据表的相应的结构。

l  要记录对数据库结构操作的事件。

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

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

 建立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结点的值，结点的位置是括号里的第一个参数。

  测试触发器功能

现在测试一下在上一章节中建立好的三个触发器的功能。下面所有的测试都是在【查询编辑器】对话框里进行的，要打开【查询编辑器】对话框，只要单击Management Studio里【新建查询】按钮即可。

测试例三：例三是保证【Northwind】数据库里不能删除表和修改表，在【查询编辑器】对话框里输入一个删除表的SQL语句：

Drop table 操作记录表

运行结果如图11.17所示：

图11.17 不允许删除表格

测试例四：例四是保证当前服务器里的所有数据库不能被删除，在【查询编辑器】对话框里输入一个删除数据库的SQL语句：

Drop DataBase test

运行结果如图11.18所示：

图11.18 不允许删除数据库

测试例五：例五是记录对【Northwind】所进行的操作，在【查询编辑器】对话框里输入一条添加数据表和一条删除数据表的SQL语句，然后再用Select语句查看【目志记录表】数据表里所有的记录：

CREATE TABLE 测试表(

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

         测试内容varchar(50) NOT NULL)

GO

Drop table 测试表

GO

select * from 日志记录表

GO

运行时不要忘了，前面曾经建立过一个不能删除数据表的触发器，要先把它禁用或删除。运行结果如图11.19所示：

图11.19 记录对数据库的操作