SQL Server 用触发器实现库存的先进先出(有退货)

今天碰到个论坛问题关于库存的先进先出,写成博客以免以后搜不到。
限制:
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
                   @batchNo = id,
                   @batchQty = 库存数量,
                   @batchPrice = 单价
              FROM 库存表
             WHERE 产品 = @pno
               AND 库存数量 > 0
          ORDER BY id

            IF @batchNo IS NULL
            BEGIN
                PRINT '库存不足'
                ROLLBACK TRAN
                RETURN
            END

            IF @batchQty >= @totalQty
                SET @batchUseQty = @totalQty
            ELSE
                SET @batchUseQty = @batchQty

            UPDATE 库存表
               SET 库存数量 = 库存数量 - @batchUseQty
             WHERE id = @batchNo

            SET @amount = @amount + @batchUseQty * @batchPrice
            SET @totalQty = @totalQty - @batchUseQty

        END

    END
    ELSE -- 退货
    BEGIN

        WHILE @totalQty < 0
        BEGIN

            SELECT TOP 1
                   @batchNo = id,
                   @batchQty = 入库数量 - 库存数量,
                   @batchPrice = 单价
              FROM 库存表
             WHERE 产品 = @pno
               AND 入库数量 > 库存数量
          ORDER BY id DESC

            IF @batchNo IS NULL
            BEGIN
                PRINT '没有可退货的库存'
                ROLLBACK TRAN
                RETURN
            END

            IF @batchQty >= ABS(@totalQty)
                SET @batchUseQty = ABS(@totalQty)
            ELSE
                SET @batchUseQty = @batchQty

            UPDATE 库存表
               SET 库存数量 = 库存数量 + @batchUseQty
             WHERE id = @batchNo

            SET @amount = @amount + @batchUseQty * @batchPrice
            SET @totalQty = @totalQty + @batchUseQty

        END

    END

    UPDATE 出仓表
       SET 金额 = @amount
     WHERE id = @id

GO

测试

INSERT INTO 进仓表(日期,产品,数量,单价) VALUES('2015-01-01','A',10,100)
SELECT * FROM 进仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  单价
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-01 00:00:00.000 A                   10                100.00

         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                   10          10                100.00

INSERT INTO 进仓表(日期,产品,数量,单价) VALUES('2015-01-02','A',4,120)
SELECT * FROM 进仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  单价
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-01 00:00:00.000 A                   10                100.00
          2 2015-01-02 00:00:00.000 A                    4                120.00

         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                   10          10                100.00
          2 A                    4           4                120.00

INSERT INTO 出仓表(日期,产品,数量)
 VALUES('2015-01-02','A',5)
SELECT * FROM 出仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  金额
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-02 00:00:00.000 A                    5                500.00


         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                   10           5                100.00
          2 A                    4           4                120.00

INSERT INTO 进仓表(日期,产品,数量,单价) VALUES('2015-01-03','A',-5,100)
SELECT * FROM 进仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  单价
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-01 00:00:00.000 A                   10                100.00
          2 2015-01-02 00:00:00.000 A                    4                120.00
          3 2015-01-03 00:00:00.000 A                   -5                100.00

         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                    5           0                100.00
          2 A                    4           4                120.00

INSERT INTO 进仓表(日期,产品,数量,单价) VALUES('2015-01-04','A',15,90)
SELECT * FROM 进仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  单价
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-01 00:00:00.000 A                   10                100.00
          2 2015-01-02 00:00:00.000 A                    4                120.00
          3 2015-01-03 00:00:00.000 A                   -5                100.00
          4 2015-01-04 00:00:00.000 A                   15                 90.00

         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                    5           0                100.00
          2 A                    4           4                120.00
          4 A                   15          15                 90.00

INSERT INTO 出仓表(日期,产品,数量)
 VALUES('2015-01-04','A',6)
SELECT * FROM 出仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  金额
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-02 00:00:00.000 A                    5                500.00
          2 2015-01-04 00:00:00.000 A                    6                660.00

         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                    5           0                100.00
          2 A                    4           0                120.00
          4 A                   15          13                 90.00

INSERT INTO 出仓表(日期,产品,数量)
 VALUES('2015-01-05','A',-3)
SELECT * FROM 出仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  金额
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-02 00:00:00.000 A                    5                500.00
          2 2015-01-04 00:00:00.000 A                    6                660.00
          3 2015-01-05 00:00:00.000 A                   -3                300.00

         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                    5           0                100.00
          2 A                    4           1                120.00
          4 A                   15          15                 90.00

INSERT INTO 出仓表(日期,产品,数量)
 VALUES('2015-01-07','A',10)
SELECT * FROM 出仓表
SELECT * FROM 库存表
GO

         id 日期                    产品              数量                  金额
----------- ----------------------- ---------- ----------- ---------------------
          1 2015-01-02 00:00:00.000 A                    5                500.00
          2 2015-01-04 00:00:00.000 A                    6                660.00
          3 2015-01-05 00:00:00.000 A                   -3                300.00
          4 2015-01-07 00:00:00.000 A                   10                930.00

         id 产品          入库数量    库存数量                  单价
----------- ---------- ----------- ----------- ---------------------
          1 A                    5           0                100.00
          2 A                    4           0                120.00
          4 A                   15           6                 90.00

没有更多推荐了,返回首页