SQL saving shadow table for a column on value change

the code that I have now updates the shadow table correctly but the only problem I'm having is that if the email value is the same it will still update the shadow table. This is not want i want. I need the shadow table to update only when the email is changed not updated how could i do this? example "[email protected]" original value should only update if the table changes that value to something different like "[email protected]"

CREATE TRIGGER AspNetUsersEmail_trigger
ON AspNetUsers
AFTER UPDATE 
AS
*  IF ( UPDATE (Email) )* what could I use here instead of update?
    BEGIN
    INSERT INTO [dbo].[AspNetUserEmailAudit]([UserId],[UserName],[Email],[NormalizedEmail],[FirstName],[LastName])
    SELECT Id,[UserName],[Email],[NormalizedEmail],[FirstName],[LastName] 
    FROM INSERTED
    END;

Solution

You need to compare the inserted and deleted tables to check if the data has actually been changed.

The easiest way to do this while still taking into account nulls, is to use EXCEPT. Make sure to compare against the same primary key.

Note that you can still use IF(UPDATE as an early bail-out condition, but it will only bail out if the column was not present at all. It won't work if the column is there but data did not change.

CREATE TRIGGER AspNetUsersEmail_trigger
ON AspNetUsers
AFTER UPDATE 
AS

SET NOCOUNT ON;

IF (UPDATE(Email))
    INSERT INTO [dbo].[AspNetUserEmailAudit]
        (UserId, UserName, Email, NormalizedEmail, FirstName, LastName)
    SELECT Id, UserName, Email, NormalizedEmail, FirstName, LastName
    FROM inserted i
    WHERE EXISTS (SELECT i.Id, i.Email   -- always include the primary key
                  EXCEPT
                  SELECT d.Id, d.Email
                  FROM deleted d);

GO