9

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
  • 2
    A quick word on the use of "IF UPDATE(<column>)". It returns true if the DML specifies a value for the column, regardless of if the value actually changed or not. – Jonathan Fite Jul 22 at 12:28
18

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...
  • This is a lot more elegant than looking at the various column-updated functions. We combined those with some front-end code to only send in the changed values (after much wrangling). Using an EXCEPT makes a lot more sense. – Peter Schott Jul 29 at 14:05
  • 2
    This doesn't work in cases where 2 rows are "swapped" in an update. If we have two John Smiths who need their JobCodes updated (first John from 1 to 2; second John from 2 to 1) - this would say no update has occurred. – Steven Hibble Jul 29 at 15:19
  • 1
    @StevenHibble - While possible how probable is that to occur? That case could easily be remedied by including the PK columns in the Select statements above. – Chad Estes Jul 29 at 15:28
  • 1
    I would say the probability depends on the data source and the likelihood of bad data entry. "Oops, wrong John Smith..." doesn't seem like it would never happen. In any case, this doesn't address the other half of a multi-row update: how do you make sure to only insert the rows that change? This EXISTS checks that any row changed. If you keep the insert from the question, you'll then log all updated rows when only one changes in a meaningful way. – Steven Hibble Jul 29 at 15:44
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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.