触发器小问:如何将内欠过程的@@error带出?兼执行效率问题!

cadinfo 2003-09-29 07:04:45
用于插入记录表insert的存储过程如下:
我用--注释掉的语句本来是为了事务回滚,工作正常。
但是真正上线使用后发现执行效率低了非常多(临时表操作)
关键的语句在:'insert into '+@newtable+' (cpxh) values ('''+@cpxh+''')'
由于表名不定,采用exec(...),这样内部产生的@@error对于整个循环是不可见的(已测试),而不采用这种结构,执行效率非常高,然而无法回滚(或者我不知采用什么方法),来向csdn上的大侠请教,是牺牲效率还是保证程序的Robust,该如何取舍 ?
----------------------------------
触发器代码如下:
create trigger trg_insert_crjlb on crjlb with encryption for insert as
set nocount on

declare @newtable char(8),@cpbs char(3),@crbh char(2),@crsl int,@glzh varchar(8),
@count int,/*@err int,*/ @sjs varchar(15),@cpxh varchar(20),@sqlcmd varchar(800),
@tsws tinyint,@tssz tinyint,@bmlen tinyint

declare ins_cursor cursor for select cpbs,crbh,crsl,glzh from inserted
open ins_cursor
fetch ins_cursor into @cpbs,@crbh,@crsl,@glzh
while @@fetch_status=0
begin
set @newtable='B'+@cpbs+@crbh
set @count=0 --set @err=0

select @tsws=tsws,@tssz=tssz,@bmlen=bmlen from CPJLB where cpbs=@cpbs
set @tsws=@tsws-6
set @bmlen=@bmlen-5

--容错,先清理
set @sqlcmd='if exists (select * from sysobjects where name='''+@newtable+''' and xtype=''U'')
drop table '+@newtable
exec(@sqlcmd)

set @sqlcmd='create table '+@newtable+' (
cpxh varchar(20) collate chinese_prc_ci_as not null primary key,
cxsj datetime null,
ldxs varchar(18) collate chinese_prc_ci_as null
) on KDA_FG'+@cpbs
exec(@sqlcmd)

--利用触发器,自动创建查询日志功能---------------
set @sqlcmd='create trigger trg_update_'+@newtable+' on '+@newtable+' with encryption for update as
declare @cpbs varchar(3),@crbh varchar(2),@cpxh varchar(20),@cxsj datetime,@ldxs varchar(15)
if update(cxsj)
begin
select @cpbs='''+@cpbs+''',@crbh='''+@crbh+''',@cpxh=cpxh,@cxsj=cxsj,@ldxs=ldxs from inserted
insert into cxjlb (cpbs,crbh,cpxh,cxsj,ldxs) values (''''+@cpbs+'''',''''+@crbh+'''',''''+@cpxh+'''',@cxsj,''''+@ldxs+'''')
end'
exec(@sqlcmd)
------------------------
--屏蔽容错的原因在于生成编码的速度比正常要慢5~6倍
-- if object_id('tempdb..#errtbl') is not null drop table #errtbl --容错
-- create table #errtbl (errno int not null)
-- insert into #errtbl (errno) values (0)

begin transaction --added later
--batch insert crsl
while @count<@crsl --and @err=0
begin
exec sp_sjs @tsws,@tssz,@bmlen,@sjs output --外部核心存储过程,生成加密序列
set @cpxh=@cpbs+@crbh+@sjs
set @sqlcmd='insert into '+@newtable+' (cpxh) values ('''+@cpxh+''')'
-- update #errtbl set errno=@@error'
exec (@sqlcmd)
-- select @err=errno from #errtbl
set @count=@count+1
end
if @count=@crsl
begin
commit transaction
--log write to czjlb
insert into czjlb (cpbs,crbh,czzh,czmc) values(@cpbs,@crbh,@glzh,'批量插入')
end
else
begin
rollback transaction
exec('drop table '+@newtable)
exec('delete from crjlb where cpbs='''+@cpbs+''' and crbh='''+@crbh+'''')
end
fetch ins_cursor into @cpbs,@crbh,@crsl,@glzh
end
close ins_cursor
deallocate ins_cursor
set nocount off
go

游标是为了形式上与delete触发器统一而作,因为delete可能同时删除多行数据!
不管是那路朋友,只要有建议都可以发言,我在此先谢过了!
...全文
45 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
cadinfo 2003-09-30
  • 打赏
  • 举报
回复
谢谢w_rose,使用exec是由于采用变量表名的原因,
触发器如果不使用transaction,那么对于在该触发器中动态生成的表是没有清除的机会的

马可提供的链接我去看过了,大力的文章显然没有经过深入思考,我会发文给他探讨,原因是他的文章中采用的 set... on直接退出触发器,后面的回滚事务还是没有调用!


好在我昨天想到一个比较折中的方法,效率比上面的要好,容错做的也不错:-)
其他热心的朋友也可以就“孤立事务导致的异常”发表自己的看法
eddiezhuo 2003-09-30
  • 打赏
  • 举报
回复
up
w_rose 2003-09-29
  • 打赏
  • 举报
回复
令搂主遗憾了,游标我没有用过。至于exec,这么多年来我只在触发器中用过一次。我相信需要用exec的地方通常用客户端来动态生成查询语句更合适,而不是在触发器中,甚至不在存储过程中。
w_rose 2003-09-29
  • 打赏
  • 举报
回复
触发器当中是每有必要包含begin transaction之类的语句的。因为它们来就工作在事务中,一旦出错,事务回滚,一直滚到激活触发器的外部代码,所以触发器里边还要写事务时多余的。
w_rose 2003-09-29
  • 打赏
  • 举报
回复
上边的代码全是同一个触发器中的么?实在看不过来。我的触发器中通常只有几行代码。再好将“触发代码”分散写到多个触发器中去。例如,一个记录被改变,可能需要备份、记账、通知客户、激活其它记录等操作,完全可以分开写道5、6个触发器中,完全不用管它们谁先被触发,说后被触发。这样“离散”的触发器设计,可以使得设计变得很小、很准确。
cadinfo 2003-09-29
  • 打赏
  • 举报
回复
先行谢过,我去学习了
txlicenhe 2003-09-29
  • 打赏
  • 举报
回复
http://expert.csdn.net/Expert/TopicView1.asp?id=1675986
全接触SQL异常与孤立事务!

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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