[url]http://topic.csdn.net/t/20041028/09/3498141.html[/url]
有A、B两表。B表中记录取自A表
A:
ID(PK), Name, Sex, Job, Address, ….
B:
ID(PK), Name, Address
现想在A上建立触发器,当A中记录发生增加、某个字段(Address)发生更新、记录删除时,对B中的记录进行相应的增加、更新和删除的工作。请问邹建大哥及各位老大,是写成单个触发器还是写成三个触发器好呢?如果写成单个触发器,请问这个触发器怎么写? 我碰到的问题是不知如何在触发器中区分三个不同的操作。请大家帮忙。。。
--写成三个吧,免得在触发器中去判断属于那种操作
--新增同步
create trigger tr_insert_A on A
for insert
as
insert B(ID,Name,Address) select ID,Name,Address from inserted
go
--删除同步
create trigger tr_delete_A on A
for delete
as
delete B from deleted d where B.ID=d.ID
go
--更新同步
create trigger tr_update_A on A
for update
as
if update(Name) or update(Address)
update B set Name=i.Name,Address=i.Address
from B,inserted i,deleted d
where i.ID=d.ID and i.ID=b.ID
谢谢邹建大哥。再问一下,三个触发器与单个触发器相比,性能上会有所不如吗?
另外,如果是这样的两个表,我又应该怎样做呢?呵呵,主要A的关键字段变了
有A、B两表。B表中记录取自A表
A:
Name(PK), Sex, Job, Address, ….
B:
ID(PK), Name, Address
很少来SQL SERVER版 ,不过邹建大名可是如雷贯耳,看你们帖子真长知识呀!!!
请教写成一个触发器怎么写?
某个字段(Address)发生更新
create trigger tr_update_A on A
for update
as
if update(Address)
begin
update B set Name=i.Name,Address=i.Address
from B,inserted i,deleted d where i.ID=d.ID and i.ID=b.ID
end
go
删除
create trigger tr_delete_A on A
for delete
as
delete B from deleted d where B.ID=d.ID
go
俺不是老大,首先声明~
关键字变了无所谓的。
看你的id是自增字段,还是一定长度的编号了。
如是自增字段,你可以这样写:
--新增同步
create trigger tr_insert_A on A
for insert
as
insert B(Name,Address) select Name,Address from inserted
go
--删除同步
create trigger tr_delete_A on A
for delete
as
delete B from deleted d where B.name=d.name
go
--更新同步
create trigger tr_update_A on A
for update
as
if update(Address)
update B set Address=i.Address
from B,inserted i,deleted d
where i.name=d.name and i.name=b.name
如id字段是按某种规则生成的编号,基本一样,只要在增加时,生成编号就可以了。
--写成一个触发器要判断当前操作是什么类型,所以在效率上没有三个好
--写成一个触发器,顺便体现你最后的表的解决方法
create trigger tr_insert_update_delete_A on A
for insert,update,delete
as
if not exists(select * from deleted) --如果是新增
insert B(Name,Address) select Name,Address from inserted
else if not exists(select * from inserted) --如果是删除
delete B from deleted d where B.Name=d.Name
else if update(Name) or update(Address) --如果是更新
update B set Name=i.Name,Address=i.Address
from B,inserted i,deleted d
where i.Name=d.Name and i.Name=b.Name
邹建大哥写的触发器Insert和Delete都没有问题,但是在Update情况下会有如下问题:A表的关键字段Name更新时,B表的Name字段无法更新!!这个问题要怎么解决呢??
create trigger tr_insert_update_delete_A on A
for insert,update,delete
as
declare @r int
set @r=@@rowcount
if @r=0 return
if not exists(select * from deleted) --如果是新增
insert B(Name,Address) select Name,Address from inserted
else if not exists(select * from inserted) --如果是删除
delete B from deleted d where B.Name=d.Name
else if update(Name) and @r>1 --如果更新了Name,则不允许多行,否则不能实现同步
begin
raiserror('不允许同时更新多条记录的主键值,更新被取消!',1,16)
rollback tran
return
end
else if update(Name)
update B set Name=i.Name,Address=i.Address
from B,inserted i,deleted d
where B.Name=D.Name
else if update(Address)
update B set Name=i.Name,Address=i.Address
from B,inserted i,deleted d
where B.Name=D.Name and i.Name=d.Name