Question: 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:

```
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

```

Answer: 
There are no for-each-row triggers in SQL Server. Instead in a trigger you can access the `inserted` and `deleted` pseudo tables. `inserted` contains the rows that get inserted or the changed version of a row for an update and `deleted` the deleted rows or the version of a row before an update.

So you'd need to do use something like the following to update the sum. It first gets the sum per ID for `inserted` and analog for `deleted` then full joins the results -- a full join is needed here since not all IDs are necessarily in both sets -- and then updates `main`.

```
UPDATE m
       SET m.total = m.total + z.total
       FROM main m
            INNER JOIN (SELECT coalesce(x.main_fk, y.main_fk) main_fk,
                               coalesce(x.total, 0) - coalesce(y.total, 0) total
                               FROM (SELECT i.main_fk,
                                            sum(i.amount) total
                                            FROM inserted i
                                            GROUP BY i.main_fk) x
                                    FULL JOIN (SELECT d.main_fk,
                                                      sum(d.amount) total
                                                      FROM delete d
                                                      GROUP BY d.main_fk) y
                                              ON y.main_fk = x.main_fk) z
                       ON z.main_fk = m.id;

```

(Maybe you want to set the `total` in `main` to `NULL` if there aren't any `sub` records left. The query above doesn't do that, that'd need some extra work.)

But physically storing such figures that can be computed from others bears the risk of inconsistencies. If the trigger is disabled or doesn't work (properly) for other reasons for a period in time, changes to the `sub` table are not (correctly) reflected in the `main` table. You'd have false figures in there and possibly wouldn't even recognize it.

I'd avoid such a thing and opt for a view instead if possible. (It's easier to write too. ;))

```
CREATE VIEW main_with_total
AS
SELECT m.id,
       sum(s.amount) total
       FROM main m
            LEFT JOIN sub s
                      ON s.main_fk = m.id
       GROUP BY m.id;

```

(Here you'd get `NULL` as `total` for an ID where no `sub` records exist. If you want 0 instead change the expression for `total` to `coalesce(sum(s.amount), 0)`.)
I was able to figure it out pretty simply. Thanks to @Dale\_Burrell for the guidance in the comment. I found this link to be a more direct answer for the info I was after: <https://www.mssqltips.com/sqlservertip/2342/understanding-sql-server-inserted-and-deleted-tables-for-dml-triggers/>

In short, I needed to be referencing the built-in `inserted` and `deleted` tables to reference the triggering row. My question was immediately "But what about an Update?". An Update will utilize both of those tables (think about it, or use the above link for an explanation). Here's the corrected trigger:

```
CREATE TRIGGER Update_Main_Total
    ON Sub
    AFTER INSERT, UPDATE, DELETE
    AS BEGIN
        DECLARE @main_id INT;

        IF EXISTS (SELECT * FROM inserted)
            SELECT @main_id = Main_fk
            FROM inserted;

        ELSE
            SELECT @main_id = Main_fk
            FROM deleted;

        UPDATE Main
        SET Total = (
            SELECT SUM(Amount)
            FROM Sub
            WHERE Main_fk = @Main_Id
            )
        WHERE Id = @Main_Id;
    END;
GO

```