Q:

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

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! 

A:

You are assuming that there will only be a single row insert or update. 
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. 
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 

