22,210
社区成员
发帖
与我相关
我的任务
分享
BEGIN TRAN --开启事务
update [表t1] set tel='XXXX',addreee = 'XXXY' --自己给更新条件
where userkey ='XXXX' and tel ='020-1234567' and addreee ='北京中路'
if @@rowcount=0
begin
insert into [表t1] values ('0004', '张六', '020-25698', '北京西路') --自己给插入数据
end
COMMIT TRAN
BEGIN TRAN --开启事务
--判断条件是否成立
if exists(select * from [表t1] a where userkey ='XXXX' ) --判断条件自己给
begin
update [表t1] set tel='XXXX',addreee = 'XXXY' --自己给更新条件
where userkey ='XXXX' and tel ='020-1234567' and addreee ='北京中路'
end
else
begin
insert into [表t1] values ('0004', '张六', '020-25698', '北京西路') --自己给插入数据
end
COMMIT TRAN
--> 测试数据:test
if object_id('test') is not null drop table test
create table test(userkey varchar(8), username varchar(8), tel varchar(11), addreee varchar(8))
insert into test
select '0001', '张三', '020-5689121', '北京一路' union all
select '0002', '李四', '020-5689121', '北京二路' union all
select '0003', '王五', '020-5689121', '北京三路'
go
--> trigger
create trigger tr_test on test
instead of insert
as
set nocount on
update t set t.username=i.username, t.tel=i.tel, t.addreee=i.addreee from test t join inserted i on t.userkey=i.userkey
insert test select * from inserted i where not exists (select 1 from test where userkey=i.userkey)
set nocount off
go
--> insert
insert into test
select '0001', '张三', '020-1234567', '北京中路' union all
select '0004', '张六', '020-25698', '北京西路'
select * from test
/*
userkey username tel addreee
-------- -------- ----------- --------
0001 张三 020-1234567 北京中路
0002 李四 020-5689121 北京二路
0003 王五 020-5689121 北京三路
0004 张六 020-25698 北京西路
*/