How is a T-SQL Trigger that only fires on real changes made?

Question

I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it has a blind spot. It only tests that some value came in. I need to go deeper, I need to compare the old and new values to see of a true change has occurred. It has to work with both INSERT and UPDATE.

In the case of an UPDATE that's easy because both the inserted and deleted tables have values I can compare within the trigger. However, for the INSERT only the insert table has values. Because I need this all in the same trigger, how do I handle that INSERT case?

Here is the script of the trigger I want to modify:

ALTER TRIGGER [dbo].[trATPerson_alter] 
   ON  [mydb].[dbo].[AT_Person]
   AFTER INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- Not all updates require a push
    IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
    BEGIN
        INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
                [Facility],
                [VendorID],
                [Person_code],
                [First_Name],
                [Last_Name],
                [JobCode],
                [Alink],
                [Inactive]
            )
        SELECT  [Facility],
                [VendorID],
                [Person_code],
                [First_Name],
                [Last_Name],
                [JobCode],
                [Alink],
                [Inactive]
        FROM inserted 
    END
END

Answer 1

You can handle both INSERT and UPDATE with an EXCEPT set operator. The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.

IF EXISTS (
           SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
           EXCEPT
           SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
          )
BEGIN...

Answer 2

In case an update can affect multiple rows, you have to protect against two things:

1. We want to consider updates that swap values between similar rows. If there are two John Smiths who need their JobCodes updated (first John from 1 to 2; second John from 2 to 1), we need to be careful to say they both were updated.
2. We only want to log the changed rows in AT_Person_To_Push. If 5 rows are updated, but only 2 are updated in a way that we care about, then we need to process only the 2 relevant rows.

Here's how I would handle it:

1. Left join inserted to deleted, because inserted will have rows for inserts and updates while deleted will only have rows for updates.
2. Use EXISTS with EXCEPT to find rows where the inserted values differ from the deleted values. You can't use i.First_Name != d.First_Name OR i.Last_Name != d.Last_Name... because the deleted table will be empty (and the LEFT JOIN will return nulls) when the trigger is handling an INSERT.
3. Insert only the affected rows into AT_Person_To_Push.

ALTER TRIGGER [dbo].[trATPerson_alter] 
   ON  [mydb].[dbo].[AT_Person]
   AFTER INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
            [Facility],
            [VendorID],
            [Person_code],
            [First_Name],
            [Last_Name],
            [JobCode],
            [Alink],
            [Inactive]
        )
    SELECT  i.[Facility],
            i.[VendorID],
            i.[Person_code],
            i.[First_Name],
            i.[Last_Name],
            i.[JobCode],
            i.[Alink],
            i.[Inactive]
    FROM inserted i
         LEFT JOIN deleted d
           ON i.Person_code = d.Person_code
    -- Check for changes that require a push
    WHERE EXISTS (SELECT i.[First_Name], i.[Last_Name], i.[JobCode], i.[Inactive]
                  EXCEPT
                  SELECT d.[First_Name], d.[Last_Name], d.[JobCode], d.[Inactive]);
END

Answer 3

Try this,

Declare @Acton int=0

If exists (Select 1 from inserted)
set @Acton=1

If exists (Select 1 from deleted)
set @Acton=@Acton+2

if(@Action=1) -- Only insert

if(@Action=3) -- Only Update
begin
IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
Begin

End
end

if(@Action=2) -- Only Delete