CODEWITHSUNDEEP
sqlsql-serverdatabase
HandsomeRob
HandsomeRob

Reputation: 445

SQL: Trigger that references 2 tables

This is probably a silly question but its got me chasing my tail.

Order table and products table, if quantity ordered exceeds quantity in stock then...

I've probably googled 50 key words, check 3 books, can't find the way to do this. This is the premise of what I've trying to do, not what I've tried.

CREATE TRIGGER tr_check_qty
ON order_details
FOR INSERT,UPDATE
AS
IF (SELECT quantity FROM inserted) > products.quantity_in_stock
    BEGIN
        PRINT 'Orderded quantity cannot exceed quantity in stock'
        ROLLBACK TRANSACTION
    END

Where can I join them? I've tried 20 different ways, tried declaring a variable in front, I cannot find a way to do this.

Cheers.

Upvotes: 3

Views: 2680

Answers (1)

Tomalak
Tomalak

Reputation: 338326

You could do this:

CREATE TRIGGER tr_check_qty ON order_details
FOR INSERT,UPDATE
AS
BEGIN
  -- rollback transaction if any product type in order exceeds stock amount
  IF EXISTS (
    SELECT 
      * 
    FROM 
      inserted
      INNER JOIN products ON inserted.product_id = products.product_id
    GROUP BY
      products.product_id
    HAVING
      SUM(inserted.quantity) > MAX(products.quantity_in_stock)
  )
  BEGIN
    PRINT 'Ordered quantity cannot exceed quantity in stock'
    ROLLBACK TRANSACTION
  END
END

It works for both single-row inserts and multi-row inserts.

Upvotes: 3

Related Questions