34,576
社区成员
发帖
与我相关
我的任务
分享
create trigger tr_T on T
after update
as
update T
set D=0
from inserted i
where i.ID=t.ID and i.D<>0 and t.D <>0
create trigger tr_table_update
on [table]
for update
as
update a
set D=0
from [table] a,inserted i,deleted d
where a.key<>0
and a.key=d.key
and i.d<>0
and d.d<>0
go
create trigger tr_wsp on 表名 for update
as
if exists(select 1 from inserted where D<>0) and exists(select 1 from deleted where D<>0)
begin
update 表名 set D=0 from 表名 a,inserted b where a.主键=b.主键
end
drop table tb
create table tb(id int,a int,b int,c int,d int)
insert into tb select 1,0,1,2,3
insert into tb select 2,2,1,2,1
insert into tb select 3,2,1,2,0
create trigger trigger_tb
on tb
instead of update
as
begin
if update(d)
begin
declare @old int,@new int
select @old=d from deleted
select @new=d from inserted
if @old<>0 and @new<>0
update tb set d=0 from tb,inserted where tb.id=inserted.id
end
end
update tb set d=1 where id=2
select * from tb
注意key是主键,如果是符合主键,比如(a,b)
where a.key=i.key
and a.key=d.key
改成
where a.a=i.a and a.b=i.b
and a.a=d.a and a.b=d.b
create trigger tr_table_update
on [table]
for update
as
update a
set D=0
from [table] a,inserted i,deleted d
where a.key=i.key
and a.key=d.key
and i.d<>0
and d.d<>0
go