Using Merge with Trigger in SQL Server 2012

Question

I am facing a problem related to merge statement I have merge statement like this

MERGE abc A 
USING xyz B ON A.trans_date = b.trans_date 

WHEN matched AND B.trans_date IS NOT NULL 
   THEN 
       UPDATE 
          SET A.column1 = B.column1, 
              A.column2 = B.column2, 
              A.column3 = B.column3 

WHEN NOT matched BY target AND B.trans_date IS NOT NULL 
   THEN 
      INSERT (column1, column2, column3) 
      VALUES (column1, column2, column3);

And having a trigger on table ABC like this

ALTER TRIGGER [dbo].[TRG_ABC] 
ON [dbo].[Z_ABC] 
FOR UPDATE 
AS 
    DECLARE @Column4 NUMERIC(9), @Column5 CHAR(1)

    SELECT @Column4 = Column4, @Column5 = Column5 
    FROM inserted 

    UPDATE ABC 
    SET Column5 = CASE 
                     WHEN @Column5 = 'S' 
                        THEN 'Y' 
                        ELSE CASE
                                WHEN @Column5 = 'N' 
                                   THEN 'N' 
                                   ELSE 'U' 
                             END
                  END 
    WHERE Column4 = @Column4

This trigger is not working for updating each row. Is there any wrong with code or any solution for updating each row?

Gao Shenghan · Accepted Answer · 2024-05-21 09:48:01Z

4

In Sql Server, Trigger will be called at statement level not row level. So the Inserted magic table will have all the updated/inserted records.

Alter the trigger like this

UPDATE a
SET    Column5 = CASE
       WHEN Column5 = 'S' THEN 'Y'
       WHEN Column5 = 'N' THEN 'N'
       ELSE 'U'
       END
FROM   ABC a
INNER JOIN inserted i
ON i.Column4 = a.Column4

edited May 21, 2024 at 9:48

Gao Shenghan

4466 silver badges15 bronze badges

answered Jan 16, 2018 at 12:42

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

Comments

Eralper · Accepted Answer · 2018-01-16 12:43:40Z

2

Code within Triggers should contain SET based operations

You have declared variables, this will cause use of only the last affected row data

So your inner UPDATE will cause faulty results

You can refer to usage of merge command in trigger

answered Jan 16, 2018 at 12:43

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Collectives™ on Stack Overflow

Using Merge with Trigger in SQL Server 2012

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related