title: How can I write an update trigger to update the current table based on a value in another table in SQL Server 2008?
body: <p>I have two tables, Table_A and Table_B. Table_A has a one-to-many relationship with Table_B.</p>
  
  <p>After inserting a record in the child table (Table_B) I want to use a trigger to grab a value from a field in the parent table (Table_A) and update the currently inserted record in Table_B.</p>
  
  <p><strong>Table_A</strong></p>
  
  <pre><code>PK|EmpID|MemID|Firstname|Lastname
  ----------------------------------
  1 |1234 |AB123|John     | Doe
  2 |5678 |CD456|Jane     | Smith
  </code></pre>
  
  <p><strong>Table_B</strong></p>
  
  <pre><code>PK|EmpID|MemID|Description
  ---------------------
  1 |1234 |NULL |Finance
  2 |1234 |NULL |IT
  3 |5678 |NULL |Finance 
  4 |5678 |NULL |Management
  </code></pre>
  
  <p>The trigger should grab the MemID value from Table_A and update it's corresponding value in Table_B where [Table_A].[EmpID] = [Table_B].[EmpID]. Of course after 4 inserts in Table_B the results should be as follows:</p>
  
  <pre><code>PK|EmpID|MemID|Description
  ---------------------
  1 |1234 |AB123|Finance
  2 |1234 |AB123|IT
  3 |5678 |CD456|Finance 
  4 |5678 |CD456|Management
  </code></pre>
  
  <p>I extensively searched other technical sites but can't seem to find one with this particular scenario. As a desperate move I have registered into this site and this would be my 1st post for help.</p>
  
  <p>The following is my futile attempt at trying to create my 1st trigger in MS SQL Server 2008 R2.</p>
  
  <pre><code>CREATE TRIGGER dbo.trgInsMemID
  ON dbo.Table_B
  AFTER INSERT
  AS
      BEGIN
          UPDATE dbo.Table_B
              SET MemID = 
                  (SELECT MemID 
                  FROM inserted i 
                  JOIN dbo.Table_A c ON c.EmpID = i.EmpID)
              FROM dbo.Table_B b
              JOIN inserted i ON i.MemID = b.MemID
      END
  </code></pre>