求教这样的一个存储过程

yige520lgz 2005-09-19 10:07:59
我有两个表(A、B),A表有字段要更新的AllCount,Count1,Count2,Count3,AllMoney,B表有Break1,Break2两个字段(两个字段是整型,但可以为空),我的客户端更新A表的AllCount字段,我要的触发器只是捕捉对AllCount的Update操作,用Break1,Break2来更新Count1,Count2,Count3,当Break1为空时:Count1=AllCount,Count2=0,Count3=0当Break2为空时:Count1=AllCount DIV Break1,Count2=AllCount MOD Break2,Count3=0,当Break2不为空时:Count1=AllCount DIV(Break1*Break2);Count2=AllCount MOD(Break1*Break2)DIV Break2;Count3=AllCount MOD(Break1*Break2)MOD Break2),更新完Count1,Count2,Count3时,再更新AllMoney=Price1*Count1+Price2*Count2+Price3*Count3,(price1,Price2,Price3:已经保证不为空值)请教各路高手赐教!最好就是附上具体实现方法!多谢先了!
...全文
183 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
子陌红尘 2005-09-19
create procedure sp_test(
@price1 money,
@Price2 money,
@Price3 money)
as
begin
declare @Break1 int,@Break2 int
declare @Count1 money,@Count1 money,Count3 money

select @Break1=Break1,@Break2=Break2 from B

update A
set
@Count1 = case when @Break1 is null then AllCount
when @Break2 is null then AllCount/@Break1
else AllCount/(@Break1*@Break2)
end,
@Count2 = case when @Break1 is null then 0
when @Break2 is null then AllCount%@Break1
else (AllCount%(@Break1*@Break2))/@Break2
end,
@Count3 = case when @Break1 is null then 0
when @Break2 is null then 0
else (AllCount%(@Break1*@Break2))%@Break2
end,
Count1 = @Count1,
Count2 = @Count2,
Count3 = @Count3,
AllMoney = @Price1*@Count1+@Price2*@Count2+@Price3*@Count3
end
go
回复
子陌红尘 2005-09-19
create procedure sp_test(
@price1 money,
@Price2 money,
@Price3 money)
as
begin
declare @Break1 int,@Break2 int
declare @Count1 money,@Count1 money,Count3 money

select @Break1=Break1,@Break2=Break2 from B

update A
set
@Count1 = case when @Break1 is null then AllCount
when @Break2 is null then AllCount/@Break1
else AllCount/(@Break1*@Break2)
end,
@Count2 = case when @Break1 is null then 0
when @Break2 is null then AllCount%@Break1
else (AllCount%(@Break1*@Break2))/@Break2
end,
@Count3 = case when @Break1 is null then 0
when @Break2 is null then 0
else (AllCount%(@Break1*@Break2))/@Break2
end,
Count1 = @Count1,
Count2 = @Count2,
Count3 = @Count3,
AllMoney = @Price1*@Count1+@Price2*@Count2+@Price3*@Count3
end
go
回复
yige520lgz 2005-09-19
是了,我忽略了一点,就是我这个表的记录是很多的,但我每次更新的AllCount只是一条记录,当然,A表和B表都会有相匹配的编码Number,这个又是如何处理呢?是否要提供一个外参数Number来进行限制呢?
回复
yige520lgz 2005-09-19
子陌红尘兄,可否解释一下,还有一点就是,你在哪里实现那捕更新AllCount时才触发该触发器呢?更新其它的字段不会触发,我好像看不到相关的哦!我想从此触发器里学习一番,希望得到大哥的帮助!多谢先了!
回复
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2005-09-19 10:07
社区公告
暂无公告