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!

Answers


  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


Need Your Help

Validate NSString for Hexadecimal value

cocoa nsstring

I'm working on a custom NSFormatter.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.