create trigger tr_tab1_update
on tab1
for update
asinsert tab2(name,status)
select i.name,i.status
from inserted i,deleted d
where i.status=2
and d.status<>2
and i.name=d.namego
on tab1
for update
asinsert tab2(name,status)
select i.name,i.status
from inserted i,deleted d
where i.status=2
and d.status<>2
and i.name=d.namego
as
delete tab2 from inserted a,tab2 b where a.name=b.name and a.status=2
insert into tab2(name,status) select name,status from inserted where status=2
go
insert into tb1 values('a' , 0)
insert into tb1 values('b' , 1)
insert into tb1 values('c' , 2)
create table tb2(name varchar(10),status int)
insert into tb2 values('a' , 0)
insert into tb2 values('b' , 0)
insert into tb2 values('c' , 0)
go
--创建触发器
create trigger my_trig on tb1 for update
as
begin
update tb2 set status = n.status from tb2 m, inserted n where m.name = n.name and n.status = 2
end
go--原始数据
select * from tb1
select * from tb2
/*
name status
---------- -----------
a 0
b 1
c 2(所影响的行数为 3 行)name status
---------- -----------
a 0
b 0
c 0(所影响的行数为 3 行)
*/update tb1 set status = 2 where name = 'b'--更改后的数据
select * from tb1
select * from tb2
/*
name status
---------- -----------
a 0
b 2
c 2(所影响的行数为 3 行)name status
---------- -----------
a 0
b 2
c 0(所影响的行数为 3 行)
*/drop table tb1,tb2
总是觉得你的问题有点矛盾.