SQL Server 2008 - using local variables for an INSERT and UPDATE trigger

I've been tinkering away at this problem for a while, but nothing's working for me.

The question is to create an INSERT and UPDATE trigger (tr_check_qty) for the order_details table to only allow orders of products that have a quantity in stock greater than or equal to the units ordered.

CREATE TRIGGE开发者_如何转开发R 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

To test this trigger, we are supposed to use this query:

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

The query selects a product that has only 28 quantity_in_stock, which should trigger the trigger. But my trigger does not trigger and it updates the table successfully.

I had a suspicion that triggers don't like local variables, so I tried not using local variables:

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

But this gave me an error.

Any help would be appreciated!

1. You are assuming that there will only be a single row insert or update.

2. quantity_in_stock FROM products has no predicate - presumably it needs to check the stock level of the inserted productid? If so what is the structure of the products table? (At the moment @stock will be assigned a value from an arbitrary row assuming more than one row in the products table.

3. This will not work under snapshot isolation.

To get around #1 and #2 you would need to JOIN the inserted table onto the products table using productid or whatever and see if any rows exist where inserted.quantity > products.quantity_in_stock

For some ideas about #3 read the discussion here

Your trigger isn't far off, but really you could be using and INSTEAD OF trigger

Create Test Data

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);

This 'Works' (in the lossest sense of the term) Taking on board Martin's comments the productid for the quantity_in_stock needed to be determined.

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

These now all work as expected...

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

And to address Martins first point this approach is better:

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

An alternative solution is to use the instead of trigger, something like this:

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

This trigger behaves differently from the other suggestion! This trigger will insert orders that are fulfilled and ignore orders that exceed the stock level, this maynot be want is required [in fact it probably isn't in most situations; your application would want to know when an order hasn't been saved to the DB!!!]

Note this is just an insert you'd need to create a different trigger for update as the 'inserted' values would need to be updates not inserts.

Again there are other considerations outside of the scope of this question.. you should probably be reducing the stock level as the orders are inserted and you should be may want to handle situations where multiple detail rows are inserted for the same product.