0

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.

Improve this question
4
  • Have you already considered and rejected using an indexed view for table2 instead of a physical table and this trigger? It would do the tallying automatically, be maintained by the system, and you don't have to write any triggers. Commented Dec 20, 2013 at 7:26
  • Thanks for chiming in Damien. This is an exercise in triggers so I won't be able to use views instead. Commented Dec 20, 2013 at 7:36
  • 1
    I've added the solution to fix your MERGE to the top of my answer - but in any real world scenario, I'd still recommend the view instead. Commented Dec 20, 2013 at 7:41
  • I've edited a complete script that applies for all operations (INSERT, UPDATE and DELETE) and follows your prescription for only deleting when there are no actual rows in Table1 into the middle of my answer. But I've added a Cnt column into Table2 - this makes the trigger efficient because we don't have to search Table1, only inserted and deleted to work out whether we need to delete a row - and is in fact why I have to include such a column in the indexed view version (because SQL Server wants to write efficient "triggers" for the view) Commented Dec 20, 2013 at 10:38

3 Answers 3

Reset to default
2

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().

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

The top part of your answer did the trick! Thanks for helping out!
1

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.

Improve this answer

2 Comments

Thanks Erike, however I can't just delete when the total is 0; 0 will be a valid value. I will try to wrap my head around the rest of the code, meanwhile, could you take a look at my attempt at it?
How about a delete after a delete only? If actually 0, to leave the value in the table?
0

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
Improve this answer

4 Comments

This will break badly for certain scenarios > 1 row. You probably want a MERGE statement.
ta.speot.is , you are correct, in doesn't work for inserting multiple rows at a time. Could you elaborate on how to use MERGE? I will read up on it in the mean time.
@ErikE now that I have finished the insert trigger, I will be working on the delete trigger. This is just for an exercise that assumes only inserts and deletes (no updates) for table1.
@dougyd See my answer which has only a single trigger for all three DML statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.