SQL: Trigger is not collecting every row

Question

I have problem with trigger that should update one table when there i new row in other table.

I have two tables:

First:

SELECT 
    [Id], [Timestamp], [MachineName], [StatusId], [Quantity]
FROM [dbo].[Events]

And second one:

SELECT 
    [Id], [MachineName], [StatusId], [QuantitySum], [StatusLastRefresh]
FROM [dbo].[ActualParams]

To table [Events] goes every status change on every machine, that came from Stream Analytics. With every new row the second table should be updated and show last value for every machine, with last Timestamp in StatusLastRefresh. Actually table [ActualParams] has 4 rows of data.

I've tried 2 triggers to do so:

ALTER TRIGGER [dbo].[AfterInsertEvent] 
ON [dbo].[Events] 
AFTER INSERT
AS 
    MERGE ActualParams AS ap
    USING (SELECT Id, Machine, Status, Timestamp, Quantity 
           FROM inserted) AS ev ON ev.MachineName = ap.MachineName

    WHEN MATCHED THEN
       UPDATE SET map.StatusId = ev.StatusId, 
                  ap.StatusLastRefresh = ev.Timestamp, 
                  ap.QuantitySum = ap.QuantitySum + ev.Quantity;

Trigger #2:

ALTER TRIGGER [dbo].[AfterInsertEvent] 
ON [dbo].[Events] 
AFTER INSERT
BEGIN
    DECLARE @mn nchar(10), @si int, @ts datetime2(7), @q int

    SELECT @mn = MachineName, @si = StatusId, @ts = Timestamp, @q = Quantity 
    FROM inserted

    UPDATE ActualParams
    SET StatusId = @si, 
        StatusLastRefresh = @ts, 
        QuantitySum = QuantitySum + @q  
    WHERE ActualParams.MachineName = @mn
END

but none of them works 100% correctly.

First, the Merge trigger works ok when inserted rows are one for each MachineName at the same time, but sometimes there are few new rows at the same time for the same MachineName inserted and I get an error that it can't write many rows to one simultaneously and everything get stuck.

Second, simpler Update trigger sometimes skips a row and in ActualParams table there's previous StatusId value, not actual. Actually I have 4 machines and usually one of them have wrong StatusId. I wonder what happens when there will be 20 or more machines.

MachineName is unique for every machine.

I tried CURSOR with UPDATE but still sometimes the row is skipped, and status is not actual.

I don't know how to combine CURSOR with MERGE, and maybe that will be solution for that.

It must reacts on every row because I also want to have summarized quantity in second table, so that's why i can't skip any row (a forgot about it earlier - edited)

Any ideas? I'm using SSMS 17.

Most of all: ***AVOID*** cursors inside triggers **AT ALL COSTS!** Cursors are horrible, slow, and hardly ever needed - and triggers should be very small, nimble and fast - do **NOT** mix those two! Worst performance nightmare you can bring on yourself.... — marc_s, Apr 14 '20 at 19:32

score 0 · Accepted Answer · answered Apr 14 '20 at 16:55

Why wouldn't you just write this with an update/join?

UPDATE ap
    SET StatusId = i.StatusId,
        StatusLastRefresh = i.TimeStamp
    FROM ActualParams ap
         inserted i
         ON ap.MachineName = i.MachineName;

If the same machine can be updated multiple times in one insert, then use window functions to get the last row:

UPDATE ap
    SET StatusId = i.StatusId,
        StatusLastRefresh = i.TimeStamp
    FROM ActualParams ap
         (SELECT i.*,
                 ROW_NUMBER() OVER (PARTITION BY MachineName ORDER BY TimeStamp DESC) as seqnum
          FROM inserted i
         ) i
         ON ap.MachineName = i.MachineName AND seqnum = 1;

Simplicity works great - that first code. I just added "JOIN" before "inserted i" because it seemed missed. After over 500 new rows no losses. Thanks! :) — maidey, Apr 14 '20 at 19:55

SQL: Trigger is not collecting every row

1 Answers1