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