SQL Server Trigger: Updating another table using Inner Joins

I had a situation recently where we were changing an internal process that meant we would no longer be updating the data in a specific table because those column where moving to another table.  Unfortunately there was going to be some crossover and a temporary trigger may be necessary to continue to populate the previous table.  So below we are using an Update statement with Inner Joins.  Another cool feature I had forgotten about is the Inserted and Deleted tables.  We use the Inserted table to make sure we are only updating the rows affected by the Update or Insert statement that initiated the trigger. 

CREATE TRIGGER My_Trigger
ON Table_1
FOR INSERT, UPDATE
AS
   UPDATE Table_2
   SET Table_2.Column_1 = Table_1.Column_1,
       Table_2.Column_2 = Table_1.Column_2
   FROM Table_2
   INNER JOIN inserted
   ON Table_2.PK_1 = Inserted.PK_1
   AND Table_2.PK_2 = Inserted.PK_2
   INNER JOIN Table_1
   ON Table_2.PK_1 = Table_1.PK_1
   AND Table_2.PK_2 = Table_1.PK_2
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s