触发器的操作与管理

很多在前线奋斗的朋友，一直对触发器比较头疼，今天农夫将和大家一起学习下  触发器的操作与管理 。

 
触发器概述
 
?触发器是一种特殊的存储过程，它在执行语言事件时自动生效。
?SQL Server2005 包括两大类触发器：DML 触发器和 DDL 触发器。
   （1）DML 触发器在数据库中发生数据操作语言 (DML) 事件时将启用。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。
   （2）DDL 触发器是 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用这些触发器
 
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 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。  
 
DML触发器创建 
 
当创建一个触发器时必须指定如下选项：
（1）名称；
（2）在其上定义触发器的表；
（3）触发器将何时激发；
（4）激活触发器的数据修改语句，有效选项为 INSERT、UPDATE 或 DELETE，多个数据修改语句可激活同一个触发器；
（5）执行触发操作的编程语句。 
 
?DML 触发器使用 deleted 和 inserted 逻辑表。它们在结构上和触发器所在的表的结构相同，SQL Server会自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。
?Deleted表用于存储delete，update语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。
?Inserted表用于存储Insert或update语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到Inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。 
 
使用SQL Server管理平台创建触发器的过程如下：
在SQL Server管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如图9-1所示，右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图9-2所示。最后，单击“执行”按钮，即可成功创建触发器。

 
图9-1 新建触发器对话框

 

 
图9-2 新建触发器窗口

 
?使用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　示例说明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-3 触发器的执行结果 
 
?例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 
 
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

 
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

  
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