一个先进先出的处理办法,进货(单据,销售、批发退货),出货(销售,批发,领用) 能不能说说具体一点的!包括建表,主要思路,大体架构 -- stin(流水号(主键),产品编号,入库数量,入库时间,入库单价,入库金额) 入库表 stout(流水号(主键),产品编号,出库数量,出库时间,出库单价,出库金额)出库表stack(流水号(主键),产品编号,入库数量,入库时间,入库单价,入库金额) 库存表create trigger ti_stin on stin for update ,insert,delete as insert into stack select * from inserted delete a from stack a,deleted b where a.流水号=b.流水号 go create trigger ti_out on stout for insert as -- declare @total int,@产品编号 varchar(20),@num int select @total=出库数量,@产品编号=产品编号 from inserted if (select sum(入库数量) from stack where 产品编号=@产品编号)<@total --库存不足 begin RAISERROR ('库存不足',16, 1) ROLLBACK TRANSACTION end declare cur cursor for select 出库数量 from stack open cur fetch next from cur into @num while @@fetch_status=0 begin if @num>@total begin update stack set 入库数量=入库数量-@total WHERE CURRENT OF cur break end else begin set @total=@total-@num delete stack WHERE CURRENT OF cur endfetch next from cur into @num end go
--上面有点错误 declare cur cursor for select 出库数量 from stack ===>改为 declare cur cursor for select 出库数量 from stack where 产品编号=@产品编号 order by 入库时间 desc --即你说的先进先出
能不能说说具体一点的!包括建表,主要思路,大体架构
--
stin(流水号(主键),产品编号,入库数量,入库时间,入库单价,入库金额) 入库表 stout(流水号(主键),产品编号,出库数量,出库时间,出库单价,出库金额)出库表stack(流水号(主键),产品编号,入库数量,入库时间,入库单价,入库金额) 库存表create trigger ti_stin on stin
for update ,insert,delete
as
insert into stack select * from inserted
delete a from stack a,deleted b where a.流水号=b.流水号
go
create trigger ti_out on stout
for insert
as
--
declare @total int,@产品编号 varchar(20),@num int
select @total=出库数量,@产品编号=产品编号 from inserted
if (select sum(入库数量) from stack where 产品编号=@产品编号)<@total --库存不足
begin
RAISERROR ('库存不足',16, 1)
ROLLBACK TRANSACTION
end
declare cur cursor for select 出库数量 from stack
open cur
fetch next from cur into @num
while @@fetch_status=0
begin
if @num>@total
begin
update stack set 入库数量=入库数量-@total WHERE CURRENT OF cur
break
end
else
begin
set @total=@total-@num
delete stack WHERE CURRENT OF cur
endfetch next from cur into @num
end
go
declare cur cursor for select 出库数量 from stack
===>改为
declare cur cursor for select 出库数量 from stack where 产品编号=@产品编号
order by 入库时间 desc --即你说的先进先出
你好!谢谢你的答复!你这个的速度怎么样呢!能不能用堆栈来实现,我不明白堆栈在用sqlserver语言来实现,希望您给我提个点子!
你好!谢谢你的答复!你这个的速度怎么样呢!能不能用堆栈来实现,我不明白堆栈在用sqlserver语言来实现,希望您给我提个点子!