SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update trigger when checking another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_sql
Starting Member

3 Posts

Posted - 06/22/2013 :  14:20:34  Show Profile  Reply with Quote
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

Edited by - learning_sql on 06/22/2013 14:47:02

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/23/2013 :  02:13:07  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000