27,579
社区成员
发帖
与我相关
我的任务
分享
--> 如果UPDATE字段B 并且新值在字段A中没有
if update(b) and not exists(select 1 from ta a,inserted b where b.id=a.id and charindex(','+ltrim(b.b)+',',','+a.a+',')>0)
begin
update ta set a =
--> 判断字段A的逗号数目
case len(a.a)-len(replace(a.a,',',''))
--> 4个逗号(五项):新值+','+前4项
when 4 then ltrim(b.b) + ',' + reverse(stuff(reverse(a.a),1,charindex(',',reverse(a.a)),''))
--> 否则:新值+','+字段A
else ltrim(b.b)+isnull(','+a.a,'')
end
from ta a,inserted b where a.id=b.id
end
create table ta(id int identity(1,1),a varchar(50),b int)
go
create trigger tri_ta on ta for insert,update
as
if not exists(select 1 from ta a,inserted b where b.id=a.id and charindex(','+ltrim(b.b)+',',','+a.a+',')>0)
and exists(select 1 from ta a,inserted b where b.id=a.id and (len(a.a)-len(replace(a.a,',',''))<4 or isnull(a.a,'')=''))
update ta set a=ltrim(b.b)+isnull(','+a.a,'') from ta a,inserted b where a.id=b.id
go
--测试:
insert into ta(b) select 1
select * from ta
update ta set b=5 where id=1
select * from ta
update ta set b=5 where id=1
select * from ta
update ta set b=4 where id=1
select * from ta
update ta set b=3 where id=1
select * from ta
update ta set b=64 where id=1
select * from ta
update ta set b=8 where id=1
select * from ta
--删除测试
drop table ta
create table ta(id int identity(1,1),a varchar(50),b int)
go
create trigger tri_ta on ta for insert,update
as
if update(b) and not exists(select 1 from ta a,inserted b where b.id=a.id and charindex(','+ltrim(b.b)+',',','+a.a+',')>0)
begin
update ta set a =
case len(a.a)-len(replace(a.a,',',''))
when 4 then ltrim(b.b) + ',' + reverse(stuff(reverse(a.a),1,charindex(',',reverse(a.a)),''))
else ltrim(b.b)+isnull(','+a.a,'')
end
from ta a,inserted b where a.id=b.id
end
go
--测试:
insert into ta(b) select 1
select * from ta
update ta set b=5 where id=1
select * from ta
update ta set b=5 where id=1
select * from ta
update ta set b=4 where id=1
select * from ta
update ta set b=3 where id=1
select * from ta
update ta set b=64 where id=1
select * from ta
update ta set b=8 where id=1
select * from ta
--删除测试
drop table ta
create trigger up_tb
on tb
for update
as
begin
if exists(select 1 from inserted where a='') -----a为空的情况
begin
update a
set a.A=(case when b.B=1 then '1'
when b.B=2 then '1,2'
else null end)
from tb a, inserted b
where a.id=b.id
end
else
begin
.........和上面的类似。
end
end