Reputation: 21
I created a Modified Order table, and did not insert any data into it. This table is supposed to have an entry made every time someone updates the order table. I created a trigger that stated that if you update a row in the Order table it triggers an entry into the Order Modified table. It works properly, the only problem is I do not know how to get the Order ID that was updated to be entered into the Order modified table.
Order Modified Table Code:
Create Table Modified_Order (
Modified_Order_Number Int Identity (1,1),
Modified_Order_Date Date,
Order_ID Int,
Foreign Key (Order_ID) references Orders (Order_ID)
);
Trigger Code:
Create Trigger Modified_Order_Trigger
On Orders After Update
AS
Insert Into Modified_Order Values (getdate(), )
;
The issue I am having is getting the Order ID for the order that was updated to show up in the Order Modified table. I know it comes in the spot that is left blank after getdate(). I know it has something to do with adding a select statement, I'm just not sure what to put in after the where statement so it knows to select the order ID for whatever Order was updated in the order table.
Create Trigger Modified_Order_Trigger
On Orders After Update
AS
Insert Into Modified_Order Values (getdate(), (Select Order_ID
from Order
where )
;
Thanks
Upvotes: 1
Views: 26569
Reputation: 48914
Within a Trigger, the INSERTED and DELETED virtual tables have the data from both states: INSERTED has new via INSERT or UPDATE, and DELETED has old via UPDATE or DELETE. So the syntax is basically:
Create Trigger Modified_Order_Trigger
On Orders
After Update
AS
Insert Into Modified_Order (Modified_Order_Date, Order_ID)
SELECT GETDATE(), Order_ID
FROM INSERTED
For more info on Triggers, look here: http://technet.microsoft.com/en-us/library/ms189799.aspx
Upvotes: 8