Update trigger - both INSERTED and DELETE hold same value after update

Question

I have a table called AssociateMembers.AssociateMembershipApplication containing a column called FoundSSO, which is a unique identifier for a person (but not the primary key).

In brief the concept is that a person applying for membership of an organisation will cause a new row in this table. As part of their application, staff will check if they've been a member before, and if so, add their previous SSO into this column. Then, the Update trigger will search for the PersonID relating to that found SSO, and add it to the record.

As I'm having trouble getting the trigger to do what I want, I've simplified it to troubleshoot, and now have this code:

CREATE TRIGGER AssociateMembers.trgApplicationSSOUpdated 
   ON  AssociateMembers.AssociateMembershipApplication 
   AFTER UPDATE
AS 
BEGIN

    SET NOCOUNT ON;
    DECLARE @DeletedSSO VARCHAR(20)
    DECLARE @InsertedSSO VARCHAR(20)

    IF UPDATE(FoundSSO)     
        BEGIN
            SELECT @DeletedSSO=d.FoundSSO, @InsertedSSO=i.FoundSSO
            FROM INSERTED i 
            INNER JOIN DELETED d ON i.AssociateMemberApplicationID = d.AssociateMemberApplicationID
            WHERE i.FoundSSO IS NOT NULL
            AND i.FoundSSO<>d.FoundSSO

            PRINT 'InsertedSSO:'
            PRINT ISNULL(@InsertedSSO,'NULL')

            PRINT 'DeletedSSO:'
            PRINT ISNULL(@DeletedSSO,'NULL')

        END

END
GO

When I issue this update command:

UPDATE AssociateMembers.AssociateMembershipApplication
SET FOUNDSSO='ball1138'
WHERE AssociateMemberApplicationID=64

The row is being updated with the new SSO, but both the variables hold NULL.

My first thought was that there wasn't an update happening, but I've double-checked that there is indeed a change. However, this did reveal that the trigger works when there was already a value in FoundSSO and it's changed, but not when the existing FoundSSO was NULL and we're updating it to hold a value for the first time - which will be the situation most of the time.

What am I missing about the logic of my trigger? And how should I update it to work when the user updates the row from a NULL in this column to a value?

Answer 1

Now that we can see what you actually want to achieve from your answer:

You have a number of fatal flaws in your trigger:

• To compare nulls, either use OR a IS NULL AND b IS NOT NULL etc, or use the new IS DISTINCT FROM. Do not use ISNULL as that means that '' and NULL compare as the same.
• Triggers can run on zero or multiple rows, do not assume the trigger is running on exactly one row.
• The UPDATE function only tells you if the column was present in the UPDATE statement, not if any rows were affected or if the column value ended being set to the same thing.
• You have a hidden cross-join in the UPDATE statement in your new answer. You need to always use the table alias on the first line to make the update, otherwise it just cross-joins the whole table again.

CREATE OR ALTER TRIGGER AssociateMembers.trgApplicationSSOUpdated 
   ON  AssociateMembers.AssociateMembershipApplication 
   AFTER UPDATE
AS 

SET NOCOUNT ON;

IF NOT UPDATE(FoundSSO) OR NOT EXISTS (SELECT 1 FROM inserted)
    RETURN;    -- early bailout

UPDATE ama  -- use alias
SET FoundPersonID = CASE WHEN i.FoundSSO IS NOT NULL THEN P.PersonID END
FROM AssociateMembers.AssociateMembershipApplication ama 
INNER JOIN INSERTED i ON ama.AssociateMemberApplicationID = i.AssociateMemberApplicationID
INNER JOIN DELETED d ON i.AssociateMemberApplicationID = d.AssociateMemberApplicationID
INNER JOIN People.People p ON i.FoundSSO = p.SSO
WHERE i.FoundSSO IS DISTINCT FROM d.FoundSSO;

Exactly why you need a trigger anyway is not clear: your application should just do the right thing in the first place.

Note also that because you are using INNER JOIN, if the FoundSSO is not matched then you may get no changes. You may want to do instead:

LEFT JOIN People.People p ON i.FoundSSO = p.SSO