22,210
社区成员
发帖
与我相关
我的任务
分享
if not exists(select 1 from tb where b = 某变量)
insert into tb ...
else
update tb set ....
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int identity(1,1),[b] varchar(2),[c] varchar(3))
insert [tb]
select 'f1','uuu' union all
select 'f2','ooo' union all
select 'f3','ttt'
---创建触发器---
create trigger tri_tb
on tb
instead of insert
as
begin
if exists(select 1 from inserted i,tb t where i.b=t.b)
update tb set tb.c=i.c from inserted i where tb.b=i.b
else
insert tb select b,c from inserted
end
---插入---
insert into tb values('f3','iii')
insert into tb values('f4','jjj')
---查询---
select * from [tb]
---结果---
a b c
----------- ---- ----
1 f1 uuu
2 f2 ooo
3 f3 iii
4 f4 jjj
(4 行受影响)
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] int identity(1,1),[b] varchar(2),[c] varchar(3))
insert [tb]
select 'f1','uuu' union all
select 'f2','ooo' union all
select 'f3','ttt'
---创建触发器---
create trigger tri_tb
on tb
instead of insert
as
begin
if exists(select 1 from inserted i,tb t where i.b=t.b)
update tb set tb.c=i.c from inserted i where tb.b=i.b
end
---插入---
insert into tb values('f3','iii')
---查询---
select * from [tb]
---结果---
a b c
----------- ---- ----
1 f1 uuu
2 f2 ooo
3 f3 iii
(3 行受影响)