SQL Server 2008 - 对 INSERT 和 UPDATE 触发器使用局部变量

Question

我已经解决这个问题有一段时间了，但没有任何效果。

问题是为 order_details 表创建一个 INSERT 和 UPDATE 触发器 (tr_check_qty)，以仅允许库存数量大于或等于订购数量的产品订单。

CREATE TRIGGER tr_check_qty

ON order_details
FOR insert, update

AS

DECLARE @stock int
DECLARE @neworder int
SELECT @stock = quantity_in_stock FROM products
SELECT @neworder = quantity FROM inserted

IF @neworder > @stock

BEGIN
PRINT 'NO WAY JOSE'
ROLLBACK TRANSACTION
END

为了测试这个触发器，我们应该使用这个查询：

UPDATE order_details
SET quantity = 30
WHERE order_id = '10044'
AND product_id = 7

该查询选择一个只有 28 个Quantity_in_stock 的产品，它应该触发触发器。但是我的触发器没有触发并且它成功更新了表。

我怀疑触发器不喜欢局部变量，所以我尝试不使用局部变量：

(SELECT quantity FROM inserted) > (SELECT quantity_in_stock FROM products)

但这给了我一个错误。

任何帮助将不胜感激！

Answer 1

1. 您假设只有单行插入或更新。

2. quantity_in_stock FROM products 没有谓词 - 大概它需要检查插入的productid 的库存水平？如果是的话，products 表的结构是什么？ （目前，假设 products 表中有不止一行，@stock 将从任意行分配一个值。

3. 这在快照隔离下不起作用。

要解决 #1 和 #2，您需要使用 productid 或其他方式将 inserted 表加入到 products 表中，并查看是否有任何行存在于 inserted.quantity > products.quantity_in_stock

有关 #3 的一些想法，请阅读 此处讨论

Answer 2

您的触发器并不遥远，但实际上您可以使用 INSTEAD OF 触发器

创建测试数据

create table product ( productId int identity(1,1) constraint PK_product_productId primary key clustered, quantity_in_stock int )
create table order_detail (  order_id int
                        ,productId int constraint FK_order_product_productId foreign key references product (productId)
                        ,quantity int not null)
set identity_insert product on
insert into product (productId, quantity_in_stock) values ( 1, 100 ), ( 2, 25 ) , (3, 2);

这“有效”（在该术语的损失意义上）
考虑 Martin 的意见，需要确定 quantity_in_stock 的 productid。

CREATE TRIGGER tr_check_qty
ON order_detail
FOR insert, update AS

DECLARE @stock int
DECLARE @neworder int
SELECT @stock = quantity_in_stock 
        From product 
        Where productid = (select productid from inserted)
SELECT @neworder = quantity FROM inserted

IF @neworder > @stock

BEGIN
PRINT 'NO WAY JOSE'
ROLLBACK TRANSACTION
END

现在这些都按预期工作......

INSERT order_detail (order_id, productId, quantity)
values 
 (10044, 1, 30) -- works as stock is 100
,(10044, 3,  1)

insert order_detail (order_id, productId, quantity)
values 
    (10044, 1, 130) /* fails (CORRECTLY) WITH Msg 3609, Level 16... (transacted ended in the trigger..) */

/* this should work... */
UPDATE order_detail
SET quantity = 30
WHERE order_id = 10044
AND productid = 1

/* this should fail..  */
UPDATE order_detail
SET quantity = 3000 /*< not enough stock. */
WHERE order_id = 10044
AND productid = 1

并且为了解决马丁斯的第一点，这种方法更好：

CREATE TRIGGER tr_check_qty
ON order_detail
FOR insert, update AS

DECLARE @stock int
DECLARE @neworder int

if(exists(select * 
          from inserted i join product p on i.productId = p.productId 
          where i.quantity > p.quantity_in_stock)) 
begin 
PRINT 'NO WAY JOSE'
ROLLBACK TRANSACTION
End

Answer 3

另一种解决方案是使用而不是触发器，如下所示：

Create Trigger TR_Check_Qty
ON order_detail
INSTEAD OF insert AS

 insert into order_detail (order_id, productId, quantity)
    select i.order_id, i.productId, i.quantity
    from inserted i inner join product p on i.productId = p.productId
    where i.quantity <= p.quantity_in_stock

此触发器的行为与其他建议不同！此触发器将插入已履行的订单并忽略超出库存水平的订单，这可能不是必需的[事实上，在大多数情况下可能不是；您的应用程序想知道订单何时尚未保存到数据库！！！]

注意这只是一个插入，您需要创建一个不同的触发器来更新“插入”值需要更新而不是插入。

同样，在这个问题的范围之外还有其他考虑因素。您可能应该在插入订单时减少库存水平，并且您应该可能想要处理为同一产品插入多个详细信息行的情况。