34,590
社区成员
发帖
与我相关
我的任务
分享
Update dbo.PartOpr
Set 字段=值
-- 存在記錄才更新
Where OperationDesc=@OperationDesc
and TopVersion=@TopVersion
and DrawNum=@DrawNum
and TopPartNum=@TopPartNum
and [Version]=@Version
Insert into dbo.PartOpr(字段)
Select
字段
-- 不存在記錄才插入
Where Not exists(select 1 from dbo.PartOpr
Where OperationDesc=@OperationDesc
and TopVersion=@TopVersion
and DrawNum=@DrawNum
and TopPartNum=@TopPartNum
and [Version]=@Version
)
--好久没写触发器了 写个练练手
if OBJECT_ID('tbl') is not null
drop table tbl
go
create table tbl
(
id int identity,
StuNo varchar(20),
StuName varchar(20),
age smallint
)
go
insert tbl(StuNo,StuName,age)
select '200911076','tracy',22 union all
select '200911077','kobe',21 union all
select '200911078','nash',23 union all
select '200911079','lucy',22 union all
select '200911080','lily',21
go
if OBJECT_ID('tr_test') is not null
drop trigger tr_test
go
create trigger tr_test on tbl
instead of insert
as
--插入的数据都会放在inserted这个临时表中,不管是tbl存在的或者不存在的
--所以 我们只需把存在的更新过去 不存在的插入就好
--先更新原有的
update tbl
set tbl.StuName=i.StuName,tbl.age=i.age
from inserted i where i.StuNo=tbl.StuNo
--再插入不存在的
insert tbl(StuNo,StuName,age)
select StuNo,StuName,age from inserted a
where not exists(select 1 from tbl i where a.StuNo=i.StuNo)
go
insert tbl(StuNo,StuName,age)
select '200911076','tracy mcgrady',30 union all
select '200911081','david',23
select * from tbl
/*
id StuNo StuName age
------------------------------------------------------------
1 200911076 tracy mcgrady 30
2 200911077 kobe 21
3 200911078 nash 23
4 200911079 lucy 22
5 200911080 lily 21
6 200911081 david 23
*/
1、--处理的触发器示例
create trigger tr_insert on 表
instead of insert --注意触发器的类型
as
--更新已经存在的主键
update 表 set name=b.name,sex=b.sex
from 表 a join inserted b on a.id=b.id
--插入存在的主键数据
insert 表
select a.*
from inserted a left join 表 b on a.id=b.id
where b.id is null
go
——————————————————————————————————————————
2、--触发器
CREATE TRIGGER tri_edit ON tab
INSTEAD OF INSERT
AS
if exists(select col1,col2 from tab join inserted on tab.学号=INSERTED.学号)
begin
--这里面你可以加如些其他修改操作,取决于具体的功能
update tab set col1='num1' from tab join inserted on tab.学号=inserted.学号
end
else
insert tab select * from inserted
GO