SQL Server 2008 - using local variables for an INSERT and UPDATE trigger

Question

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!

Answer 1

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

Answer 2

Your trigger isn't far off, but really you could be using and INSTEAD OF trigger

Create Test Data

create table product ( productId int identity(1,1) constraint PK_product_productId primary key clustered, quantity_in_stock int )
create table order_detail (  order_id int
                        ,productId int constraint FK_order_product_productId foreign key references product (productId)
                        ,quantity int not null)
set identity_insert product on
insert into product (productId, quantity_in_stock) values ( 1, 100 ), ( 2, 25 ) , (3, 2);

This 'Works' (in the lossest sense of the term) Taking on board Martin's comments the productid for the quantity_in_stock needed to be determined.

CREATE TRIGGER tr_check_qty
ON order_detail
FOR insert, update AS

DECLARE @stock int
DECLARE @neworder int
SELECT @stock = quantity_in_stock 
        From product 
        Where productid = (select productid from inserted)
SELECT @neworder = quantity FROM inserted

IF @neworder > @stock

BEGIN
PRINT 'NO WAY JOSE'
ROLLBACK TRANSACTION
END

These now all work as expected...

INSERT order_detail (order_id, productId, quantity)
values 
 (10044, 1, 30) -- works as stock is 100
,(10044, 3,  1)

insert order_detail (order_id, productId, quantity)
values 
    (10044, 1, 130) /* fails (CORRECTLY) WITH Msg 3609, Level 16... (transacted ended in the trigger..) */

/* this should work... */
UPDATE order_detail
SET quantity = 30
WHERE order_id = 10044
AND productid = 1

/* this should fail..  */
UPDATE order_detail
SET quantity = 3000 /*< not enough stock. */
WHERE order_id = 10044
AND productid = 1

And to address Martins first point this approach is better:

CREATE TRIGGER tr_check_qty
ON order_detail
FOR insert, update AS

DECLARE @stock int
DECLARE @neworder int

if(exists(select * 
          from inserted i join product p on i.productId = p.productId 
          where i.quantity > p.quantity_in_stock)) 
begin 
PRINT 'NO WAY JOSE'
ROLLBACK TRANSACTION
End