sql server 分期付款 触发器设计

[Elysium]

事例如下：
现有一分期应收款表BS
编号 应收款年度 应收款时间 应收款金额 实收款时间 实收款金额
0001 2005.01.01-2005.12.30 2005-02-01 50000.00
0001 2006.01.01-2006.12.30 2006-02-01 90000.00
0001 2007.01.01-2007.12.30 2007-02-01 50000.00
...

实收款表Charge
编号 实收款时间 实收款金额


则：
对0001收到第一笔款50000元(Charge表)，那么应收款表0001第一笔实收款即为50000(BS表)；如果收到40000(Charge表)，则第一笔实收款为40000(BS表)；如果收到100000(Charge表)，则第一笔实收款是50000(BS表)，第二笔实收款是50000(BS表)...然后是陆续的实收款，将自动的按时间排序将前面的款项结清，保持最新的待结应收款，对实收款表Charge进行增加编辑删除....

[昵称被占用了]

规则没完全

如果
BS
编号 应收款年度 应收款时间 应收款金额 实收款时间 实收款金额
0001 2005.01.01-2005.12.30 2005-02-01 50000.00 2005-3-1 40000.00
0001 2006.01.01-2006.12.30 2006-02-01 90000.00
0001 2007.01.01-2007.12.30 2007-02-01 50000.00

这时
收到一笔
2005-4-1 10000
或者
2005-4-1 100000

BS第一条记录如何改变？

[Elysium]

现在暂时是把实收款时间更新到当前新增或编辑的charge记录
最后结果是这个样子，以新增100000为例
BS
编号 应收款年度 应收款时间 应收款金额 实收款时间 实收款金额
0001 2005.01.01-2005.12.30 2005-02-01 50000.00 2005-4-1 50000.00
0001 2006.01.01-2006.12.30 2006-02-01 90000.00 2005-4-1 90000.00

[Elysium]

或其他任何优化建议？？

[昵称被占用了]

create trigger tr_Charge_Insert
on Charge
for insert
as

if (select count(*) from inserted)>1
begin
RAISERROR ('一次只能插入一条记录', 16, 1)
ROLLBACK TRANSACTION
return
end
set nocount on

declare @实收款金额 numeric(18,2)
declare @编号 varchar(20)
declare @实收款时间 datetime
declare @temp numeric(18,2)

select @编号=编号,@实收款时间=实收款时间,@实收款金额=实收款金额 from inserted

update bs
set @temp=case when @实收款金额>应收款金额-isnull(实收款金额,0) then 应收款金额-isnull(实收款金额,0) else @实收款金额 end,
@实收款金额=@实收款金额-@temp,
实收款时间=case when @temp>0 then @实收款时间 else 实收款时间 end,
实收款金额=case when @temp>0 then isnull(实收款金额,0)+@Temp else 实收款金额 end
where 编号=@编号 and 应收款金额-isnull(实收款金额,0)>0

go

--要求：BS表以(编号,应收款时间)作为聚集索引

[昵称被占用了]

--未测试,你测试下
--修改的情况将非常复杂,建议Charge表不允许修改和删除,有错误只能用另一条数据充

[昵称被占用了]

触发器未考虑充数的情况,假设实收款金额>0

[Elysium]

修改的情况的确很复杂，我现在就主要是update这一关不好过，效果总不是令人满意，还有如过不要update，则必须支持delete才行，否则应收实收就摆不平了，还有实收为负数的情况...

还有先问一下以上的更新可以覆盖多条记录么？就是说实收款相当于好几期的应收款之和了，是否能都更新，我原来触发器里用了游标...

[昵称被占用了]

如果需要修改删除,不如去掉BS表的实收款时间 实收款金额字段，也不需要触发器

问题就变成一个非配查询的问题

BS
编号 应收款年度 应收款时间 应收款金额
0001 2005.01.01-2005.12.30 2005-02-01 50000.00
0001 2006.01.01-2006.12.30 2006-02-01 90000.00
0001 2007.01.01-2007.12.30 2007-02-01 50000.00
...

Charge
编号 实收款时间 实收款金额
0001 2005-3-1 40000
0001 2005-4-1 100000
0001 2007-4-1 50000

结果
编号 应收款年度 应收款时间 应收款金额 实收款时间 实收款金额
0001 2005.01.01-2005.12.30 2005-02-01 50000.00 2005-4-1 50000
0001 2006.01.01-2006.12.30 2006-02-01 90000.00 2005-4-1 90000
0001 2007.01.01-2007.12.30 2007-02-01 50000.00 2007-4-1 50000

[昵称被占用了]

这个查询可以用个存储过程来解决,用临时表和游标是可以写出来的,出错机会会少很多

[Elysium]

存储过程实现应收实收的自动计算？也得用游标吧
可是这样在进行一些应收实收统计/报表的时候会不会又不方便了或出其他问题？

[昵称被占用了]

你原来的数据是冗余的,而且需要修改,这样维护一致性的代价有点大

[Elysium]

难道要用个视图来解决？