34,590
社区成员
发帖
与我相关
我的任务
分享
-- 触类旁通
if OBJECT_ID('S') is not null
drop table s;
go
create table S (id int identity primary key, c1 varchar(20),c2 varchar(20));
go
insert into s(c1,c2) values(null,'c2');
insert into s(c1,c2) values('c1','c2');
go
create trigger trg_upd_c on s
instead of update
as
if update(c1)
begin
;with t as(
select i.id,i.c1 ic1,i.c2 ic2,d.c1 dc1 from inserted i,deleted d where i.id=d.id
)
update s set c2=
(case when t.dc1 is not null and t.ic1 is null then convert(varchar(20),getdate(),120)
when t.dc1 is null and t.ic1 is not null then null
else t.ic2 end), c1=t.ic1
from s,t where s.id=t.id;
end
go
update s set c1='c1' where id=1;
update s set c1=null where id=2;
select * from s;
-- 要求 s 表上必须有主键,用于唯一标示行
if OBJECT_ID('S') is not null
drop table s;
go
create table S (id int identity primary key, c datetime);
go
insert into s(c) values(null);
insert into s(c) values(GETDATE()-1);
go
create trigger trg_upd_c on s
instead of update
as
if update(c)
begin
;with t as(
select i.id,i.c ic,d.c dc from inserted i,deleted d where i.id=d.id
)
update s set c=
(case when t.dc is not null and t.ic is null then getdate()
when t.dc is null and t.ic is not null then null
else t.ic end)
from s,t where s.id=t.id;
end
go
update s set c=getdate() where id=1;
update s set c=null where id=2;
select * from s;