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?
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