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

最新推荐文章于 2021-02-05 08:55:32 发布
最新推荐文章于 2021-02-05 08:55:32 发布
阅读量4k 收藏 20
点赞数
分类专栏： SQL Server 文章标签： 库存管理
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tiger_zhao/article/details/45481305
版权

今天碰到个论坛问题关于库存的先进先出，写成博客以免以后搜不到。
限制：
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

Tiger_Zhao
关注
  • 0
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
先进先出SQL Server 语句
矫正的快乐人生
03-05 3093
--> 测试数据：[m] if object_id('[m]') is not null drop table [m] go Create table m ( ID int not NULL , mCount int not NULL ,mTime datetime not null) INSERT into m(ID,mCount,mTime) SELECT 1,50,'2014-02-0
sql server 2000 实现库存进销存
04-19
用sql server 2000 触发器实现进销存
仓库管理系统商业项目（附SpringBoot项目源代码）
02-12
课程介绍 仓库管理系统主要功能有采购入库，采购退货，销售出库，销售退货，仓库盘点，库存报表，Excel导入导出，按钮级权限控制及系统日志等功能，系统采用SpringBoot ,mybatis,easyui,ajax,mssql数据库等技术开发。提供所有源代码下载，系统功能完善，可直接运行。开发环境项目开发语言：SpringBoot ,mybatis,easyui,ajax,mssql数据库项目运行环境：jdk1.8及以上版本,tomcat8.0及以上版本,sql server2005及以上版本项目开发工具: 本项目开发工具是Intellij Idea课程目标掌握SpringBoot等技术，熟悉仓库管理系统主要功能，采购入库，采购退货，销售出库，销售退货，仓库盘点，系统报表，权限控制及日志等50多门JAVA系列全套课程，包括大一新生到大四毕业的所有JAVA系列技术专业课程，项目实战，商业项目等；基础课程：JAVA初级工程师： 1、计算机基础 2、HTML语言基础 3、C语言从入门到精通+贪吃蛇游戏 4、贪吃蛇游戏 5、SQL SERVER数据库基础 6、JAVA从入门到精通+推箱子游戏+QQ即时通讯软件 7、推箱子游戏； 8、仿QQ即时通讯软件；JAVA中级工程师： 9、SQLSERVER数据库高级 10、SQLSERVER从入门到精通（基础+高级） 11、JavaScript从入门到精通， 12、JSP从入门到精通+点餐系统， 13、JSP从入门到精通+在线视频学习教育平台， 14、JSP从入门到精通+大型电商平台； 15、XML从入门到精通， 16、数据结构（JAVA版），JAVA高级工程师： 17、Oracle数据库从入门到精通, 18、ajax+jquery从入门到精通， 19、EasyUI从入门到精通，SSH框架： 20、Struts2从入门到精通课程， 21、Hibernate从入门到精通课程， 22、Spring从入门到精通课程； 23、Echarts从入门到精通, 24、Excel基于POI的导入导出工作流框架： 25、Activiti流程框架从入门到精通 26、JBPM流程框架从入门到精通SSM框架： 27、MyBatis从入门到精通 28、Spring MVC从入门到精通 29、Spring Boot入门到精通 30、Spring Cloud入门到精通面试题： 31、职业生涯规划及面试题集锦商业项目： 32、微信公众号在线支付系统 33、微信生活缴费在线支付系统 34、支付宝生活缴费在线支付系统 35、在线考试系统 36、人脸识别智能考试系统（人工智能AI） 37、仓库管理及质量追溯系统 38、房屋出租管理系统APP（身份证识别） 39、手机订餐管理系统， 40、CRM客户关系管理系统 41、大型房地产CRM销售管理系统 42、CMPP2,CMPP3移动网关系统 43、仓库管理系统（SpringBoot) 44、影院在线售票系统(仿猫眼电影)人工智能： 45、人脸识别在线考试系统 46、人脸识别系统项目实战 47、车牌识别停车场管理系统 48、身份证识别系统项目实战 49、营业执照识别系统项目实战 50、名片识别管理系统
sql 库存先进先出原则，统计库存
qq_37935177的博客
07-22 1673
create table t( id int identity(1,1), mz varchar(50),--煤种 lc varchar(50),--料场 fq varchar(50),--分区 j int, --入库数量 c int, --出库数量 jdate datetime --入库时间 ) insert into t(mz,lc,fq,j,c,jdate) select 'A','LC1...
SQL处理库存与销售，先进先出原则
远
08-19 5460
--库存表create table t(id int identity(1,1),name varchar(50),--商品名称j int, --入库数量c int,        --出库数量jdate datetime --入库时间)insert into t(name,j,c,jdate) select  A,100,0,2007-12-01insert into
在mysql中如何设计退货时间触发器_mysql触发器 mysql之如何使用触发器
weixin_39578674的博客
02-05 157
myesl触发器该如何使用，这个问题是许多在学习网页设计的同学经常会遇到的问题，而且也是必须要掌握的一个知识点。所以今天我们就一起来了解一下有关myesl触发器的相关知识点。mysql触发器——mysql如何使用触发器1. 语法：createtriggertrigger_nameafter/beforeinsert/update/deleteontb_nameforeachrowbeginsql语...
sql_server数据库课程设计论文超市管理.pdf
05-27
这篇文档资料是关于使用SQL Server数据库进行课程设计的论文，主题是构建一个小型超市的商品进销存管理系统。这个系统旨在帮助广西师范大学的教职工管理工资、补贴、福利等事务，同时提供统计、查询和报表打印等功能...
简单的库存管理系统C# VS2010 SQL2008以上
09-08
【标题】"简单的库存管理系统C# VS2010 SQL2008以上"涉及到的主要技术包括C#编程语言、Visual Studio 2010开发环境以及SQL Server 2008以上的数据库管理系统。该系统提供了基本的库存管理功能，如用户管理、进销存...
手把手教你创建数据库, SQL Server 数据库手动创建 一个 超市数据库
12-24
在本教程中，我们将一起学习如何手动创建一个SQL Server数据库，特别是一个用于超市管理的数据库。这个数据库将包括几个核心的表，如商品表、员工表、进货表、退货表、交易表和供货商表，它们之间通过外键进行关联，...
sql_server.rar_进销存
09-19
开发者可能使用了SQL查询、存储过程、触发器等技术来实现系统的数据交互逻辑。 在“0309生产物流_server”这个文件名中，我们可以推测这可能是系统中关于生产物流的部分。这部分可能包含了对生产流程的跟踪，原材料...
jsp + SQLServer在线物流管理系统
04-18
【jsp + SQLServer在线物流管理系统】是一个基于Java服务器页面（JSP）技术和Microsoft SQL Server数据库构建的综合性进销存管理平台。这样的系统旨在提供高效、可靠的物流管理解决方案，以优化企业的库存控制、订单...
sql server对数据进行先进先出
qin_yaping的博客
04-25 840
--> 测试数据：[m] if object_id('[m]') is not null drop table [m] go Create table m ( ID int not NULL , mCount int not NULL ,mTime datetime not null) INSERT into m(ID,mCount,mTime) SELECT 1,50,'2014-0...
sql 先进先出 库存
weixin_33947521的博客
11-22 296
create table t( id int identity(1,1), name varchar(50),--商品名称 j int,        --入库数量 c int,        --出库数量 jdate datetime --入库时间 ) insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01' insert into...
sql先进先出查询解决办法
sunfor的专栏
09-15 5482
求 sql先进先出查询解决办法???? 商品编号  批次号  库存数量 0001      090801     200 0001      090501      50 0002      090101     30 0002      0
“先进先出”的库存处理
qq_39300966的博客
07-26 578
很经典的“先进先出”的库存处理的SQL语句。 CREATE TABLE #tmp --创建临时表#Tmp ( ID int IDENTITY (1, 1), --创建列ID,并且每次新增一条记录就会加1 单价 decimal(18, 2) NOT NULL , 数量 decimal(18, 2) NOT NULL , 已出数量 decimal(18, 2) NOT...
使用存储过程实现进销存系统中的先进先出算法（1）——数据库与存储过程设计...
weixin_34242509的博客
04-24 1044
         进销存系统中的先进先出，指得是利用队列原理——先采购进来的商品先卖出。          这里我用SQL Server的存储过程来实现这个功能。          关于这个存储过程的调用和演示说明,请查看 这里             这个存储过程在SQL Server 2000上编译通过并运行，      使用Oracle？ 前往。 数据库设计   ...
按需求先进先出分配库存
矫正的快乐人生
08-04 3073
--测试数据 create table #库存( wareid varchar(13), makeno varchar(40), storeqty numeric(16,6)) insert into #库存 select 'A','批号1',30 insert into #库存 select 'A','批号2',40 insert into #库存 select 'A','批号
sql实现触发器.ppt
最新发布
11-15
SQL Server中的触发器是一种特殊的存储过程，它会在执行特定语言事件时自动触发。SQL Server中包括两种类型的触发器：DML触发器和DDL触发器。 DDL触发器是在SQL Server 2005中引入的新功能，当服务器或数据库中发生...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值