title: SQL Server 2008 - using local variables for an INSERT and UPDATE trigger
body: <p>I've been tinkering away at this problem for a while, but nothing's working for me.</p>
  
  <p>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.</p>
  
  <pre><code>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 &gt; @stock
  
  BEGIN
  PRINT 'NO WAY JOSE'
  ROLLBACK TRANSACTION
  END
  </code></pre>
  
  <p>To test this trigger, we are supposed to use this query:</p>
  
  <pre><code>UPDATE order_details
  SET quantity = 30
  WHERE order_id = '10044'
  AND product_id = 7
  </code></pre>
  
  <p>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.</p>
  
  <p>I had a suspicion that triggers don't like local variables, so I tried not using local variables:</p>
  
  <pre><code>(SELECT quantity FROM inserted) &gt; (SELECT quantity_in_stock FROM products)
  </code></pre>
  
  <p>But this gave me an error.</p>
  
  <p>Any help would be appreciated!</p>