化简SQL语句

leonatcs 2006-05-26 11:43:42
本人初学SQL语法,下面是我写的触发器,请教怎样从语句上简化一下。

CREATE TRIGGER identical_insert ON ch_mon
instead of INSERT
AS
if @@rowcount=0 return
declare @tbl varchar(64),@recno bigint,@itype varchar(64),@otype varchar(64)
select @tbl=tbl,@recno=recno,@itype=type from inserted
if not exists(select 1 from ch_mon where recno=@recno and tbl=@tbl)
begin
insert into ch_mon select * from inserted
return
end
select @otype=type from ch_mon where recno=@recno and tbl=@tbl
if @otype='insert' and @itype='delete'
begin
delete from ch_mon where recno=@recno and tbl=@tbl
return
end
if @otype='update' and @itype='delete'
update ch_mon set type='delete'where recno=@recno and tbl=@tbl
go
...全文
133 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
paoluo 2006-05-27
  • 打赏
  • 举报
回复
try
CREATE TRIGGER identical_insert ON ch_mon
instead of INSERT
AS
Begin
if @@rowcount=0 return
Insert into ch_mon select * from inserted A Where Not Exists((select 1 from ch_mon where recno=A.recno and tbl=A.tbl)
delete A from ch_mon A Inner Join inserted B On A.recno=B.recno And A.tbl=B.tbl where A.type='insert' and B.type='delete'
update A set type='delete' From ch_mon A Inner Join inserted B On A.recno=B.recno And A.tbl=B.tbl where A.type='update' and B.type='delete'
End
GO
leonatcs 2006-05-27
  • 打赏
  • 举报
回复
THNX!

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