title: Trigger to update another table with sum of a column of original table
body: <p>I have a Project table:
  <img src="https://i.stack.imgur.com/YqHRa.png" alt="enter image description here"></p>
  
  <p>and a Milestone table:
  <img src="https://i.stack.imgur.com/rVJAo.png" alt="enter image description here"></p>
  
  <p>Whenever any insert, update, delete is made to Milestone table, Project table needs to updated.</p>
  
  <p>ProjectCost column in Project table is the sum of its milestones. Milestone table has ProjectId column which tells which Project does a milestone belong to.</p>
  
  <p>Everytime any change is made to milestone table, ProductCost in Product table should be updated.</p>
  
  <p>I'm having problem in the trigger:</p>
  
  <pre><code>CREATE TRIGGER UpdateProject 
     ON  [Organisation].[dbo].[Milestone] 
     AFTER INSERT,DELETE,UPDATE
  AS 
  BEGIN
      UPDATE T1
      SET T1.ProjectCost = (SELECT SUM(MilestoneCost) FROM [Organisation].[dbo].[Milestone] T2 
                              WHERE T2.ProjectId=)
      FROM [Organisation].[dbo].[Project] T1
      INNER JOIN Inserted i ON T1.ProjectId=i.ProjectId
      WHERE T1.ProjectId=
  
  END
  GO
  </code></pre>
  
  <p>How should the trigger be?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I edited the trigger as:</p>
  
  <pre><code>USE Organisation
  GO
  
  CREATE TRIGGER [UpdateProject]
     ON  [Organisation].[dbo].[Milestone] 
     AFTER INSERT,DELETE,UPDATE
  AS 
  BEGIN
      UPDATE T1
      SET T1.ProjectCost = (SELECT SUM(MilestoneCost) 
                              FROM [Organisation].[dbo].[Milestone] T2 
                              WHERE T2.ProjectId=i.ProjectId)
      FROM [Organisation].[dbo].[Project] T1
      INNER JOIN Inserted i ON T1.ProjectId=i.ProjectId
  END
  GO
  </code></pre>