---测试数据---
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 行受影响)
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 行受影响)
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 行受影响)
insert into tb ...
else
update tb set ....
if(select count(b) from inserted)>0
begin
update tab set tab.c=inserted.c from tab,inserted
where tab.b=inserted.b;
end;
insert into tab values('f3','iii');