保持两表数据一致的触发器事例

保持两表数据一致的触发器事例

from:http://blog.youkuaiyun.com/Haiwer/archive/2007/07/30/1717206.aspx

触发器一个重要的作用是在修改一个表的数据的同时可以修改另一个表的数据，这里所说的修改是指包括插入、更新和删除操作。但是，在编写触发器代码的时候，需要考虑各种可能的情况，使得触发器代码变得健壮，可以适应未来的各种变化。
一般来说，需要考虑以下情况：
1、一次修改多条记录的情况，无论是插入、删除还是修改，都有可能一次操作多条记录，所以触发器中应该避免使用变量来取得inserted、deleted临时表的字段值,因为这样取值很可能只取得一条记录的字段值，造成其他记录未处理，而且这种错误在语法上没有任何报错，在大部分应用的时候（大部分应用一次只处理一条记录）不容易发现错误，而一旦发生错误的时候，又很难找出错误所在，因为很多时候我们不会首先怀疑触发器的问题。所以个人更愿意把这类问题作为第一个问题提出。
2、低效的语句，在触发器中，更应该注意语句的效率，因为每次对这个表的操作，都会调用这个触发器。效率问题首先是需要缩小数据修改的范围，充分利用inserted、deleted临时表连接相关表来减少数据操作的记录数。其次是触发器操作的的表最好都设置主键和聚集索引，既保证数据的一致性也可以提高代码的效率。
3、递归和嵌套触发器，sql server安装的时候，默认是允许嵌套触发器和不允许直接递归触发器，有些应用可以关闭嵌套触发器选项来保证不会发生递归,但是大部分情况是需要打开嵌套触发器选项来保证数据的一致性的,关闭嵌套触发器选项是需要认真认证后才能作出的决定。在打开嵌套触发器选项的情况下，需要很好的利用代码防止无限递归触发器的发生。
4、必须注重触发器的测试，要在各种情况下（空表、有一条数据、有多条数据），各种可能的操作（插入、删除、修改、一条记录、多条记录等）都要测试。

下面以一个例子来说明编写触发器需要注意的地方，这是一个保持两个结构相同的表的数据一致的处罚其解决方案，要求任何一个表的改动在另一个表度需要作相应改动。
首先，这个事例很容易引起触发器的间接递归，间接递归是应该允许的，但是必须防止无限间接递归的情况发生。所以在触发器中操作数据的语句都增加了判断，只有在必须修改的时候才进行修改。
其次，必须注意都多行修改的情况，所以必须都是连接inserted、deleted临时表进行数据操作。

下面是代码和注释

--建表
create table T1(
id int not null PRIMARY KEY,--这里必须有主键
value int
)
go

create table T2(--这里必须有主键
id int not null PRIMARY KEY,
value int
)
go

--触发器代码
--T1 Insert 触发器
Create Trigger tr_T1_Insert
on T1
for Insert
as
set nocount on
if exists (--这个判断是防止无限递归的
select 1 from inserted i
where not exists (
select 1 from T2 where id=i.id
)
)
insert T2
select * from inserted i
where not exists (--必须有这个条件,防止重复插入
select 1 from T2 where id=i.id
)

print '1I'--报告触发器执行,只用于测试
go

--T2 Insert 触发器
Create Trigger tr_T2_Insert
on T2
for Insert
as
set nocount on
if exists (--这个判断是防止无限递归的
select 1 from inserted i
where not exists (
select 1 from T1 where id=i.id
)
)
insert T1
select * from inserted i
where not exists (--必须有这个条件,防止重复插入
select 1 from T1 where id=i.id
)
print '2I'--报告触发器执行,只用于测试
go

--T1 Update 触发器
Create Trigger tr_T1_Update
on T1
for update
as
set nocount on
if update (value)--只有修改value才触发
begin
if exists (--这个判断是防止无限递归的
select 1
from T2,Inserted i,deleted d
where i.id=d.id
and i.id=T2.id
and i.value<>d.value
)
update T2
set value=i.Value
from T2,Inserted i,deleted d
where i.id=d.id
and i.id=T2.id
and i.value<>d.value--这个条件保证只有真正修改了值才触发
end

print '1U'--报告触发器执行,只用于测试
go

--T2 Update 触发器
Create Trigger tr_T2_Update
on T2
for update
as
set nocount on
if update (value)--只有修改value才触发
begin
if exists (--这个判断是防止无限递归的
select 1
from T1,Inserted i,deleted d
where i.id=d.id
and i.id=T1.id
and i.value<>d.value
)
update T1
set value=i.Value
from T1,Inserted i,deleted d
where i.id=d.id
and i.id=T1.id
and i.value<>d.value--这个条件保证只有真正修改了值才触发
end

print '2U'--报告触发器执行,只用于测试
go

--T1 Delete 触发器
Create Trigger tr_T1_delete
on T1
for delete
as
set nocount on
if exists (--这个判断是防止无限递归的
select 1 from T2,deleted d
where T2.id=d.Id
)
delete T2
from deleted d
where T2.id=d.Id
print '1D'--报告触发器执行,只用于测试
go

--T2 Delete 触发器
Create Trigger tr_T2_delete
on T2
for delete
as
set nocount on
if exists (--这个判断是防止无限递归的
select 1 from T1,deleted d
where T1.id=d.Id
)
delete T1
from deleted d
where T1.id=d.Id
print '2D'--报告触发器执行,只用于测试
go

--测试
--测试1
insert T1 values(1,1)

--结果1
2I
1I
--这说明调用了两个触发器

--以下查看数据
select * from T1
id value
----------- -----------
1 1
select * from T2
id value
----------- -----------
1 1

--测试2
insert T2 select 2,2 union all select 3,3 --一次插入两条记录

--结果2
1I
2I
--这说明调用了两个触发器

--以下查看数据
select * from T1
id value
----------- -----------
1 1
2 2
3 3
select * from T2
id value
----------- -----------
1 1
2 2
3 3

--测试3
update T1
set value=5
where id=1

--结果3
1U
2U
1U
--这说明调用了两个触发器，其中1表的update触发器调用了两次

--以下查看数据
select * from T1
id value
----------- -----------
1 5
2 2
3 3
select * from T2
id value
----------- -----------
1 5
2 2
3 3

--测试4
update T1
set value=value+1
where id>1

--结果4
1U
2U
1U
--这说明调用了两个触发器，其中1表的update触发器调用了两次

--以下查看数据
select * from T1
id value
----------- -----------
1 5
2 3
3 4
select * from T2
id value
----------- -----------
1 5
2 3
3 4

--测试5
delete T1
where id=1

--结果5
2D
1D
--这说明调用了两个触发器

--以下查看数据
select * from T1
id value
----------- -----------
2 3
3 4
select * from T2
id value
----------- -----------
2 3
3 4

--测试6
delete T2
where id=2

--结果6
1D
2D
--这说明调用了两个触发器

--以下查看数据
select * from T1
id value
----------- -----------
3 4
select * from T2
id value
----------- -----------
3 4