SQL SERVER使用嵌套触发器
2024-08-31 00:49:27
供稿：网友
，欢迎访问网页设计爱好者web开发。如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器……这些触发器就是嵌套触发器。触发器可嵌套至 32 层，并且可以控制是否可以通过"嵌套触发器"服务器配置选项进行触发器嵌套。
如果允许使用嵌套触发器，且链中的一个触发器开始一个无限循环，则超出嵌套级，而且触发器将终止。
可使用嵌套触发器执行一些有用的日常工作，如保存前一触发器所影响行的一个备份。例如，可以在 titleauthor 上创建一个触发器，以保存由 delcascadetrig 触发器所删除的 titleauthor 行的备份。在使用 delcascadetrig 时，从 titles 中删除title_id ps2091 将删除 titleauthor 中相应的一行或多行。要保存数据，可在 titleauthor 上创建 delete 触发器，该触发器的作用是将被删除的数据保存到另一个单独创建的名为 del_save 表中。例如：
create trigger savedel
on titleauthor
for delete
as
insert del_save
select * from deleted
不推荐按依赖于顺序的序列使用嵌套触发器。应使用单独的触发器层叠数据修改。
说明 由于触发器在事务中执行，如果在一系列嵌套触发器的任意层中发生错误，则整个事务都将取消，且所有的数据修改都将回滚。在触发器中包含 print 语句，用以确定错误发生的位置。
递归触发器
触发器不会以递归方式自行调用，除非设置了 recursive_triggers 数据库选项。有两种不同的递归方式：
直接递归
即触发器激发并执行一个操作，而该操作又使同一个触发器再次激发。例如，一应用程序更新了表 t3，从而引发触发器 trig3。trig3 再次更新表 t3，使触发器 trig3 再次被引发。
间接递归
即触发器激发并执行一个操作，而该操作又使另一个表中的某个触发器激发。第二个触发器使原始表得到更新，从而再次引发第一个触发器。例如，一应用程序更新了表 t1，并引发触发器 trig1。trig1 更新表 t2，从而使触发器 trig2 被引发。trig2 转而更新表 t1，从而使 trig1 再次被引发。
当将 recursive_triggers 数据库选项设置为 off 时，仅防止直接递归。若要也禁用间接递归，请将 nested triggers 服务器选项设置为 0。
示例
a. 使用递归触发器解决自引用关系
递归触发器的一种用法是用于带有自引用关系的表（亦称为传递闭包）。例如，表 emp_mgr 定义了：
一个公司的雇员 (emp)。
每个雇员的经理 (mgr)。
组织树中向每个经理汇报的雇员总数 (noofreports)。
递归 update 触发器在插入新雇员记录的情况下可以使 noofreports 列保持最新。insert 触发器更新经理记录的 noofreports 列，而该操作递归更新管理层向上其它记录的 noofreports 列。
use pubs
go
-- turn recursive triggers on in the database.
alter database pubs
set recursive_triggers on
go
create table emp_mgr (
emp char(30) primary key,
mgr char(30) null foreign key references emp_mgr(emp),
noofreports int default 0
)
go
create trigger emp_mgrins on emp_mgr
for insert
as
declare @e char(30), @m char(30)
declare c1 cursor for
select emp_mgr.emp
from emp_mgr, inserted
where emp_mgr.emp = inserted.mgr
open c1
fetch next from c1 into @e
while @@fetch_status = 0
begin
update emp_mgr
set emp_mgr.noofreports = emp_mgr.noofreports + 1 -- add 1 for newly
where emp_mgr.emp = @e -- added employee.
fetch next from c1 into @e
end
close c1
deallocate c1
go
-- this recursive update trigger works assuming:
-- 1. only singleton updates on emp_mgr.
-- 2. no inserts in the middle of the org tree.
create trigger emp_mgrupd on emp_mgr for update
as
if update (mgr)
begin
update emp_mgr
set emp_mgr.noofreports = emp_mgr.noofreports + 1 -- increment mgr's
from inserted -- (no. of reports) by
where emp_mgr.emp = inserted.mgr -- 1 for the new report.
update emp_mgr
set emp_mgr.noofreports = emp_mgr.noofreports - 1 -- decrement mgr's
from deleted -- (no. of reports) by 1
where emp_mgr.emp = deleted.mgr -- for the new report.
end
go
-- insert some test data rows.
insert emp_mgr(emp, mgr) values ('harry', null)
insert emp_mgr(emp, mgr) values ('alice', 'harry')
insert emp_mgr(emp, mgr) values ('paul', 'alice')
insert emp_mgr(emp, mgr) values ('joe', 'alice')
insert emp_mgr(emp, mgr) values ('dave', 'joe')
go
select * from emp_mgr
go
-- change dave's manager from joe to harry
update emp_mgr set mgr = 'harry'
where emp = 'dave'
go
select * from emp_mgr
go
以下是更新前的结果：
emp mgr noofreports
------------------------------ ----------------------------- -----------
alice harry 2
dave joe 0
harry null 1
joe alice 1
paul alice 0
以下为更新后的结果：
emp mgr noofreports
------------------------------ ----------------------------- -----------
alice harry 2
dave harry 0
harry null 2
joe alice 0
paul alice 0
