update xt_bm set 你要更新的字段=tem.sign吗? from inserted as tem where tem.bm=xt_bm.bm and tem.sign='情况1'
update xt_bm set 你要更新的字段=tem.sign吗? from inserted as tem where tem.bm=xt_bm.bm and tem.sign='情况2'
update xt_bm set 你要更新的字段=tem.sign吗? from inserted as tem where tem.bm=xt_bm.bm and tem.sign='情况2'
update xt_bm set salary=
case when 不同的情况1 tem.salary *1.5
case when 不同的情况2 tem.salary *1
case when 不同的情况3 tem.salary *0.5
end ----这里不同的情况是胡乱写的
from inserted as tem
where tem.bm=xt_bm.bm
FOR UPDATE
ASBEGIN
declare @wp_bh char(8)
declare @wplx char(4)
declare @kcl numeric(10,3)
declare @je_changed numeric(10,3)
declare @djb_id numeric--SAVE TRANSACTION myTran --开始一个嵌套事务,并保存,以便部分回滚
begin transactiondeclare djb_id cursor for
select id from inserted -- 定义游标枚举inserted表记录open djb_id
fetch next from djb_id into @djb_idwhile @@fetch_status = 0begin ----------cursor djb_id select @wp_bh = inserted.wp_bh from inserted where inserted.id = @djb_id --取得物品编号
select @wplx = hr_wp.er_ejbm from hr_wp,inserted where hr_wp.bh = inserted.wp_bh and inserted.id = @djb_id --取得物品类型,二级编码
select @wplx = left(@wplx,1) ----------------------------------------确定物品字典表是否定意,或是否已物品类型
if @wp_bh = '' or (isnull(@wplx,'yes') = 'yes')
begin
rollback transaction --myTran
close djb_id
raiserror('物品字典表中没有该物品',16,1)
return
end
if @wplx = '' or (isnull(@wplx,'yes') = 'yes')
begin
rollback transaction --myTran
close djb_id
raiserror('物品类型没有定义',16,1)
return
end
--------------------------------------是否是作废票据-----------------------------------------------
if (select inserted.sign from inserted where inserted.id = @djb_id) = '1'
begin
if @wplx = '1'
begin
select @kcl = (select hr_kcb.sl from hr_kcb,inserted where hr_kcb.wp_bh = inserted.wp_bh and hr_kcb.xt_yyks = inserted.xt_yyks and hr_kcb.qyrq = inserted.qyrq and inserted.id = @djb_id)
--取得当前库存量
end
-- 是固定资产
else
begin
select @kcl = (select hr_kcb.sl from hr_kcb,inserted where hr_kcb.wp_bh = inserted.wp_bh and hr_kcb.xt_yyks = inserted.xt_yyks and inserted.id = @djb_id )
--取得当前库存量
end
--------------------------------根据单据类型取得业务发生后的库存量-----------------------------------------------------------------------------------------------------------
if (select inserted.type from inserted where inserted.id = @djb_id) > 10
begin
select @kcl = @kcl + (select sl from inserted where inserted.id = @djb_id)
select @je_changed = -(select je from inserted where inserted.id = @djb_id)
end --出库类单据
else
begin
select @kcl = @kcl - (select sl from inserted where inserted.id = @djb_id)
select @je_changed = (select je from inserted where inserted.id = @djb_id)
end --入库类单据
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
if @wplx <> '3' and @kcl < 0 --不是医用材料且库存小于 0
begin
rollback transaction myTran
close djb_id
raiserror('库存不足,不能作废此票据',16,1)
return
end
if @wplx = '1' --是固定资产
begin
update hr_kcb set hr_kcb.sl = @kcl,hr_kcb.je = hr_kcb.je - @je_changed from hr_kcb,inserted where hr_kcb.wp_bh = inserted.wp_bh and hr_kcb.xt_yyks = inserted.xt_yyks and hr_kcb.qyrq = inserted.qyrq and inserted.id = @djb_id
if (@@error <> 0)
begin
rollback transaction --myTran
close djb_id
raiserror('更新库存错误,请与Dreamaster联系',16,1)
return
end
end
else --不是固定资产
begin
update hr_kcb set hr_kcb.sl = @kcl,hr_kcb.je = hr_kcb.je - @je_changed from hr_kcb,inserted where hr_kcb.wp_bh = inserted.wp_bh and hr_kcb.xt_yyks = inserted.xt_yyks and inserted.id = @djb_id
if (@@error <> 0)
begin
rollback transaction --myTran
close djb_id
raiserror('更新库存错误,请与Dreamaster联系',16,1)
return
end
end
--更新库存
-------------------------------------------更新即时库存量-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
update hr_djb set hr_djb.kcl = @kcl from hr_djb,inserted where hr_djb.id = inserted.id and inserted.id = @djb_id
if (@@error <> 0)
begin
rollback transaction --myTran
close djb_id
raiserror('更新即时库存错误,请与Dreamaster联系',16,1)
return
end
endfetch next from djb_id into @djb_id ---取下一个游标
end -------cursor djb_idclose djb_id
----------------------------------------提交所有修改-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
commit transaction --myTran
END
删除游标引用。当释放最后的游标引用时,组成该游标的数据结构由 Microsoft® SQL Server™ 释放。
end -------cursor djb_idclose djb_id
DEALLOCATE djb_id
可以用变量
例如:
create table # (a varchar(10), b varchar(10), c varchar(10), d int, e int)
insert into # values('a', 'b', 'c' ,100 , null)
insert into # values('a', 'b', 'c' ,10 , null)
insert into # values('d', 'f', 'j', 50 , null)
insert into # values('a' , 'd' , 'f' , 30 , null)
insert into # values('d' , 'f' , 'a', 0 , 100)
select * into #1 from # ORDER BY D
declare @ int,@id int,@E INT
SELECT @=MAX(E) FROM #1
SET @E=0
update #1 set @=E=@ - @E ,@E=ISNULL(D,0)
SELECT * FROM #1
drop table #
drop table #1
a b c d e
---------- ---------- ---------- ----------- -----------
d f a 0 100
a b c 10 100
a d f 30 90
d f j 50 60
a b c 100 10