INSTEAD OF 触发器

实现在一定条件下不向表中进行操作的触发器也可以用 AFTER | FOR 触发器，方法是对系统临时表 inserted 表或者 deleted 表进行检查，发现不能操作的，便用 ROLLBACK 对表进行回滚。但是这种操作是有点问题的，一是如果同时进行其他操作的话，可能会被一起回滚，二是有点脱什么放什么的嫌疑，先干了，发现不行，再回退，是不是很好笑！

 

其实 INSTEAD OF 触发器是解决这类问题的最好办法。在对表进行操作时，总会产生 INSERTED 和（或）DELETED表，不管这个操作是否已经进行。这里的和/或，要看进行的什么操作，插入，产生 INSERTED 表，删除，产生DELETED表，而update则两个都会产生。

 

行了，实际的编程思想是这样的。。。

如果不满足条件就不能插入，好，用 INSTEAD OF 触发器，利用 INSERTED 表先检查是否满足条件，如满足条件，那就只要： insert into 目标表 select * from inserted 就OK了，否则，可以什么都不做，由插入程序去根据 @@ROWCOUNT判断插入是否成功。当然，也可以用select '提示' 的方法给出提示。

 

如果不满足条件就不能删除，好，用 INSTEAD OF 触发器，利用 DELETED 表先检查是否满足条件，如满足条件，只要： delete from 目标表 where id in(select id from deleted) 就OK了，不满足条件也是什么都不做或给出提示，注意这里的 id 列必须是标识列，可不能删错了啊。

 

如果不满足条件就不能更新，同样用 INSTEAD OF 触发器（怎么有点儿像葛优在做广告？我最烦这人了！），这时两个系统临时表都可以派到用场，如果要检查更新前记录的某种条件，那可以用 DELETED 表，如果要检查更新后记录的某种条件，那可以用 INSERTED 表，想用什么就用什么，多方便！

 

好了，看个实际例子吧，也是坛子上有人提问的，实际上也是很经典的问题：

问1：有个借书系统，每人限借五本，超出的话不能借，如何通过触发器对借书进行限制？

问2：如果有人尚有借书，则此人不能被删除，如何用触发器对删除进行限制？

 

回复如下（表名，列名也是从问题帖子上得来的，记录是自己加的）：

 

create table usertb(userid int,借书证号 int)  
--读者表,姓名等其他信息就免了
insert into usertb select 1,11 union all select 2,22 union all select 3,33
--插入有三条记录,三个读者

create table flow(bookid int ,借书证号 int)  
--借书表,典型的纽带表,其另一主表book未写,但对本例不受影响

insert into flow 
select 2483,22 union all
select 1524,11 union all 
select 583,11 union all 
select 374,11 union all 
select 348,11 union all 
select 347,11 
--1,2两个用户都借着书,其中1号用户已借5本
go

 

--防删除触发器,尚有借书的读者不可被删除
create trigger tri_student
on usertb 
INSTEAD OF delete
as  
begin
--要到借书表中去找,假设你的 flow就是借书表:
if not exists(select 1 from flow a inner join deleted b on a.借书证号=b.借书证号)
delete from usertb where userid in(select userid from deleted)
--否则给出提示:(也可以不给出提示,在插入过程中可以用@@ROWCOUNT 判断是否有记录改变
else
select '尚有借书,不能删除!' 
end
go

 

--防超借触发器:
create trigger js on flow
INSTEAD OF INSERT 
as
begin
--在本表中查找,判断是否还能借
if not exists(select 1 from inserted a inner join flow b on a.借书证号=b.借书证号 group by a.借书证号 having count(*)>=5)
insert into flow select * from inserted
--提示
else
select '已满五本!'
end 
go

 

--测试1:删除用户
delete from usertb where userid=2
select * from usertb
/*
--删除失败,用户还在:
userid      借书证号
----------- -----------
1           11
2           22
3           33

(3 行受影响)

*/
delete from usertb where userid=3
select * from usertb
/*
--删除成功
userid      借书证号
----------- -----------
1           11
2           22

(2 行受影响)

*/

--插入借书:
insert into flow select 2344,11
select * from flow where 借书证号=11
/*
--书号 2344 插入失败,已有五条记录
bookid      借书证号
----------- -----------
1524        11
583         11
374         11
348         11
347         11

(5 行受影响)
*/
insert into flow select 165,22
select * from flow where 借书证号=22
/*
--插入成功, 书号 165 被2号借去
bookid      借书证号
----------- -----------
2483        22
165         22

(2 行受影响)

*/
go

drop table usertb,flow