CODEWITHSUNDEEP
sql-servertriggers
user3015938
user3015938

Reputation: 21

Trigger that inserts data into new table when another table is updated

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: 26570

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48934

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

Related Questions