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?