if exists (select name from sysobjects where name = 'update_jjsj') drop trigger update_jjsj GOCREATE trigger update_jjsj on dbo.jjsj for update AS --定义变量:@flag|当前插入记录的rc_id;@uid|当前插入记录的unit_id;@nd|当前插入记录的年度 --@snzsr|上年总收入;@bnpjrs|本年平均人数;@snlrze|上年利润总额 declare @flag int,@uid char(6),@nd char(4),@snzsr decimal(10,2),@bnpjrs int,@snlrze decimal select @flag = rc_id from jjsj select @nd = rc_nd from jjsj select @uid = unit_id from jjsj --前提每单位每年只能输入一次经济数据 select @snzsr = (select rc_zsr from jjsj where unit_id = @uid and rc_nd = @nd -1) select @bnpjrs = (select rc_pjrs from ryxx where unit_id = @uid and rc_nd = @nd) select @snlrze = (select rc_lrze from jjsj where unit_id = @uid and rc_nd = @nd -1)--对经济数据表的计算字段进行更新 update jjsj set -------------------------------------------------------------------------- --计算总收入 rc_zsr = rc_jzy+rc_sy+rc_ysy+rc_gy+rc_fwy+rc_qt, --成本总额 rc_cbze = rc_xscb+rc_xsfy+rc_glfy+rc_cwfy+rc_xxsj+rc_sds+rc_yywzc, --增加值 rc_zjz = rc_zj+rc_yy+rc_bc+rc_jse, --所有者权益 rc_qy = rc_sszb+rc_zbgj+rc_yygj+rc_wfplr, -------------------------------------------------------------------------- --总收入增长率 rc_zzj = case when isnull(@snzsr,0) = 0 then 0 else cast((rc_zzc-@snzsr)/@snzsr*100 as decimal(10,1)) end, --计算资本增值保值 rc_zbbzh = case when isnull(rc_qcqy,0) =0 then 0 else cast(rc_qmqy/rc_qcqy*100 as decimal(10,1)) end, --全员劳动生产率 rc_qyscl = case when isnull(@bnpjrs,0) =0 then 0 else cast(rc_zjz/@bnpjrs as decimal(10,1)) end, --资产负债率 rc_fzl = case when isnull(rc_zfz,0) =0 then 0 else cast(rc_zfz/rc_zzc*100 as decimal(10,1)) end, --流动比率 rc_ldbl = case when isnull(rc_ldfz,0) =0 then 0 else cast(rc_ldzc/rc_ldfz*100 as decimal(10,1)) end, --利润率 rc_lrl = case when isnull(rc_zsr,0) =0 then 0 else cast(rc_lrze/rc_zsr*100 as decimal(10,1)) end, --利润增长率 rc_lrzzl = case when isnull(@snlrze,0) =0 then 0 else cast((rc_lrze-@snlrze)/@snlrze*100 as decimal(10,1)) end, --净资产收益率 rc_jzcsy = case when rc_qcqy+rc_qmqy = 0 then 0 else cast(rc_jlr/((rc_qcqy+rc_qmqy)/2)*100 as decimal(10,1)) end, --总资产收益率 rc_zzcsyl = case when isnull(rc_zzc,0) =0 then 0 else cast(rc_jlr/rc_zzc*100 as decimal(10,1)) end where rc_id = @flagGO
但update 數據時會觸發insert觸發器
应该不是吧,update触发器确实激活了,我用事件探查器看到了2条update的记录,但对计算字段却没有做出任何操作,仅仅把前台修改的部分update了一遍
这句不太明白,如果update的时候触发了insert的触发器的话,那数据应该被修改的
drop trigger update_jjsj
GOCREATE trigger update_jjsj
on dbo.jjsj
for update
AS
--定义变量:@flag|当前插入记录的rc_id;@uid|当前插入记录的unit_id;@nd|当前插入记录的年度
--@snzsr|上年总收入;@bnpjrs|本年平均人数;@snlrze|上年利润总额
declare @flag int,@uid char(6),@nd char(4),@snzsr decimal(10,2),@bnpjrs int,@snlrze decimal select @flag = rc_id from jjsj
select @nd = rc_nd from jjsj
select @uid = unit_id from jjsj
--前提每单位每年只能输入一次经济数据
select @snzsr = (select rc_zsr from jjsj where unit_id = @uid and rc_nd = @nd -1)
select @bnpjrs = (select rc_pjrs from ryxx where unit_id = @uid and rc_nd = @nd)
select @snlrze = (select rc_lrze from jjsj where unit_id = @uid and rc_nd = @nd -1)--对经济数据表的计算字段进行更新
update jjsj set
--------------------------------------------------------------------------
--计算总收入
rc_zsr = rc_jzy+rc_sy+rc_ysy+rc_gy+rc_fwy+rc_qt,
--成本总额
rc_cbze = rc_xscb+rc_xsfy+rc_glfy+rc_cwfy+rc_xxsj+rc_sds+rc_yywzc,
--增加值
rc_zjz = rc_zj+rc_yy+rc_bc+rc_jse,
--所有者权益
rc_qy = rc_sszb+rc_zbgj+rc_yygj+rc_wfplr,
--------------------------------------------------------------------------
--总收入增长率
rc_zzj = case when isnull(@snzsr,0) = 0 then 0
else cast((rc_zzc-@snzsr)/@snzsr*100 as decimal(10,1)) end,
--计算资本增值保值
rc_zbbzh = case when isnull(rc_qcqy,0) =0 then 0
else cast(rc_qmqy/rc_qcqy*100 as decimal(10,1)) end,
--全员劳动生产率
rc_qyscl = case when isnull(@bnpjrs,0) =0 then 0
else cast(rc_zjz/@bnpjrs as decimal(10,1)) end,
--资产负债率
rc_fzl = case when isnull(rc_zfz,0) =0 then 0
else cast(rc_zfz/rc_zzc*100 as decimal(10,1)) end,
--流动比率
rc_ldbl = case when isnull(rc_ldfz,0) =0 then 0
else cast(rc_ldzc/rc_ldfz*100 as decimal(10,1)) end,
--利润率
rc_lrl = case when isnull(rc_zsr,0) =0 then 0
else cast(rc_lrze/rc_zsr*100 as decimal(10,1)) end,
--利润增长率
rc_lrzzl = case when isnull(@snlrze,0) =0 then 0
else cast((rc_lrze-@snlrze)/@snlrze*100 as decimal(10,1)) end,
--净资产收益率
rc_jzcsy = case when rc_qcqy+rc_qmqy = 0 then 0
else cast(rc_jlr/((rc_qcqy+rc_qmqy)/2)*100 as decimal(10,1)) end,
--总资产收益率
rc_zzcsyl = case when isnull(rc_zzc,0) =0 then 0
else cast(rc_jlr/rc_zzc*100 as decimal(10,1)) end
where rc_id = @flagGO