update trigger when checking another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update trigger when checking another table

Author

Topic

learning_sql
Starting Member

3 Posts

Posted - 2013-06-22 : 14:20:34

Hello I am trying to create an UPDATE trigger on a table named OrderDetails, which fires to prevent the updating of quantity in the OrderDetails table if the quantity amount is greater than the units which are in stock in the Products table. I am using MS Server 2008.

I am having a few difficulties as I am very new to using triggers. First off, I am not sure where to do the inner join on the OrderDetails and Products table in this case. I am pretty sure I almost have this, but the order of my logic is perhaps flawed.

This is what I have written, but it gives me the following error:

Msg 156, Level 15, State 1, Procedure tr_check_qty, Line 7
Incorrect syntax near the keyword 'from'.

I would like some input on why this is failing to work

CREATE TRIGGER tr_check_qty
ON OrderDetails
AFTER UPDATE
AS
select od.quantity from orderdetails od
INNER JOIN products pr ON pr.productid = od.productid
WHERE (od.quantity from deleted) > pr.unitsInStock)
BEGIN
print 'order is greater than quantity in stock, order can not be filled.'
ROLLBACK TRANSACTION
END

Any help would be great!
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-23 : 02:13:07

i prefer instead of trigger for this


CREATE TRIGGER tr_check_qty
ON OrderDetails 
INSTEAD OF UPDATE 
AS
BEGIN
INSERT INTO OrderDetails
SELECT i1.*
          FROM INSERTED i1
          INNER JOIN (SELECT productid,SUM(quantity) AS TotalQty
                FROM INSERTED 
                GROUP BY productid)i2
          ON i1.productid = i2.product
          INNER JOIN products p
          ON p.productid = i.productid
          WHERE p.unitsInStock >= i.TotalQty

IF EXISTS(SELECT 1
          FROM (SELECT productid,SUM(quantity) AS TotalQty
                FROM INSERTED 
                GROUP BY productid)i
          INNER JOIN products p
          ON p.productid = i.productid
          WHERE p.unitsInStock < i.TotalQty
          )
RAISERROR 'order is greater than quantity in stock, order can not be filled.'

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources