关于|关于 INSTEAD OF 触发器

快生三星了,好歹也得在CSDN上留点什么以示水平不低吧,但总是不知道该写什么,只善于回答问题,不善于总结问题。
近来在坛子上经常看到有问触发器的,而且大多是要求实现在一定条件下不向表中进行操作的触发器,可大多数都不知道该用 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

【关于|关于 INSTEAD OF 触发器】--防删除触发器,尚有借书的读者不可被删除
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借书证号
----------- -----------
111
222
333
(3 行受影响)
*/
delete from usertb where userid=3
select * from usertb
/*
--删除成功
userid借书证号
----------- -----------
111
222
(2 行受影响)
*/
--插入借书:
insert into flow select 2344,11
select * from flow where 借书证号=11
/*
--书号 2344 插入失败,已有五条记录
bookid借书证号
----------- -----------
152411
58311
37411
34811
34711
(5 行受影响)
*/
insert into flow select 165,22
select * from flow where 借书证号=22
/*
--插入成功, 书号 165 被2号借去
bookid借书证号
----------- -----------
248322
16522
(2 行受影响)
*/
go

drop table usertb,flow


没想到更新的例子,其实可以参照上面的。

玩计算机几十年了(N>1就算是“几”吧?),第一次写博客。初生博客,围观者不要笑话俺。。。

2011。1。11+2

    推荐阅读