第9章 触发器的操作与管理  

触发器概述

•触发器是一种特殊的存储过程，它在执行语言事件时自动生效。

•SQL Server2005 包括两大类触发器：DML 触发器和 DDL 触发器。

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

   （2）DDL 触发器是 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用这些触发器。

9.1 DML触发器的创建和应用  

• 当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。

 

• DML 触发器的主要优点如下：

（1）DML 触发器可通过数据库中的相关表实现级联更改。例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其他表中的各匹配行采取删除操作。该触发器用 title_id 列作为惟一键，在 titleauthor、sales 及 roysched 表中对各匹配行进行定位。

（2）DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。与 CHECK 约束不同，DML 触发器可以引用其他表中的列。

（3）DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。 

9.1.1 DML触发器创建  

当创建一个触发器时必须指定如下选项：

（1）名称；

（2）在其上定义触发器的表；

（3）触发器将何时激发；

（4）激活触发器的数据修改语句，有效选项为 INSERT、UPDATE 或 DELETE，多个数据修改语句可激活同一个触发器；

（5）执行触发操作的编程语句。

9.1.1 DML触发器创建

•      DML 触发器使用 deleted 和 inserted 逻辑表。它们在结构上和触发器所在的表的结构相同，SQL Server会自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。

•      Deleted表用于存储delete，update语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。

•      Inserted表用于存储Insert或update语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到Inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

9.1.1 DML触发器创建

使用SQL Server管理平台创建触发器的过程如下：

在SQL Server管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如图9-1所示，右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图9-2所示。最后，单击“执行”按钮，即可成功创建触发器。

9.1.1 DML触发器创建

•使用CREATE TRIGGER命令创建DML触发器的语法形式如下：

 CREATE TRIGGER [schema_name.]trigger_name

 ON {table|view}

 [WITH [ENCRYPTION] EXECUTE AS Clause][,...n]]

  {FOR|AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,]  [DELETE]}

  [WITH APPEND]

 [NOT FOR REPLICATION]

 AS

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

 <method_specifier> ::= assembly_name.class_name.method_name

9.1.1 DML触发器创建

•例9-1　示例说明inserted，deleted表的作用。执行结果如右图。

程序清单如下：

create table sc

 (sno  char(10),

  cno  char(2),

  score  real)

Go

CREATE TRIGGER tr1

ON sc

FOR INSERT, UPDATE, DELETE

AS

PRINT ‘inserted表：’

Select * from inserted

PRINT ‘deleted表：’

Select * from deleted

Go

9.1.1 DML触发器创建

•例9-2　创建一个触发器，在 s 表上创建一个插入、更新类型的触发器。

程序清单如下：

CREATE TRIGGER tr_s

ON s

FOR INSERT, UPDATE

AS

Begin

DECLARE @bh varchar(6)

SELECT @bh =inserted.sno FROM inserted /*获取插入或更新操作时的新值（学号）*/

End

9.1.2 DML触发器的应用  

1. 使用INSERT触发器

INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。

例9-3建立一个触发器，当向sc表中添加数据时，如果添加的数据与s表中的数据不匹配（没有对应的学号），则将此数据删除。

程序清单如下：

CREATE TRIGGER sc_ins ON sc

FOR INSERT

AS

BEGIN

DECLARE @bh char(5)

Select @bh=Inserted.sno from Inserted

If not exists(select sno from s where s.sno=@bh)

Delete sc where sno=@bh

END

9.1.2 DML触发器的应用  

1. 使用INSERT触发器

例9-4创建一个触发器，当插入或更新成绩列时，该触发器检查插入的数据是否处于设定的范围内。

程序清单如下：

CREATE TRIGGER sc_insupd

ON sc

FOR INSERT, UPDATE

AS

DECLARE @cj int,

SELECT @cj=inserted.score from inserted

IF （@cj<0 or @cj > 100）

