SQL触发器（AFTER和INSTEAD OF）

转自：http://www.cnblogs.com/shepherldeng/archive/2010/06/23/1763766.html

何为触发器：触发器是数据库服务器中发生事件时自动执行的特种存储过程，这是帮助文档上的定义，就认为是一种特殊的存储过程也行，经常用于强制执行业务规则和数据完整性。

在正式操作之前先把触发器原理给说说，理解好触发器的机制是对更好的运用触发器自己好处很明显。

1、原理：

　　在执行触发器的过程中会产生两张临时表INSERTED 和DELETED，这两张表的作用简单说就是拿来记录数据的，主我们可以在触发器执行过程中更好的对数据进行操作

　　对表执行INSERT和UPDATE操作时都会在临时表INSERTED中拷贝一份所增加的数据。

　　对表执行DELETE和UPDATE操作时都会在临时表DELETED中拷贝一份所删除的数据

　   对表执行UPDATE操作时，首先把UPDATE前的数据DELETE到DELETED临时表中，然后再把所要更新的数据插入表中，最后把更新后的数据拷贝到INSERTED临时表中

 

触发器分为两种AFTER和INSTEAD OF

2、AFTER

　　字面意思就是在对表执行INSERT、UPDATE、DELETE操作后触发的触发器了，这里以INSERT结合触发器原理简单描述其过程

　　当有INSERT语句要执行时，首先直接执行INSERT语句，再就是AFTER执行触发器里面的操作（把数据拷贝到临时表INSERTED中，然后进行我们想要的操作，最后完成同样删除了临时　　　　　　　　　　　　　　　　　　　　表）

　　下面就开始进行实例演示了：

01
--学生
02
create table Student
03
(
04
    StudentNo int primary key, 
05
    StudentName varchar(20) not null
06
)
07
--书本
08
create table Book
09
(
10
    BookId int identity(1,1),
11
    BookName varchar(30),
12
    Owner int foreign key references Student(StudentNo)
13
)
14
insert into Student values(1,'ShepherlDeng')
15
insert into Student values(2,'Divi')
16
insert into Student values(3,'Lili')
17
insert into Book values('Book--01',1)
18
insert into Book values('Book--02',2)
19
insert into Book values('Book--01',3)
20
insert into Book values('Book--02',1)
21
insert into Book values('Book--03',2)
22
insert into Book values('Book--03',3)
23
insert into Book values('Book--04',1)
24
insert into Book values('Book--04',2)
25
insert into Book values('Book--01',3)
　如果我们有这样一个业务需求，就是当有一个学生时，必须买Book--01这一本书

于是我们可以写个简单的AFTER触发器

1
CREATE TRIGGER itStudent
2
on Student
3
after Insert
4
as
5
begin
6
declare @studentNo int
7
select @studentNo=StudentNo from Inserted
8
insert into Book values('Book--01',@studentNo)
9
end
　　当我们增加一条记录时就会自动在Book表中增加一条记录,这里其它操作也很相像所以不多说了只要知道是在操作守后再执行，重点放在INSTEAD OF触发器上。

 

3、INSTEAD OF

　　字面意思为取代,难道说当在一张表上定义了这样的触发器后，对表所做的INSERT、UPDATE、DELETE操作会被替换掉而不执行了？呵呵…这种触发器执行过程为：

　　当对表执行INSERT等操作时，并不直接执行这些操作而是转到触发器里面来执行触发器所定义的操作语句(应该说是一起执行的更合适)；

 

　　演示仍然为上面的所创建的表首先我们演示一个级联删除，当我们在Student表中删除一个被Book引用记录时因为有完整无缺约束我们无法删除这是INSTEAD OF就有作用了　　

1
CREATE TRIGGER dtStudent
2
on Student
3
instead of Delete
4
as
5
begin
6
declare @studentNo int
7
select @studentNo=StudentNo from Deleted
8
delete Book where Owner=@studentNo
9
end
　呵呵这是不是很爽呢……

 

　那我们再来演示一个UPDATE的INSTEAD OF触发器的操作

01
Create Trigger utStudent
02
on Student
03
instead of Update
04
as
05
begin
06
declare @studentNo int,
07
            @studentNold int
08
    if update(StudentNo)
09
    begin
10
    select @studentNold=StudentNo from Deleted
11
    select @studentNo=StudentNo from Inserted
12
    update Book set Owner=@studentNo where Owner=@studentNold
13
    end
14
end
这会是什么结果呢？执行后就会发现当你更改StudentNo是它的子表Book中的Owner也会一起改变了…

我还是觉得触发器能解决的好像存储过程也可以，以前也用存储过程写级联删除感觉要比这个要繁琐点，还有人说触发器是高手用的，总之各有各的应用场景了看怎么用了。