title: Insert/Update trigger in sql server to check values
body: <p>I'm new to SQL Server and triggers. I have an existing trigger i need to customise. There are two tables Products (which contains the total amount of products in the stock. Quantity) and OrdersLines (which contains all information about orders. an example row would would have OrderID, ProductID and Amount in it)
  When I make an order product amount is reserved.
  I need the trigger to to check if new ordered amount doesnt go above the Quantity. If it does, throw RAISERROR, if not, let the transaction continue. </p>
  
  <p>I'm having trouble with identifying number of total order to check against the Quantity in stock. </p>
  
  <p>Trigger is for OrdersLines table
  I was thinking of doing something along the lines of:</p>
  
  <pre><code>IF EXISTS ((SELECT *
              FROM Products AS p
              JOIN inserted AS i
              ON p.ProductID = i.ProductID
              JOIN OrdersLines AS o
              ON p.ProductID = o.ProductID
              WHERE (SUM(o.Amount) + i.Amount) &lt;= p.Quantity)
  BEGIN 
      GOTO ContinueTransaction
  END ELSE BEGIN
      GOTO RollbackTransaction
      END
  </code></pre>
  
  <p>but I think this is not correct. Also in ELSE i need RAISERROR to inform that order wasn't added but I'm not sure how. There are arguments to be put there and its confusing to me right now. </p>