title: Using "AFTER INSERT, UPDATE, DELETE" DML trigger. How reference triggering row?
body: <p>I have a Main table in which I want to sum up values from rows in a Sub table. One-or-many Sub rows will reference a single Main row. I want Main.Total to be the sum of all corresponding Sub.Amount values. I am attempting to implement a AFTER INSERT, UPDATE, DELETE trigger to accomplish this.
  I am open to alternative solutions besides a trigger, such as using a view, if that is a better way to approach this. Even if there is a better solution, I am still interested in learning how I would solve this with a trigger, if only for academic purposes. Here is a simplified example:</p>
  
  <pre><code>CREATE DATABASE TEST;
  GO
  USE TEST;
  GO
  
  CREATE TABLE Main (
      Id INT
      ,Total INT DEFAULT(0)
      );
  GO
  
  CREATE TABLE Sub (
      Main_fk INT
      ,Sub_Id INT
      ,Amount INT
      );
  GO
  
  CREATE TRIGGER Update_Main_Total
      ON Sub
      AFTER INSERT, UPDATE, DELETE
      AS BEGIN
          DECLARE @Main_Id INT = (
              --  The Main_fk value for the inserted/updated/deleted
              --  Sub row that caused the trigger to fire
              );
          UPDATE Main
          SET Total = (
              SELECT SUM(Amount)
              FROM Sub
              WHERE Main_fk = @Main_Id
              )
          WHERE Id = @Main_Id;
      END;
  GO
  </code></pre>