title: SQL Server Trigger- How to use each row of INSERTED
body: <p>Here is a simplified version of the problem:</p>
  
  <p>Table1 with columns: <strong>name, color, shape, amount</strong>.</p>
  
  <p>Table2 with columns: <strong>color, shape, amount</strong></p>
  
  <p>Table2 is supposed to keep the total amount of all color/shape pairs, for instance, if table1 had entries</p>
  
  <pre><code>Joe, blue, square, 2
  Bob, red, square, 1
  Alice, blue, square, 3
  </code></pre>
  
  <p>Then table2 should read:</p>
  
  <pre><code>blue, square, 5
  red, square, 1
  </code></pre>
  
  <p>I would like to write a trigger that will keep table2 updated every time something is inserted to table1. For each row of the INSERTED table, it should check if that particular color/shape combo is in table2 already. If it is, it should update that row to reflect the new total. If it is not, it should add that row. However, I am completely new to SQL and I have no idea how to check through each row of INSERTED. I have posted my early attempt at it below. Any and all help is appreciated!</p>
  
  <p>EDIT: Final working version for insert trigger.</p>
  
  <pre><code>CREATE TRIGGER after_insert_table1 ON table1
  AFTER INSERT  
  AS
  BEGIN
  
  MERGE table2 AS TARGET
  USING (select color,shape,SUM(amount) as amount from INSERTED
     group by color,shape)  AS SOURCE
  ON TARGET.color = SOURCE.color
  AND TARGET.shape = SOURCE.shape
  WHEN MATCHED THEN
  UPDATE 
  SET TARGET.amount = (TARGET.amount + SOURCE.amount)
  WHEN NOT MATCHED THEN
  INSERT (color,shape,amount)
  VALUES(SOURCE.color, SOURCE.shape, SOURCE.amount)
  ;
  
  END/
  </code></pre>
  
  <p>EDIT: ALMOST WORKING! So it functions as needed. But I am worried that this method is inefficient, especially when table2 is very large. I want to be able to do this only checking the rows that were deleted, not every row in table2.</p>
  
  <pre><code>CREATE TRIGGER after_delete_table1 ON table1
  AFTER DELETE  
  AS
  BEGIN
  
  MERGE table2 AS TARGET
  USING (select color,shape,SUM(amount) as amount from DELETED
     group by color,shape)  AS SOURCE
  ON TARGET.color = SOURCE.color
  AND TARGET.shape = SOURCE.shape
  
  WHEN MATCHED THEN
  
  UPDATE 
  SET TARGET.amount = (TARGET.amount - SOURCE.amount)
  
  ;
  
  DELETE FROM table2 
  WHERE NOT EXISTS
  (SELECT NULL FROM 
   table1 t WHERE t.color = table2.color AND 
                t.shape = table2.shape);
  
  
  END/
  </code></pre>
  
  <p>Zero values are allowed in table2. However, a row in table2 should be removed if there are no longer corresponding rows in table1. This is what I am trying to do now, but throwing it in the merge definitely didn't work.</p>