触发器

触发器

 

就本质而言，触发器也是一种存储过程，它是一种特殊类型的存储过程。触发器只要满足一定的条件，它就可以触发完成各种简单和复杂的任务，可以帮助我们更好的维护数据库中数据的完整性。本章要重点理解触发器的特点和作用，掌握创建和管理触发器的方法。

 

 

好处与坏处：

好处：相对于外部程序、存储过程，触发器可以更快更高效的维护数据
坏处：触发器要用的恰到好处，一个大型应用里，触发器越少越好，触发器会使编程时源码的结构被迫打乱，为将来的程序修改、源码阅读带来很大不便。

 

触发器概述

 

l         Microsoft SQL Server 2005 提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。

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

l         SQL Server 包括两大类触发器：DML 触发器和 DDL 触发器。

 

 

类型	用途	触发事件
DML 触发器	当数据库中发生数据操作语言事件时将调用 DML 触发器。	INSERT语句 UPDATE语句 DELETE语句
DDL 触发器	当服务器或数据库中发生数据定义语言 事件时将调用这些触发器。	以 CREATE、ALTER 和 DROP 开头的语句。

 

 

DML触发器有三种类型：

（1）AFTER触发器 (之后触发)

（2）INSTEAD OF 触发器（之前触发）

（3）CLR 触发器(不作要求)

 注：公共语言运行时（Common Language Runtime，CLR）

 

 

AFTER触发器

 

n       将在数据变动（INSERT、UPDATE和DELETE操作）完成后才被激发。对变动数据进行检查，如果发现错误，将拒绝或回滚变动的数据。

n       在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。

n       指定 AFTER 与指定 FOR 相同。

n       AFTER 触发器只能在表上指定。

n       一个表中可以具有多个给定类型的 AFTER 触发器，只要它们的名称不相同即可。

n       每个触发器只能应用于一个表。

n       一个触发器可以应用于三个用户操作（UPDATE、INSERT 和 DELETE）的任何子集。

 

INSTEAD OF 触发器

 

n       将在数据变动以前被激发。并取代变动数据（INSERT、UPDATE和DELETE操作）的操作，转而去执行触发器定义的操作 （本来的操作就不执行了）

n       执行 INSTEAD OF 触发器代替通常的触发动作。如可以为视图定义 INSTEAD OF 触发器，则这些触发器能够扩展视图可支持的更新类型。

n       INSTEAD OF触发器的主要优点是可以使不能更新的视图支持更新。基于多个表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。（见示例1）

n       一个表只能具有一个给定类型的 INSTEAD OF 触发器。

 

 

创建触发器的语法格式：

CREATE TRIGGER 触发器名ON {表名|视图名}

[WITH  ENCRYPTION]

[FOR|AFTER|INSTEAD OF]

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

[NOT FOR REPLICATION]

AS

 SQL语句

[RETURE 整数表达式]

 

 

两个特殊的表：

触发器在表上定义。执行触发器时，SQL Server系统创建了两个特殊的临时表：inserted表和deleted表。它们驻留在内存中，两个表的结构与被该触发器作用的表的结构相同，并且由系统维护。用户不能对它们进行修改，但可以从表中获取数据。触发器工作完成后，与此触发器相关的这两个表也会被删除。

 

n       DML 触发器在执行过程中可以使用两个特殊的临时表Deleted 表和 Inserted表。

n       这两个表都存在于内存中，它们在结构上和触发器所在的表的结构相同，SQL Server会自动创建和管理这些表。

n       可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。

n       不允许用户直接对其修改，但可访问。触发器工作完成后，与该触发器相关的这两个表也将被删除。

n       在触发器执行的时候，会产生两个临时表：inserted表和deleted表。

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

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

 

 

在对具有触发器的表进行操作时，将进行下列操作：

n       执行INSERT操作插入到触发器表中的新行被插入到inserted表中。

n       执行DELETE操作从触发器表中删除的行被插入到deleted表中。

n       执行UPDATE操作先从触发器表中删除旧行，然后再插入新行。其中被删除的旧行被插入到deleted表中，插入的新行被插入到inserted表中。

 

 

 

示例代码：

 

--【示例1】创建关于视图V_score的触发器，实现对于视图的数据插入操作。

Use study

go

--创建视图

create view V_score

as

select s.sno as '学号', sname as '姓名', c.cno as '课程号',

cname as '课程名',degree as '成绩'

from Student s, Course c, score sc

where s.sno=sc.sno and c.cno=sc.cno and s. depart='信息系'

go

 

-- select * from v_score

 

-- drop view v_score

 

insert into v_score(学号,课程号,成绩) values('109','1091',89);

 

Use study

go

if exists(select * from sysobjects where name = 'tr_v_score_insert' and type = 'tr')

    drop trigger tr_v_score_insert

Go

 

create trigger tr_v_score_insert

on V_score

instead of insert

as

declare @sno char(10),@cno char(10), @degree tinyint  --定义个变量，分别用于存放学号、课程号、成绩

select @sno=学号, @cno=课程号, @degree=成绩

from inserted   --用于从inserted表中给变量@sno,@cno,@degree赋值

print @sno

print @cno

print @degree

 --判断是否存在相同的课程号和学号

if (not exists(select sno from student where sno=@sno)and not exists(select cno from course where cno=@cno))

   insert into score values(@sno, @cno, @degree)

else

   print 'wrong data!'

go

 

 

 

面试中没做出来的问题：

       每当员工的工龄增加一个三年，就把他的底薪加300

 

这里，我用Study数据库模拟这种情况

 --【示例2】

use study

go

 

if exists(select name from sysobjects where name='tri1' and type='tr')

    drop trigger tri1

go

 

create trigger tri1

on score

after update

as

declare @cno_beforUpdate int,@cno_afterUpdate int,@current_sno int

select @cno_beforUpdate=cno,@current_sno=sno from deleted

select @cno_afterUpdate=cno from Score

if(@cno_beforUpdate<@cno_afterUpdate and @cno_afterUpdate%10=0)

begin

    update score

       set degree=degree+10

    where sno=@current_sno

end

return

go

 

update score

set cno=cno+10

where sno=109