求触发器实现按时间统计仓储余额
一个项目中，每天有货物进出。
我弄了二个表，一个是流水表，一个是余额表。余额表用触发器管理
当有货物进出时，写入在流水表上，同时余额表反映其变化。并要保留时间信息。
流水表的结构是：序号，货物名称，进出方向，货物进出量，货物进出时间
余额表是：货物名称，货物余额，货物进出时间。
当进货仓储增加时，直接在余额表上增加。如果是同一天的，余额表上余额加在一起
当出货追念减少时，余额表上按先进先出的原则，如果余额0，则不显示此货物
如仓库余额表里原有：
轴承 100只 5月1日
端盖 200只 6月1日
轴承 200只 7月1日
轴承 330只 8月1日
轴承 400只 9月1日
当9月1日增加轴承50只时，9月2日增加端盖30只时表变成
轴承 100只 5月1日
端盖 200只 6月1日
轴承 200只 7月1日
轴承 330只 8月1日
轴承 450只 9月1日
端盖 30只 9月2日
当9月3日出货轴承400只时，即是
轴承 0只 5月1日
端盖 200只 6月1日
轴承 0只 7月1日
轴承 230只 8月1日
轴承 450只 9月1日
端盖 30只 9月2日
其中0只不显示,表应为
端盖 200只 6月1日
轴承 230只 8月1日
轴承 450只 9月1日
端盖 30只 9月2日
求触发器实现上述余额表。谢谢
[解决办法]
--分析没错，不知执行有没有错，没建表，试下create trigger [流水表_updins]on [流水表]for insertasupdate 余额表 set 货物余额=货物余额+i.货物进出量 from inserted i where 余额表.货物名称=i.货物名称 and i.进出方向='进货' and convert(varchar(10),i.货物进出时间,111)=convert(varchar(10),余额表.货物进出时间,111)insert into 余额表 select 货物名称,货物进出量,货物进出时间 from inserted i where not exists (select 1 from 余额表 where 余额表.货物名称=i.货物名称 and convert(varchar(10),i.货物进出时间,111)=convert(varchar(10),余额表.货物进出时间,111)) and i.进出方向='进货'--你的出货时间就按晚于所有进货的来计算declare @proname nvarchar(50),@qty floatdeclare cur cursor local for select 货物名称,货物进出量=sum(货物进出量) from inserted i where i.进出方向='出货'open curfetch next from cur into @proname,@qtywhile @@fetch_status=0begin declare @tmpqty float,@tmpdate varchar(10) while exists(select 1 from 余额表 where 货物进出量>0) and @qty>0 begin select top 1 @tmpqty=货物进出量,@tmpdate=convert(varchar(10),货物进出时间,111) from 余额表 where 货物进出量>0 and 货物名称=@proname order by 货物进出时间 asc if(@qty>@tmpqty) begin delete 余额表 where 货物名称=@proname and convert(varchar(10),货物进出时间,111)=@tmpdate select @qty=@qty-@tmpqty end else begin update 余额表 set 货物进出量=货物进出量-@qty where 货物名称=@proname and convert(varchar(10),货物进出时间,111)=@tmpdate delete 余额表 where 货物名称=@proname and convert(varchar(10),货物进出时间,111)=@tmpdate and 货物进出量=0 select @qty=@qty-@tmpqty end end fetch next from cur into @proname,@qtyendclose curdeallocate curgo
[解决办法]
create trigger tri_l_u_ion liusfor insertasdeclare @type char(2),@total int,@date datetime,@cName char(10),@con int,@i int ,@t intselect @cName=cName,@total=total,@date=date,@type=ctype from insertedif @type='入'begin if exists(select 1 from yue where cName=@cName and date=@date) update yue set total=total+@total where cName=@cName and date=@date else insert into yue values(@cName,@total,@date)endif @type='出'begin select @t=sum(total) from yue where cName=@cName if @total>@t return while @total>0 begin select top 1 @con=total,@date=date from yue where cName=@cName order by date asc if @con>=@total begin select @con=@con-@total,@total=0 update yue set total=@con where cName=@cName and date=@date end else begin select @total=@total-@con,@con=0 delete yue where cName=@cName and date=@date end endend------------------------------------------测试表结构：create table yue( cName char(10), total int, date datetime)create table lius( cName char(10), total int, date datetime, ctype char(2))insert into yue select '轴承','100','2012-05-01' union allselect '端盖','200','2012-06-01' union allselect '轴承','200' ,'2012-07-01'union allselect '轴承', '330', '2012-08-01'union allselect '轴承', '400', '2012-09-01'-------------------------------------------触发器触发语句：insert into lius select '轴承', '10', '2012-09-7','入'insert into lius select '轴承', '9', '2012-09-8','入'insert into lius select '轴承', '10', '2012-09-9','入'insert into lius select '轴承', '27', '2012-09-10','出'
[解决办法]
-------------------------------------------- -- Author:TravyLee(跟小F姐姐混) -- Date :2012-05-26 17:20:00------------------------------------------------>>TravyLee生成测试数据:if OBJECT_ID('流水表') is not nulldrop table 流水表gocreate table 流水表(ProductNmae varchar(20),Counts int,Dates varchar(10),Kinds varchar(2))goif OBJECT_ID('余额表') is not nulldrop table 余额表gocreate table 余额表(ProductNmae varchar(20),Counts int,Dates varchar(10))goinsert 余额表select '轴承',100,'5月1日' union allselect '端盖',200,'6月1日' union allselect '轴承',200,'7月1日' union allselect '轴承',330,'8月1日' union allselect '轴承',400,'9月1日'------------------------------------------------------------------------------------------>>>触发器实现对余额表的管理goif OBJECT_ID('tri_test')is not nulldrop trigger tri_testgocreate trigger tri_test on 流水表for insertas--处理新增类型为'入'的零件的余额表数据更新/*更新余额表中存在的日期的数据*/update 余额表set 余额表.Counts=t.Counts+余额表.Counts from(select ProductNmae,sum(Counts) Counts,Datesfrom inserted iwhere exists(select 1 from 余额表 t where i.ProductNmae=t.ProductNmae and i.Dates=t.Dates) and i.Kinds='入'group by ProductNmae,Dates)twhere 余额表.ProductNmae=t.ProductNmae and 余额表.Dates=t.Dates/*插入日期在之前余额表中不存在的*/insert 余额表select ProductNmae,sum(Counts),Datesfrom inserted iwhere not exists(select 1 from 余额表 t where i.ProductNmae=t.ProductNmae and i.Dates=t.Dates) and i.Kinds='入'group by ProductNmae,Dates --处理新增类型为'出'的零件的余额表数据更新/*先对余额表里的数据进行递归累计求和运算*/;with tas(select px=row_number()over(partition by ProductNmae order by getdate()),ProductNmae,Counts,Datesfrom 余额表),mas(select px,ProductNmae,Counts,Counts as total,Datesfrom twhere px=1union allselect a.px,a.ProductNmae,a.Counts, a.Counts+b.total,a.Datesfrom t ainner join m bon a.px=b.px+1 and a.ProductNmae=b.ProductNmae),n as(select m.px,m.ProductNmae,m.Dates,m.Counts,m.total totalm,b.total totalnfrom minner join(select ProductNmae,sum(Counts) as total from inserted where Kinds='出'group by ProductNmae)b on m.ProductNmae=b.ProductNmae),o as(select ProductNmae,Dates,case when px in(select px from n where totalm-totaln<0) then 0 when px=(select min(px) from n where totalm-totaln>=0) then totalm-totaln else Counts end as NewCountsfrom n)update 余额表set 余额表.Counts=o.NewCounts from owhere 余额表.Dates=o.Dates and 余额表.ProductNmae=o.ProductNmaedelete from 余额表 where Counts=0--验证:--当9月1日增加轴承50只时，9月2日增加端盖30只时表变成insert 流水表select '轴承',50,'9月1日','入' union allselect '端盖',30,'9月2日','入'select * from 余额表/*ProductNmae Counts Dates轴承 100 5月1日端盖 200 6月1日轴承 200 7月1日轴承 330 8月1日轴承 450 9月1日端盖 30 9月2日*/--当9月3日出货轴承400只时，即是insert 流水表select '轴承',400,'9月3日','出'select * from 余额表/*ProductNmae Counts Dates端盖 200 6月1日轴承 230 8月1日轴承 450 9月1日端盖 30 9月2日*/--这个问题的难点在与先进先出