触发器小问:如何将内欠过程的@@error带出?兼执行效率问题!
用于插入记录表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可能同时删除多行数据!
不管是那路朋友,只要有建议都可以发言,我在此先谢过了!