如何在SQL Server中创建存储过程/触发器更新商品库存与状态并限制库存非负

• 拥有products和order_details两张表，需要创建SQL Server存储过程实现：商品售出时更新products表的product_quantity列，计算逻辑为现有库存 - 订购数量，且product_quantity值不能低于0
• 当库存减去售出数量后等于0时，将product_status改为'not available'

尝试的视图代码 #

drop view if exists v_products
go

create view v_products 
as
    select 
        product_name, product_quantity, product_status, 
        quantity, p.product_id
    from
        products p
    inner join 
        order_details od on p.product_id = od.product_id

尝试的存储过程代码 #

DROP PROCEDURE IF EXISTS p_products_product_quantity
GO

CREATE PROCEDURE p_products_product_quantity
    @product_quantity INT,
    @sold_quantity INT,
    @product_status VARCHAR(50),
    @product_id INT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE v_products
    SET product_status = @product_status,
        quantity = @sold_quantity,
        product_quantity = @product_quantity,
        product_id = @product_id
        -- ,product_quantity = @product_quantity - @sold_quantity
    WHERE @product_quantity - @sold_quantity = 0
END

现有实现存在几个核心问题：内连接视图更新存在歧义、参数设计冗余、更新逻辑未满足业务需求。以下是符合要求的存储过程：

DROP PROCEDURE IF EXISTS p_update_product_stock
GO

CREATE PROCEDURE p_update_product_stock
    @product_id INT,
    @sold_quantity INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 校验售出数量合法性
    IF @sold_quantity <= 0
    BEGIN
        RAISERROR('售出数量必须大于0', 16, 1);
        RETURN;
    END

    -- 执行库存更新，确保库存不低于0，自动切换状态
    UPDATE products
    SET 
        product_quantity = CASE 
                            WHEN product_quantity - @sold_quantity < 0 THEN 0
                            ELSE product_quantity - @sold_quantity
                          END,
        product_status = CASE 
                            WHEN product_quantity - @sold_quantity <= 0 THEN 'not available'
                            ELSE product_status
                          END
    WHERE product_id = @product_id;

    -- 校验目标商品是否存在
    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('未找到指定ID的商品', 16, 1);
    END
END

关键说明 #

• 直接操作products表，避免视图更新的歧义问题
• 通过CASE语句自动处理库存下限逻辑，同时根据库存剩余值切换商品状态
• 增加参数校验和商品存在性验证，提升程序鲁棒性
• 调用示例：当ID为1的商品售出23件时，执行EXEC p_update_product_stock @product_id = 1, @sold_quantity = 23;

内容的提问来源于stack exchange，提问作者ema pema