Antonio Antonio - 5 months ago 9
SQL Question

Trigger is not working when comparing specific column

I created this trigger to everytime one column of a table is changed, another column of this table is set "null". This is my code:

ALTER TRIGGER [dbo].[MSF_TONKEN_CLEAR]
ON [dbo].[end]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE (ENDNO)
BEGIN
UPDATE end
SET TOKEN = Null
FROM end e2 INNER JOIN Inserted I ON e2.ID = I.ID
WHERE I.ENDNO <> c2.ENDNO
END
END


But it's not working. I update the column "ENDNO" and the TOKEN still there.
If I comment the line

--WHERE I.ENDNO <> c2.ENDNO


it works, but for every update, not only the update of the column "ENDNO". What am I doing wrong?

Answer

This is AFTER trigger.

Your comparison I.ENDNO <> e2.ENDNO will always be false, because INSERTED values are already in target table. You have to compare with DELETED.

    UPDATE [end] SET
      TOKEN = Null
    FROM [end] e2
    INNER JOIN DELETED d ON e2.ID = I.ID
    WHERE D.ENDNO <> e2.ENDNO

By the way IF UPDATE (ENDNO) is almost useless. It will be true even if nothing changed, e.g.

UPDATE ... SET ENDNO=ENDNO