Trigger for handling multiple rows insert

Question

I know this might be duplicate question. Refering to the answer in this question, it uses cursor in the trigger to handle multiple rows insert which could reduce performance. And there should be solution from the Docs here, but I got no idea how to get it to work. Is there any other way to fire trigger after multiple rows insert?

Here is the example of my trigger:

ALTER TRIGGER [dbo].[UpdateTaskStatus] 
ON [dbo].[STG_CANCEL_TASK]   
AFTER INSERT
AS 
BEGIN

DECLARE @NO_HC VARCHAR (20) = NULL;
DECLARE @taskId VARCHAR (80) = NULL;
DECLARE @START_DATE DATETIME = NULL;

SELECT @NO_HC = RTRIM(LTRIM(INSERTED.NO_HC)) FROM INSERTED;

SELECT @START_DATE = START_DTM 
    FROM MSS.dbo.TR_TASK 
    WHERE NO_HC = @NO_HC

SET @taskId  = (SELECT TASK_ID 
                FROM MSS.dbo.TR_TASK 
                WHERE NO_HC = @NO_HC)

IF (@START_DATE IS NOT NULL)
    BEGIN
        UPDATE MCS.dbo.STG_CANCEL_TASK 
            SET RESULT = 'Error',
            TASK_ID = @taskId
            WHERE NO_HC = @NO_HC 
    END
ELSE
    BEGIN
        UPDATE MCS.dbo.STG_CANCEL_TASK 
            SET RESULT = 'Success',
            TASK_ID = @taskId
            WHERE NO_HC = @NO_HC 
    END
END;

And here is the example of the insert statement:

INSERT INTO STG_CANCEL_TASK (NO_HC)
VALUES ('2018032801'),
       ('2018032802'),
       ('2018032803'),
       ('2018032804'),
       ('2018032805')

Update: Currently I modified my trigger to something like this and it worked for now. I am not sure if this is the proper way.

ALTER TRIGGER [dbo].[UpdateTaskStatus] 
ON [dbo].[STG_CANCEL_TASK]   
AFTER INSERT
AS 
BEGIN

DECLARE @NO_HC VARCHAR (20) = NULL;
DECLARE @taskId VARCHAR (80) = NULL;
DECLARE @START_DATE DATETIME = NULL;

SELECT NO_HC INTO #tempInserted FROM INSERTED

WHILE EXISTS (SELECT 1 FROM #tempInserted)
BEGIN
    SELECT TOP 1 @NO_HC = NO_HC FROM #tempInserted
    SET @START_DATE = (SELECT START_DTM FROM MSS.dbo.TR_TASK where NO_HC = @NO_HC)
    SET @taskId = (SELECT TASK_ID FROM MSS.dbo.TR_TASK where NO_HC = @NO_HC)

    IF (@START_DATE IS NOT NULL)
    BEGIN
        UPDATE MCS.dbo.STG_CANCEL_TASK 
            SET RESULT = 'Error',
            TASK_ID = @taskId
            WHERE NO_HC = @NO_HC
            -- do more here...
    END
    ELSE
    BEGIN
        UPDATE MCS.dbo.STG_CANCEL_TASK 
            SET RESULT = 'Success',
            TASK_ID = @taskId
            WHERE NO_HC = @NO_HC
            -- do more here...
    END
    DELETE FROM #tempInserted WHERE NO_HC = @NO_HC
END
DROP #tempInserted
END;

marc_s · Accepted Answer · 2018-03-29 05:52:41Z

2

something along below lines will help

UPDATE M
SET m.result = CASE
                  WHEN i.result IS NULL
                     THEN 'error' 
                     ELSE 'success' 
               END
FROM inserted i
JOIN MCS.dbo.STG_CANCEL_TASK M ON m.task_id-i.task_id
                               AND m.NO_HC = RTRIM(LTRIM(i.NO_HC))
                               AND m.START_DTM = i.START_DTM

edited Mar 29, 2018 at 5:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 29, 2018 at 4:33

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

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

3 Comments

aldr Over a year ago

Thank you for the answer, it helped a lot for simple trigger like above. But actually the trigger I created is not that simple, so I modified my trigger by populating temporary table.

TheGameiswar Over a year ago

i have tried to modify your code and joined inserted table based on that,you may have to modify based on your needs.There are still some red flags in your revised code like this SELECT START_DTM FROM MSS.dbo.TR_TASK where NO_HC = @NO_HC ..when there is more than one row, you may face issues with incorrect data

aldr Over a year ago

Yep, you're right. That's why I said it worked for now because NO_HC in TR_TASK is unique and it's handled by Java code.

Collectives™ on Stack Overflow

Trigger for handling multiple rows insert

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related