title: SQL Server update value in another table if value in (inserted) first table is greater
body: <p>I have the following tables: (simplified for clarity)</p>
  
  <ol>
  <li>AppWorkHeader with columns: (ProjectID <strong>(FK)</strong>, ProgPercent, + 40 others not relevant)</li>
  <li>AppProjects with columns: (ProjectID <strong>(PK)</strong>, ProgPercent + 9 others not relevant)</li>
  </ol>
  
  <p>I am trying to create a trigger that after insert, the ProgPercent value from the AppWorkHeader table updates the ProgPercent value in the AppProjects table only if it's greater than the existing value. I can get it to work with single insertions with the following:</p>
  
  <pre><code>-- Only works on single row insert
  CREATE TRIGGER [dbo].[AppWorkHeader_project_trigger]
  ON 
  [dbo].[AppWorkHeader]
  AFTER INSERT AS
  IF @@ROWCOUNT = 0
      RETURN
  
  SET NOCOUNT ON
  
  -- Get the current project completion percentage from the AppProjects table
  DECLARE @inserted_projectID int = (SELECT i.ProjectID FROM inserted i)
  DECLARE @current_percent decimal(5,4) = (SELECT ProgPercent FROM AppProjects WHERE ProjectID = @inserted_projectID)
  
  UPDATE AppProjects
  SET ProgPercent = inserted.ProgPercent
  FROM inserted
  WHERE AppProjects.ProjectID = inserted.ProjectID AND inserted.ProgPercent &gt; @current_percent
  </code></pre>
  
  <p>I can get multiple insertions to work with the following code. However, the greater-than part of my where clause seems to be ignored. Multiple insertions with the same ProjectID are updated to lower values.</p>
  
  <pre><code>-- Multiple row insert
  CREATE TRIGGER [dbo].[AppWorkHeader_project_trigger]
  ON 
  [dbo].[AppWorkHeader]
  AFTER INSERT AS
  
  IF @@ROWCOUNT = 0
      RETURN
  
  SET NOCOUNT ON
  
  UPDATE AppProjects
  SET ProgPercent = inserted.ProgPercent
  FROM inserted
  WHERE AppProjects.ProjectID = inserted.ProjectID AND inserted.ProgPercent &gt; (SELECT ProgPercent FROM AppProjects WHERE ProjectID = (SELECT inserted.ProjectID))
  </code></pre>
  
  <p>I can't figure out how to get the existing value in the AppProjects table without using a variable, and I can't seem to get multiple insertions to work if I use a variable. Where am I going wrong?</p>