Table1 EntreeID EntreeQty OutedQty EntreeDate
001 100 0 2007-1-1
002 50 0 2007-1-2
003 50 0 2007-1-3Table2
OutID OutQty OutDate
当插入数据时
001 160 2007-1-3
时,更新Table 为
EntreeID EntreeQty OutedQty EntreeDate
001 100 100 2007-1-1
002 50 50 2007-1-2
003 50 10 2007-1-3
如何实现这种果,,
001 100 0 2007-1-1
002 50 0 2007-1-2
003 50 0 2007-1-3Table2
OutID OutQty OutDate
当插入数据时
001 160 2007-1-3
时,更新Table 为
EntreeID EntreeQty OutedQty EntreeDate
001 100 100 2007-1-1
002 50 50 2007-1-2
003 50 10 2007-1-3
如何实现这种果,,
AS
update Table1 set OutedQty=EntreeQty
001 100 100 2007-1-1
002 50 50 2007-1-2
003 50 10 2007-1-3
????
还是
EntreeID EntreeQty OutedQty EntreeDate
001 100 100 2007-1-1
002 50 50 2007-1-2
003 50 50 2007-1-3
????
EntreeID EntreeQty OutedQty EntreeDate
001 100 100 2007-1-1
002 50 50 2007-1-2
003 50 50 2007-1-3
的话,上面那个触发器就可以
001 100 100 2007-1-1
002 50 50 2007-1-2
003 50 10 2007-1-3
sum(OutedQty)=160 ,也就是和输入的出库数一样
insert ta select '001', 100, 0, '2007-1-1'
union all select '002', 50, 0, '2007-1-2'
union all select '003', 50, 0, '2007-1-3'
create table tb(OutID varchar(20), OutQty int, OutDate datetime)
create trigger test_tr on tb
for insert
as
update a
set OutedQty=case when (select sum(EntreeQty)from ta where EntreeDate!>a.EntreeDate)<OutQty
then EntreeQty else OutQty-
((select sum(EntreeQty)from ta where EntreeDate!>a.EntreeDate)-EntreeQty)end
from ta a ,tb测试:
insert tb select '001', 160, '2007-1-3'select * from ta--drop table ta,tbEntreeID EntreeQty OutedQty EntreeDate
-------------------- ----------- ----------- ------------------------------------------------------
001 100 100 2007-01-01 00:00:00.000
002 50 50 2007-01-02 00:00:00.000
003 50 10 2007-01-03 00:00:00.000(所影响的行数为 3 行)
create table ta(EntreeID varchar(20), EntreeQty int, OutedQty int, EntreeDate datetime)
insert ta select '001', 100, 0, '2007-1-1'
union all select '002', 50, 0, '2007-1-2'
union all select '003', 50, 0, '2007-1-3'
create table tb(OutID varchar(20), OutQty int, OutDate datetime)--用存储过程
create proc test_p
@OutID varchar(20),
@OutQty int,
@OutDate datetime
as
insert tb select @OutID,@OutQty,@OutDate
update a
set OutedQty=case when (select sum(EntreeQty)from ta where EntreeID!>a.EntreeID)<OutQty--EntreeDate可以换为EntreeID(如果EntreeID是递增列的情况下)
then EntreeQty else OutQty-
((select sum(EntreeQty)from ta where EntreeID!>a.EntreeID)-EntreeQty)end
from ta a ,tb测试:
exec test_p '001', 160, '2007-1-3'select * from ta--drop table ta,tb
EntreeID EntreeQty OutedQty EntreeDate
-------------------- ----------- ----------- ------------------------------------------------------
001 100 100 2007-01-01 00:00:00.000
002 50 50 2007-01-02 00:00:00.000
003 50 10 2007-01-03 00:00:00.000(所影响的行数为 3 行)
insert ta select '004', 50, 0, '2007-1-4'
union all select '005', 50, 0, '2007-1-5'
union all select '006', 50, 0, '2007-1-6'然后insert tb select '001', 160, '2007-1-3'试试就不行了
谢谢 roy_88(中国风_燃烧你的激情!!!)
也谢谢各位的参与。
create table ta(EntreeID varchar(20), EntreeQty int, OutedQty int, EntreeDate datetime)
insert ta select '001', 100, 100, '2007-1-1'
union all select '002', 50, 50, '2007-1-2'
union all select '003', 50, 10, '2007-1-3'
union all select '004', 50, 0, '2007-1-4'
union all select '005', 50, 0, '2007-1-5'
union all select '006', 50, 0, '2007-1-6'
create table tb(OutID varchar(20), OutQty int, OutDate datetime)
insert tb select '001', 160, '2007-1-3'
--用存储过程
create proc test_p
@OutID varchar(20),
@OutQty int,
@OutDate datetime
as
insert tb select @OutID,@OutQty,@OutDate
update a
set OutedQty=case when (select sum(EntreeQty-OutedQty)from ta where EntreeID!>a.EntreeID)<OutQty--EntreeDate可以换为EntreeID(如果EntreeID是递增列的情况下)
then EntreeQty else OutQty-
((select sum(EntreeQty-OutedQty) from ta where EntreeID!>a.EntreeID)-EntreeQty)end
from ta a ,tb
where OutedQty<EntreeQty and tb.OutID=@OutID测试:
exec test_p '001', 160, '2007-1-3'select * from ta--drop table ta,tb
--drop proc test_p
EntreeID EntreeQty OutedQty EntreeDate
-------------------- ----------- ----------- ------------------------------------------------------
001 100 100 2007-01-01 00:00:00.000
002 50 50 2007-01-02 00:00:00.000
003 50 50 2007-01-03 00:00:00.000
004 50 50 2007-01-04 00:00:00.000
005 50 50 2007-01-05 00:00:00.000
006 50 20 2007-01-06 00:00:00.000(所影响的行数为 6 行)
create table ta(EntreeID varchar(20), EntreeQty int, OutedQty int, EntreeDate datetime)
insert ta select '001', 100, 100, '2007-1-1'
union all select '002', 50, 50, '2007-1-2'
union all select '003', 50, 10, '2007-1-3'
union all select '004', 50, 0, '2007-1-4'
union all select '005', 50, 0, '2007-1-5'
union all select '006', 50, 0, '2007-1-6'
create table tb(OutID varchar(20), OutQty int, OutDate datetime)
insert tb select '001', 160, '2007-1-3'
--用存储过程
create proc test_p
@OutID varchar(20),
@OutQty int,
@OutDate datetime
as
insert tb select @OutID,@OutQty,@OutDate
update a
set OutedQty=case when (select sum(EntreeQty-OutedQty)from ta where EntreeID!>a.EntreeID)<OutQty--EntreeDate可以换为EntreeID(如果EntreeID是递增列的情况下)
then EntreeQty else OutQty-
((select sum(EntreeQty-OutedQty) from ta where EntreeID!>a.EntreeID)-EntreeQty)end
from ta a ,tb
where OutedQty<EntreeQty and tb.OutID=@OutID测试:
exec test_p '002', 160, '2007-1-3'--这里把插入编号改为002select * from ta
insert ta select '001', 100, 0, '2007-1-1'
union all select '002', 50, 0, '2007-1-2'
union all select '003', 50, 0, '2007-1-3'
go
create table tb(OutID varchar(20),OutQty int,OutDate datetime)
gocreate proc Sotreage_proc
@OutID varchar(20),
@OutQty int,
@OutDate datetime
as
declare cur cursor for select EntreeId,EntreeQty from ta where EntreeDate<=@OutDate order by EntreeDate
declare @EntreeId int,@EntreeQty int
declare @temp int
set @temp=@OutQty
open cur
fetch next from cur into @EntreeId,@EntreeQty
while @@fetch_Status=0
begin
update ta set OutedQty=case when @EntreeQty>@temp then @temp else @EntreeQty end where EntreeId=@EntreeId
set @temp=case when @EntreeQty<@temp then @temp-@EntreeQty else 0 end
fetch next from cur into @EntreeId,@EntreeQty
end
close cur
deallocate cur
goexec Sotreage_proc '001','160','2007-1-3'
create table ta(EntreeID varchar(20), EntreeQty int, OutedQty int, EntreeDate datetime)
insert ta select '001', 100, 0, '2007-1-1'
union all select '002', 50, 0, '2007-1-2'
union all select '003', 50, 0, '2007-1-3'
union all select '004', 50, 0, '2007-1-4'create table tb(OutID varchar(20), OutQty int, OutDate datetime)
insert tb select '001', 160, '2007-1-3'
--用存储过程
create proc test_p
@OutID varchar(20),
@OutQty int,
@OutDate datetime
as
insert tb select @OutID,@OutQty,@OutDate
update a
set OutedQty=case when (select sum(EntreeQty-OutedQty)from ta where EntreeID!>a.EntreeID)<OutQty--EntreeDate可以换为EntreeID(如果EntreeID是递增列的情况下)
then EntreeQty else OutQty-
((select sum(EntreeQty-OutedQty) from ta where EntreeID!>a.EntreeID)-EntreeQty)end
from ta a ,tb
where OutedQty<EntreeQty and tb.OutID=@OutID
and
isnull((select sum(EntreeQty-OutedQty)from ta where EntreeID<a.EntreeID and OutedQty<EntreeQty ),0)-OutedQty<OutQty--加多一个条件
或直接用变量:
create table tb(OutID varchar(20), OutQty int, OutDate datetime)
insert tb select '001', 160, '2007-1-3'
--用存储过程
create proc test_p
@OutID varchar(20),
@OutQty int,
@OutDate datetime
as
insert tb select @OutID,@OutQty,@OutDate
update a
set OutedQty=case when (select sum(EntreeQty-OutedQty)from ta where EntreeID!>a.EntreeID)<@OutQty--EntreeDate可以换为EntreeID(如果EntreeID是递增列的情况下)
then EntreeQty else @OutQty-
((select sum(EntreeQty-OutedQty) from ta where EntreeID!>a.EntreeID)-EntreeQty)end
from ta a
where OutedQty<EntreeQty
and
isnull((select sum(EntreeQty-OutedQty)from ta where EntreeID<a.EntreeID and OutedQty<EntreeQty ),0)-OutedQty<@OutQty
测试:
exec test_p '002', 160, '2007-1-3'--这里把插入编号改为002