SQL Server Trigger- How to use each row of INSERTED

Question

Here is a simplified version of the problem:

Table1 with columns: name, color, shape, amount.

Table2 with columns: color, shape, amount

Table2 is supposed to keep the total amount of all color/shape pairs, for instance, if table1 had entries

Joe, blue, square, 2
Bob, red, square, 1
Alice, blue, square, 3

Then table2 should read:

blue, square, 5
red, square, 1

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!

EDIT: Final working version for insert trigger.

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/

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.

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/

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.

Answer 1

The issue with your MERGE is that it applies all of the changes, effectively, in parallel. So if you insert all 3 rows at once (rather than as separate statements) then for both blue, square rows, no existing row is found, and so both enter the NOT MATCHED arm of the MERGE and insert a row.

So you need to pre-merge any new rows which should only affect one row in the target table:

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)
;

---

For all possible operations, and with the requirement to eliminate only when the actual count of rows returns to 0, I'd again introduce a cnt column into Table2 and implement the trigger as:

create table dbo.Table1 (
    name varchar(10) not null,
    color varchar(10) not null,
    shape varchar(10) not null,
    amount int not null,
    constraint PK_Table1 PRIMARY KEY (name /*any other columns?*/)
)
go
create table dbo.Table2 (
    color varchar(10) not null,
    shape varchar(10) not null,
    amount int not null,
    cnt int not null
)
go
CREATE TRIGGER after_insert_table1 ON table1
AFTER INSERT, UPDATE, DELETE  
AS
BEGIN

MERGE table2 AS TARGET
USING (select color,shape,SUM(amount) as amount,SUM(Cnt) as Cnt from
        (select color,shape,amount,1 as Cnt from INSERTED
         union all
         select color,shape,-amount,-1 from deleted) t
       group by color,shape)  AS SOURCE
ON TARGET.color = SOURCE.color
AND TARGET.shape = SOURCE.shape
WHEN MATCHED AND TARGET.cnt + SOURCE.cnt = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE 
SET
    TARGET.amount = (TARGET.amount + SOURCE.amount),
    TARGET.cnt= (TARGET.cnt + SOURCE.cnt)
WHEN NOT MATCHED THEN
INSERT (color,shape,amount,cnt)
VALUES(SOURCE.color, SOURCE.shape, SOURCE.amount,SOURCE.cnt)
;

END
go
insert into dbo.Table1 (name,color,shape,amount) values
('Joe', 'blue', 'square', 2),
('Bob', 'red', 'square', 1),
('Alice', 'blue', 'square', 3)
go
select * from Table2
go
delete from dbo.Table1 where name='Bob' or name='Alice'
go
select * from Table2
go
update dbo.Table1 set Amount = 0
go
select * from Table2

---

If you wanted to do this as an indexed view instead, here's how:

create table dbo.Table1 (
    name varchar(10) not null,
    color varchar(10) not null,
    shape varchar(10) not null,
    amount int not null,
    constraint PK_Table1 PRIMARY KEY (name /*any other columns?*/)
)
go
create view dbo.Table2
with schemabinding
as
    select color,shape,SUM(amount) as amount
        ,COUNT_BIG(*) as cnt /* Needed to make indexed view possible */
    from dbo.Table1
    group by color,shape
go
create unique clustered index IX_Table2 on Table2(color,shape)
go
insert into dbo.Table1 (name,color,shape,amount) values
('Joe', 'blue', 'square', 2),
('Bob', 'red', 'square', 1),
('Alice', 'blue', 'square', 3)
go
select * from Table2

The advantage here is that effectively, SQL Server writes the triggers (but they're hidden away from you, there's no way to see them) and automatically maintains Table2. And those implementations have been tested on (thousands, millions, more?) systems - they definitely work.

You do have to accept the additional Cnt column in the view - it's a requirement for being able to do the SUM().

Answer 2

I'd like to present an alternate solution that also handles DELETE equally as well as INSERT and UPDATE, all in one trigger. I chose to delete when the total count for a group is 0 (either through deletion, through setting explicitly to zero, or through having positive and negative values summing to 0).

CREATE TABLE dbo.NameShape (
   Name varchar(30) NOT NULL CONSTRAINT PK_NameShape PRIMARY KEY CLUSTERED,
   Color varchar(20) NOT NULL,
   Shape varchar(20) NOT NULL,
   Amount int NOT NULL
);

CREATE TABLE dbo.ShapeCount (
  Color varchar(20) NOT NULL,
  Shape varchar(20) NOT NULL,
  Total int NOT NULL
);

Then the trigger looks like this:

CREATE TRIGGER TR_NameShape_IUD ON dbo.NameShape FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRAN;
WITH ColorShapes AS (
   SELECT
      Color,
      Shape
   FROM Inserted
   UNION
   SELECT
      Color,
      Shape
   FROM Deleted
)
SELECT
   CS.Color,
   CS.Shape,
   Total = Sum(Coalesce(S.Amount, 0))
INTO #NewValues
FROM
   ColorShapes CS
   LEFT JOIN dbo.NameShape S WITH (ROWLOCK, HOLDLOCK)
      ON CS.Color = S.Color
      AND CS.Shape = S.Shape
GROUP BY
   CS.Color,
   CS.Shape
;

DELETE SC
FROM
   dbo.ShapeCount SC
   INNER JOIN #NewValues V
      ON SC.Color = V.Color
      AND SC.Shape = V.Shape
WHERE
   V.Total = 0
;

UPDATE SC
SET SC.Total = V.Total
FROM
   dbo.ShapeCount SC
   INNER JOIN #NewValues V
      ON SC.Color = V.Color
      AND SC.Shape = V.Shape
WHERE
   SC.Total <> V.Total
;

INSERT dbo.ShapeCount (Color, Shape, Total)
SELECT
   V.Color,
   V.Shape,
   V.Total
FROM
   #NewValues V
WHERE
   V.Total <> 0
   AND NOT EXISTS (
      SELECT *
      FROM dbo.ShapeCount SC
      WHERE
         V.Color = SC.Color
         AND V.Shape = SC.Shape
   )
;
COMMIT TRAN

See a Live Demo at SQL Fiddle

I don't want to scare you, yet I think you should be informed that MERGE may have some issues in some situations.

It would be possible to use my first query above as the source for a MERGE statement, but be careful: you'll have to filter out any non-affected rows. Note that adding the DELETE to the merge will be just a tad difficult as you'll have to ensure you don't delete shapes/colors that were not involved in the DELETE.

Answer 3

Actually, INSERTED is a special table.

Try this:

CREATE TRIGGER after_insert_table1 ON table1
AFTER INSERT 
AS
BEGIN
    IF NOT EXISTS (
        SELECT *
          FROM table2
          JOIN INSERTED
            ON table2.color = INSERTED.color
           AND table2.shape = INSERTED.shape)
        INSERT INTO table2(color,shape,amount)
        SELECT INSERTED.color,
               INSERTED.shape, 
               INSERTED.amount
          FROM INSERTED;
    ELSE
        UPDATE table2
           SET amount = (amount + INSERTED.amount)
          FROM table2
          JOIN INSERTED
            ON color = INSERTED.color
           AND shape = INSERTED.shape;
END