BEGIN

   RAISERROR （'成绩的取值必须在0到100之间', 16, 1）

   ROLLBACK TRANSACTION

END

9.1.2 DML触发器的应用  

2.  使用UPDATE触发器

•当在一个有UPDATE触发器的表中修改记录时，表中原来的记录被移动到删除表中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入表以及被修改的表，以确定如何完成数据库操作。

•例9-5　创建一个修改触发器，该触发器防止用户修改表s的入学成绩。

程序清单如下：

create trigger tri_s_upd

on s

for update

as

if update（escore）

begin

raiserror（‘不能修改入学成绩’,16,10）

rollback transaction

end

go

9.1.2 DML触发器的应用  

•使用UPDATE触发

•例9-6 DAS数据库由存放实时数据的数据表以及存放历史数据的历史表组成。由于存放实时数据的数据表不断更新，为了保存更新过的数据，在实时表和历史表之间建立了触发器。程序清单如下：

CREATE TRIGGER DasD_UTRIGGER ON DasD FOR UPDATE AS

BEGIN

IF Update(TV)  /*数据更新*/

BEGIN

    UPDATE DasD

        SET UT=getdate()    /*更新时间*/

        FROM DasD,inserted

        WHERE DasD.ID=inserted.ID

    INSERT DasDHis(ID,TV,UT)

        SELECT inserted.ID,inserted.TV,DasD.UT FROM DasD,inserted

        WHERE  DasD.ID=inserted.ID    /*将更新过的数据送入历史库*/

END

END 

9.1.2 DML触发器的应用  

3. 使用DELETE触发器

•DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除，第二种情况是执行可删除主记录的子记录的级联删除操作。

•例9-8 建立一个与s表结构一样的表s1，当删除表s中的记录时，自动将删除掉的记录存放到s1表中。

程序清单如下：

CREATE TRIGGER tr_del ON s /*建立触发器

FOR DELETE  /*对表删除操作

AS insert s1 select * from deleted /*将删除掉的数据送入表s1中*/

GO

9.1.2 DML触发器的应用  

3. 使用DELETE触发器

•例9-9当删除表s中的记录时，自动删除表sc中对应学号的记录。

程序清单如下：

CREATE TRIGGER tr_del_s ON s

FOR DELETE

BEGIN

DECLARE @bh char(5)

Select @bh=deleted.sno from deleted

Delete sc where sno=@bh

END

9.2  DDL触发器的创建和应用  

•DDL 触发器会为响应多种数据定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。

•DDL 触发器一般用于以下目的：

（1）防止对数据库架构进行某些更改；

（2）希望数据库中发生某种情况以响应数据库架构中的更改；

（3）要记录数据库架构中的更改或事件。

•仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。

9.2.1 创建DDL触发器  

•使用CREATE TRIGGER命令创建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>[;]}

•其中：

 <ddl_trigger_option>::=[ENCRYPTION] EXECUTE AS Clause]

  <method_specifier> ::= assembly_name.class_name.method_name

9.2.2 DDL触发器的应用  

•在响应当前数据库或服务器中处理的 Transact-SQL 事件时，可以激发 DDL 触发器。触发器的作用域取决于事件。

•例9-11 使用 DDL 触发器来防止数据库中的任一表被修改或删除。

 程序清单如下：

 CREATE TRIGGER safety

 ON DATABASE

 FOR DROP_TABLE, ALTER_TABLE

 AS

  PRINT 'You must disable Trigger "safety" to drop or alter tables!'

 ROLLBACK

9.2.2 DDL触发器的应用

•例9-12  使用 DDL 触发器来防止在数据库中创建表。

 程序清单如下：

 CREATE TRIGGER safety

 ON DATABASE

 FOR CREATE_TABLE

  AS

 PRINT 'CREATE TABLE Issued.'

 SELECT

 EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

 RAISERROR ('New tables cannot be created in this database.', 16, 1) 

 ROLLBACK

9.3 DDL查看、修改和删除触发器

