今天碰到个论坛问题关于库存的先进先出,写成博客以免以后搜不到。
限制:
1)进仓表、出仓表只能一次插入一条,多条触发器没有对应。
2)退货没有指定批次,是按最近原则自动匹配的。
表结构和触发器
CREATE TABLE 进仓表(
id int identity(1,1),
日期 datetime,
产品 varchar(10),
数量 int,
单价 money
)
GO
CREATE TABLE 出仓表(
id int identity(1,1),
日期 datetime,
产品 varchar(10),
数量 int,
金额 money
)
GO
CREATE TABLE 库存表(
id int, -- = 进仓表.id,表示批次
产品 varchar(10),
入库数量 int,
库存数量 int,
单价 money
)
GO
CREATE TRIGGER tr_进仓表 ON 进仓表
AFTER INSERT
AS
DECLARE @id int
DECLARE @pno varchar(10)
DECLARE @qty int
DECLARE @price money
DECLARE @batchNo int
SELECT @id = id,
@pno = 产品,
@qty = 数量,
@price = 单价
FROM inserted
IF @qty >=0 -- 正常入库
INSERT INTO 库存表
SELECT @id, @pno, @qty, @qty, @price
ELSE -- 退货
BEGIN
SELECT @batchNo = MAX(id)
FROM 库存表
WHERE 产品 = @pno
AND 单价 = @price
AND (库存数量 + @qty) >=0
IF @batchNo IS NULL
BEGIN
PRINT '没有可退货的库存'
ROLLBACK TRAN
RETURN
END
UPDATE 库存表
SET 入库数量 = 入库数量 + @qty,
库存数量 = 库存数量 + @qty
WHERE id = @batchNo
END
GO
CREATE TRIGGER tr_出仓表 ON 出仓表
AFTER INSERT
AS
DECLARE @id int
DECLARE @pno varchar(10)
DECLARE @totalQty int
DECLARE @amount money
DECLARE @batchNo int
DECLARE @batchQty int
DECLARE @batchPrice money
DECLARE @batchUseQty int
SET @amount = 0
SELECT @id = id,
@pno = 产品,
@totalQty = 数量
FROM inserted
IF @totalQty >=0 -- 正常出库
BEGIN
WHILE @totalQty > 0
BEGIN
SELECT TOP 1
@batc