例如:我现在有两个表,表A和表BA表
id c列 d列
1 aa ww
2 qq ee
B表
id A_id c列
4 1 aa
5 2 qq现在我想更新表A的C列中 id = 1的aa = bb,在更新A表的同时,B表A_id = 1 的C列也同时更新为 aa = bb
id c列 d列
1 aa ww
2 qq ee
B表
id A_id c列
4 1 aa
5 2 qq现在我想更新表A的C列中 id = 1的aa = bb,在更新A表的同时,B表A_id = 1 的C列也同时更新为 aa = bb
after update
as
begin update B
set colc=bb
where A_id in (select id from deleted)
end
update B set c='bb' where A_id=1
for update
as
begin update B
set colc='bb'
where A_id in (select id from deleted)
end
update a set c列='bb' where id=1
update b set c列='bb' where a_id=1用触发器:
create trigger tria on a
for update
as
if update(c列)
update b set c列=aa.c列 from inserted aa where aa.id=a_id
after update
as
begin update B
set colc='bb'
from B a ,deleted b
where a.id=b.A_id
end
set nocount on
create table b(id varchar(20),A_id varchar(20),c列 varchar(20))
insert into b select '4','1','aa'
insert into b select '5','2','qq'
create table a(id varchar(20),c列 varchar(20),d列 varchar(20))
insert into a select '1','aa','ww'
insert into a select '2','qq','ee'
go
--测试
create trigger test on b
for update
as
begin
update a set c列=i.c列
from inserted i inner join a on A_id=a.id
end
go
update b set c列='bb' where A_id='1'
select * from a
--删除测试环境
drop table b
drop table a
set nocount off/*
1 bb ww
2 qq ee
*/
after update
as
upate b
set c列=i.c列
from inserted i join b on i.ID=b.A_id