这个SQL 如何完成!!

[Tongls]

我这里有一条总表,一个子表.现在向主表更新一个回款金额的数据.现在如何向子表里加上回款金额??


主表FCon如下: FConID 单据号;主键
FSumMoney 总金额
FSumGetMoney 回款金额

子表FCon_Sub如下: ID 流水号
FConID 单据号
FDate 计划回款日期
FMoney 计划回款金额
FGetMoney 回款

现在想做一个触发器,当FSumMoney 更新时,把子表的FGetMoney更新了.

业务型式是按计划回款日期,即先满足时间小的.回款计划是不定的.可能有一条,可能有四\五条.

如: 单据号 为 F001 时
回款计划有: 2004-8-1 100
2004-9-1 500
2004-10-1 400
总金额为1000.

但是现在回款了500元. 按计划是得到的结果如下:

2004-8-1 100 100
2004-9-1 500 400
2004-10-1 400 0


各位大虾,不知如何写好,是不是一定要用到邮标呢?? 不用行不行??

[netcoder]

贴出测试数据来看看吧
即原有的数据和应得到的结果

[Tongls]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TCon_Sub_TCon]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TCon_Sub] DROP CONSTRAINT FK_TCon_Sub_TCon
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TCon]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TCon]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TCon_Sub]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TCon_Sub]
GO

CREATE TABLE [dbo].[TCon] (
[FConID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FSumMoney] [decimal](18, 0) NOT NULL ,
[FSumGetMoney] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TCon_Sub] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FConID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FDate] [datetime] NULL ,
[FMoney] [decimal](18, 0) NULL ,
[FGetMoney] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

Insert Into FCon (FConID,FSumMoney,FSumGetMoney) Values ("F001",1000,0)

Insert Into FCon_Sub (FConID,FDate,FMoney,FGetMoney) Values ("F001","2004-8-1",100,0)
Insert Into FCon_Sub (FConID,FDate,FMoney,FGetMoney) Values ("F001","2004-9-1",500,0)
Insert Into FCon_Sub (FConID,FDate,FMoney,FGetMoney) Values ("F001","2004-10-1",400,0)

当更新主表TCon的FSumGetMoney为500时,

得到结果
F001 2004-8-1 100 100
F001 2004-9-1 500 400
F001 2004-10-1 400 0

[zjcxc]

--处理的触发器
create trigger tr_update_TCon on TCon
for update
as
if update(FSumGetMoney)
update b set FGetMoney=case when (
select sum(FMoney) from TCon_Sub
where FConID=a.FConID and(
FDate<b.FDate or FDate=b.FDate and ID<=b.ID)
)<=a.FSumGetMoney then b.FMoney
else a.FSumGetMoney-isnull((
select sum(FMoney) from TCon_Sub
where FConID=a.FConID and(
FDate<b.FDate or FDate=b.FDate and ID<b.ID)
),0) end
from inserted a,TCon_Sub b
where a.FConID=b.FConID and isnull((
select sum(FMoney) from TCon_Sub
where FConID=a.FConID and(
FDate<b.FDate or FDate=b.FDate and ID<b.ID)
),0)<a.FSumGetMoney
go

[zjcxc]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TCon_Sub_TCon]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TCon_Sub] DROP CONSTRAINT FK_TCon_Sub_TCon
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TCon]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TCon]
GO

CREATE TABLE [dbo].[TCon] (
[FConID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FSumMoney] [decimal](18, 0) NOT NULL ,
[FSumGetMoney] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TCon_Sub] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FConID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FDate] [datetime] NULL ,
[FMoney] [decimal](18, 0) NULL ,
[FGetMoney] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

Insert Into TCon (FConID,FSumMoney,FSumGetMoney) Values ('F001',1000,0)

Insert Into TCon_Sub (FConID,FDate,FMoney,FGetMoney) Values ('F001','2004-8-1',100,0)
Insert Into TCon_Sub (FConID,FDate,FMoney,FGetMoney) Values ('F001','2004-9-1',500,0)
Insert Into TCon_Sub (FConID,FDate,FMoney,FGetMoney) Values ('F001','2004-10-1',400,0)
go

--处理的触发器
create trigger tr_update_TCon on TCon
for update
as
if update(FSumGetMoney)
update b set FGetMoney=case when (
select sum(FMoney) from TCon_Sub
where FConID=a.FConID and(
FDate<b.FDate or FDate=b.FDate and ID<=b.ID)
)<=a.FSumGetMoney then b.FMoney
else a.FSumGetMoney-isnull((
select sum(FMoney) from TCon_Sub
where FConID=a.FConID and(
FDate<b.FDate or FDate=b.FDate and ID<b.ID)
),0) end
from inserted a,TCon_Sub b
where a.FConID=b.FConID and isnull((
select sum(FMoney) from TCon_Sub
where FConID=a.FConID and(
FDate<b.FDate or FDate=b.FDate and ID<b.ID)
),0)<a.FSumGetMoney
go

--更新测试
update TCon set FSumGetMoney=500

--显示处理结果
select * from TCon
select * from TCon_Sub
go

--删除测试
drop table TCon,TCon_Sub

/*--测试结果

FConID FSumMoney FSumGetMoney
---------- --------------- --------------------
F001 1000 500

（所影响的行数为 1 行）

ID FConID FDate FMoney FGetMoney
----------- ---------- --------------------------- -------- ------------
1 F001 2004-08-01 00:00:00.000 100 100
2 F001 2004-09-01 00:00:00.000 500 400
3 F001 2004-10-01 00:00:00.000 400 0

（所影响的行数为 3 行）

--*/