UPDATE触发器异常：将NULL改为有效值时INSERTED/DELETED取值为NULL

问题背景 #

有一张AssociateMembers.AssociateMembershipApplication表，包含FoundSSO列（人员唯一标识符，非主键）。业务逻辑为：人员申请会员时生成新行，工作人员发现其曾是会员时，将旧SSO填入FoundSSO列，之后UPDATE触发器需根据该SSO查找关联PersonID并添加到记录中。

为排查问题，简化后的触发器代码如下：

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

执行更新语句：

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

发现FoundSSO已成功更新为新值，但触发器中@DeletedSSO和@InsertedSSO均为NULL。经测试，FoundSSO已有值时修改正常，但从NULL改为有效值时触发该问题。

问题原因 #

核心问题出在WHERE子句的i.FoundSSO<>d.FoundSSO条件上：SQL中NULL与任何值比较的结果都是UNKNOWN，不会返回TRUE。当原FoundSSO为NULL、新值为有效值时，i.FoundSSO<>d.FoundSSO等价于'ball1138' <> NULL，结果是UNKNOWN，导致这条记录不会被SELECT语句匹配到，最终变量无法赋值，保持NULL。

修复方案 #

需要修改WHERE条件，专门处理NULL的比较场景，以下两种方案可选：

方案1：使用IS NOT DISTINCT FROM（SQL Server 2016及以上版本支持） #

该运算符会将NULL视为相等的值，直接判断两个值是否不同（包括NULL和非NULL的差异场景）：

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 NOT (i.FoundSSO IS NOT DISTINCT FROM d.FoundSSO)

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

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

        END

END
GO

方案2：显式处理NULL场景（兼容所有SQL Server版本） #

如果使用的SQL Server版本不支持IS NOT DISTINCT FROM，可以用逻辑表达式覆盖NULL与非NULL的比较场景：

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 OR (d.FoundSSO IS NULL AND i.FoundSSO IS NOT NULL))

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

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

        END

END
GO

额外注意：当前触发器仅适配单行更新场景，若业务存在批量更新需求，标量变量会仅保留最后一行的赋值结果，建议改用基于集合的操作逻辑，避免依赖标量变量。

内容的提问来源于stack exchange，提问作者Andrew Richards