9.3.1 查看触发器

 

9.3.2 修改触发器

 

9.3.3 删除触发器

9.3.1 查看触发器

   如果要显示作用于表上的触发器究竟对表有哪些操作，必须查看触发器信息。在SQL Server中，有多种方法可以查看触发器信息，其中最常用的有如下两种：

（1）使用SQL Server管理平台查看触发器信息；

（2）使用系统存储过程查看触发器。

9.3.1 查看触发器

（1）使用SQL Server管理平台查看触发器信息。

在SQL Server管理平台中，展开服务器和数据库，选择并展开表，然后展开触发器选项，右击需要查看的触发器名称，如图9-4所示，从弹出的快捷菜单中，选择“编写触发器脚本为→create到→新查询编辑器窗口”，则可以看到触发器的源代码。

9.3.1 查看触发器

（2）使用系统存储过程查看触发器。

•系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。其具体用途和语法形式如下。

•sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。

　　sp_help  ‘触发器名称’

•sp_helptext：用于查看触发器的正文信息。

　　sp_helptext  ‘触发器名称’

•sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。

　　sp_depends  ‘触发器名称’

　　sp_depends  ‘表名’

9.3.2 修改触发器

•通过SQL Server管理平台、存储过程，可以修改触发器的正文和名称。

1．使用SQL Server管理平台修改触发器正文。

在管理平台中，展开指定的表，右击要修改的触发器，从弹出的快捷菜单中选择“修改”选项，则会出现触发器修改窗口，如图9-5所示。在文本框中修改触发器的SQL语句，单击“语法检查”按钮，可以检查语法是否正确，单击“执行”按钮，可以成功修改此触发器。

9.3.2 修改触发器

•修改DML触发器的语法形式如下：

 ALTER TRIGGER schema_name.trigger_name

  ON (table|view)

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

  (FOR|AFTER|INSTEAD OF)

  {[DELETE][,][INSERT][,][UPDATE]}

 [NOT FOR REPLICATION]

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

  <dml_trigger_option>::=[ENCRYPTION][&lEXECUTE AS Clause >]

  <method_specifier> ::=assembly_name.class_name.method_name

•修改DDL触发器的语法形式如下：

  ALTER TRIGGER trigger_name

  ON {DATABASE|ALL SERVER}[WITH <ddl_trigger_option> [,...n]]

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

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

  <ddl_trigger_option>::=[ENCRYPTION][&lEXECUTE AS Clause > ]

  <method_specifier> ::=assembly_name.class_name.method_name

9.3.2 修改触发器

•例9-13  修改触发器。

 程序清单如下：

 CREATE TRIGGER s_reminder

 ON S

  WITH ENCRYPTION

  AFTER INSERT, UPDATE

 AS

 RAISERROR ('不能对该表执行添加、更新操作', 16, 10)

 ROLLBACK

 GO

• -- 下面修改触发器.

 ALTER TRIGGER s_reminder

  ON S

  AFTER INSERT

  AS

  RAISERROR ('不能对该表执行添加操作', 16, 10)

  ROLLBACK

 GO

9.3.2 修改触发器

2．使用sp_rename命令修改触发器的名称。

       sp_rename命令的语法形式如下：

　　sp_rename oldname,newname

9.3.3 删除触发器

•由于某种原因，需要从表中删除触发器或者需要使用新的触发器，这就必须首先删除旧的触发器。只有触发器所有者才有权删除触发器。删除已创建的触发器有三种方法：

 （1）使用系统命令DROP TRIGGER删除指定的触发器。其语法形式如下：

 　　  DROP TRIGGER { trigger } [ ,...n ]

 （2）删除触发器所在的表。删除表时，SQL Server将会自动删除与该表相关的触发器。

 （3）在SQL Server管理平台中，展开指定的服务器和数据库，选择并展开指定的表，右击要删除的触发器，从弹出的快捷菜单中选择“删除”选项，即可删除该触发器。