Help with an Inventory Update trigger

[TJGreene]

I'm currently studying SQL and have created two tables (Orders and Inventory). I have been trying to create a trigger on the "OrderDate" column of the Orders table to update the Cur_Stock value (to subtract the Quantity ordered from the Cur_Stock) on the Inventory table whenever a new Order is placed. I've tried searching the internet but haven't had any success and I need some help with it.

HERE IS MY CODE:

CREATE TRIGGER [UpdateInventory]
ON [dbo].[Orders]
   FOR INSERT
AS
DECLARE @OrderDate smalldatetime
                @Quantity INT

	
SELECT @OrderDate = OrderDate, @Quantity=Quantity FROM Orders

BEGIN
UPDATE Inventory
SET Cur_Stock = Cur_Stock - @Quantity
WHERE @OrderDate = GetDate()
END

But obviously it isn't working. Can someone help me understand what I'm missing and WHY the above isn't working?

Thanks.

[MeSampath]

Hi,

Could you try in this way;

ALTER TRIGGER [UpdateInventory]
ON [dbo].[Orders]
FOR INSERT
AS
DECLARE @OrderDate smalldatetime,@Quantity INT

SELECT @OrderDate = OrderDate, @Quantity=Quantity FROM Orders

BEGIN
UPDATE Inventory
SET Cur_Stock = Cur_Stock - @Quantity
WHERE convert(varchar(10),@OrderDate,101) = convert(varchar(10),getdate(),101)
END

Good luck

[TJGreene]

AH. . . I see now that I was missing a comma in my DECLARE statement and instead of using "@OrderDate=getdate()" in my WHERE clause -- I should have used "convert(varchar(10),@OrderDate,101) = convert(varchar(10),getdate(),101)"

Thank you VERY much. It works now AND I was able to further update the Inventory Table to also modify the Date_Modified field to the current date/time for the appropriate item ordered as well.