求触发器实现按时间统计仓储余额

Question

一个项目中，每天有货物进出。
我弄了二个表，一个是流水表，一个是余额表。余额表用触发器管理
当有货物进出时，写入在流水表上，同时余额表反映其变化。并要保留时间信息。
流水表的结构是：序号，货物名称，进出方向，货物进出量，货物进出时间
余额表是：货物名称，货物余额，货物进出时间。当进货仓储增加时，直接在余额表上增加。如果是同一天的，余额表上余额加在一起
当出货追念减少时，余额表上按先进先出的原则，如果余额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日求触发器实现上述余额表。谢谢

Answer 1

为什么要用
触发器实现上述余额表，
写事务吧。
记号http://hqn.jschina.com.cn/v_show.asp?m=prop_&id=355

Answer 2

--分析没错，不知执行有没有错，没建表，试下
create trigger [流水表_updins]
on [流水表]
for insert
as
update 余额表
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 float
declare cur cursor local for select 货物名称,货物进出量=sum(货物进出量) from inserted i where i.进出方向='出货'
open cur
fetch next from cur into @proname,@qty
while @@fetch_status=0
begin
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,@qty
end
close cur
deallocate cur
go

Answer 3

create trigger tri_l_u_i
on lius
for insert
as
declare @type char(2),@total int,@date datetime,@cName char(10),@con int,@i int ,@t int
select @cName=cName,@total=total,@date=date,@type=ctype from inserted
if @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)
end
if @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
end
end
----------------------------------------
--测试表结构：
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 all
select '端盖','200','2012-06-01' union all
select '轴承','200' ,'2012-07-01'union all
select '轴承', '330', '2012-08-01'union all
select '轴承', '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','出'

Answer 4

--这一句，最好是在执行‘出库’的时候判断，因为，这里的判断只能阻止不更改“余额”表中的数据，流水表还是会写入！！！！
select @t=sum(total) from yue where cName=@cName
    if @total>@t
        return

Answer 5

--------------------------------------------
   --  Author:TravyLee(跟小F姐姐混)
   --  Date  :2012-05-26 17:20:00
--------------------------------------------
---->>TravyLee生成测试数据:
if OBJECT_ID('流水表') is not null
drop table 流水表
go
create table 流水表(
ProductNmae varchar(20),
Counts int,
Dates varchar(10),
Kinds varchar(2)
)
go
if OBJECT_ID('余额表') is not null
drop table 余额表
go
create table 余额表(
ProductNmae varchar(20),
Counts int,
Dates varchar(10)
)
go
insert 余额表
select '轴承',100,'5月1日' union all
select '端盖',200,'6月1日' union all
select '轴承',200,'7月1日' union all
select '轴承',330,'8月1日' union all
select '轴承',400,'9月1日'-------------------------------------------
-------------------------------------------
---->>>触发器实现对余额表的管理
go
if OBJECT_ID('tri_test')is not null
drop trigger tri_test
go
create trigger tri_test on 流水表
for insert
as
--处理新增类型为'入'的零件的余额表数据更新/*更新余额表中存在的日期的数据*/
update 余额表
set 余额表.Counts=t.Counts+余额表.Counts from(
select 
    ProductNmae,sum(Counts) Counts,Dates
from
    inserted i
where 
    exists(select 1 from 余额表 t 
         where i.ProductNmae=t.ProductNmae and i.Dates=t.Dates)
    and i.Kinds='入'
group by
    ProductNmae,Dates
)t
where
    余额表.ProductNmae=t.ProductNmae and 余额表.Dates=t.Dates/*插入日期在之前余额表中不存在的*/
insert 余额表
select 
    ProductNmae,sum(Counts),Dates
from
    inserted i
where 
    not exists(select 1 from 余额表 t 
         where i.ProductNmae=t.ProductNmae and i.Dates=t.Dates)
    and i.Kinds='入'
group by
    ProductNmae,Dates --处理新增类型为'出'的零件的余额表数据更新
/*先对余额表里的数据进行递归累计求和运算*/
;with t
as(
select 
    px=row_number()over(partition by ProductNmae 
   order by getdate()),ProductNmae,Counts,Dates
from 余额表
),m
as(
select px,ProductNmae,Counts,Counts as total,Dates
from t
where px=1
union all
select a.px,a.ProductNmae,a.Counts,
    a.Counts+b.total,a.Dates
from t a
inner join  m b
on 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 totaln
from m
inner 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 NewCounts
from n
)
update 余额表
set 余额表.Counts=o.NewCounts from o
where 余额表.Dates=o.Dates and 余额表.ProductNmae=o.ProductNmae
delete from 余额表 where Counts=0
--验证:
--当9月1日增加轴承50只时，9月2日增加端盖30只时表变成
insert 流水表
select '轴承',50,'9月1日','入' union all
select '端盖',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日
*/
--这个问题的难点在与先进先出

Answer 6

你每个零件插入几条时间不相同的数据试试：
insert 流水表
select '轴承',50,'9月1日','入' union all
select '轴承',50,'9月2日','入' union all
select '轴承',50,'9月3日','入' union all
select '端盖',30,'9月2日','入'

Answer 7

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','出'结果是9号剩下2

Answer 8

select top 1 @con=total,@date=date from yue where cName=@cName order by date asc
我用的是循环，每次都是取最早的一条。肯定不会出现你说的问题。
你可以复制代码试试。

Answer 9

create trigger tri_l_u_i
on lius
for insert
as
declare @type char(2),
@total int,@date datetime,@cName char(10),@con int,@i int ,@t int
select @cName=cName,@total=total,@date=date,@type=ctype from inserted
if @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)
end
if @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
    end
end
----------------------------------------
--测试表结构：
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 all
select '端盖','200','2012-06-01' union all
select '轴承','200' ,'2012-07-01'union all
select '轴承', '330', '2012-08-01'union all
select '轴承', '400', '2012-09-01'-----------------------------------------
--触发器触发语句：
--这样插入数据:
insert into lius 
select  '轴承', '10', '2012-07-1','入' union all
select  '轴承', '10', '2012-07-1','入' union all
select  '轴承', '9', '2012-09-8','入' union all
select  '轴承', '9', '2012-09-8','入' union all
select  '轴承', '9', '2012-09-8','入' union all
select  '轴承', '10', '2012-09-9','入' union all
select  '轴承', '27', '2012-09-10','出'select * from yue
--执行你的语句的结果，自己看吧
/*
cName total date
轴承       100 2012-05-01 00:00:00.000
端盖       200 2012-06-01 00:00:00.000
轴承       210 2012-07-01 00:00:00.000
轴承       330 2012-08-01 00:00:00.000
轴承       400 2012-09-01 00:00:00.000
*/
--循环跟递归的效率你可以去试试

Answer 10

结果如下：（不过，数据是一条一条insert的，而不是批量insert的，关于效率问题，我认为带检测。。）
cName      total       date
---------- ----------- -----------------------
轴承         20          2012-09-08 00:00:00.000
轴承         10          2012-09-09 00:00:00.000(2 行受影响)