Update触发器tr_trtest_update的description字段返回NULL问题求助

Alright, let's work through this issue step by step. You mentioned that your tr_log table is getting NULL values in the description field specifically when running UPDATE operations—DELETE and INSERT triggers work perfectly fine. Looking at the partial trigger code you shared, there are a couple of common missteps that are likely causing this problem.

Common Issues in Your Current Trigger #

• Error-prone row-by-row processing: Using a temporary table and WHILE loop to handle records one at a time is risky. If the loop fails to properly populate your @auditstring variable (for example, if a field wasn't updated and the variable stays unassigned), you'll end up inserting NULL into tr_log.
• NULL concatenation behavior: In SQL Server, any string concatenated with NULL results in NULL. If even one of your old/new value variables is NULL (like when a field wasn't modified in the update), the entire @auditstring becomes NULL.

Corrected Trigger Code #

Instead of relying on loops and variables, use SQL Server's set-based operations—this is how triggers are designed to work, and it eliminates NULL-related bugs:

ALTER TRIGGER tr_trtest_update 
ON trtest 
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra row-count messages from interfering with application logic

    -- Insert audit records directly using set-based logic
    INSERT INTO tr_log (description)
    SELECT 
        CONCAT(
            'Updated record ID: ', i.id,
            -- Only append name change details if the name actually changed
            CASE WHEN d.name <> i.name THEN CONCAT(' | Old Name: ', d.name, ' | New Name: ', i.name) ELSE '' END,
            -- Only append salary change details if the salary actually changed
            CASE WHEN d.salary <> i.salary THEN CONCAT(' | Old Salary: ', d.salary, ' | New Salary: ', i.salary) ELSE '' END
        ) AS audit_description
    FROM inserted i
    INNER JOIN deleted d ON i.id = d.id; -- Link new (inserted) and old (deleted) records via primary key
END

Why This Works #

• Set-based efficiency: We process all updated records in a single operation, no loops required. This is far more performant and avoids per-row variable bugs.
• Conditional concatenation: The CASE statements only add text if the field actually changed. This prevents NULL from sneaking into the description (we use empty strings instead of NULL for unchanged fields).
• Proper record pairing: Using INNER JOIN on the primary key (id) ensures we correctly match each new record in inserted with its old version in deleted.

Additional Checks #

• Double-check that your tr_log.description field doesn't have a default value of NULL (though the code above should avoid inserting NULL entirely).
• If you want to skip inserting records when no fields were actually modified (e.g., an UPDATE statement that sets a field to its current value), add a WHERE clause to the SELECT:

  WHERE d.name <> i.name OR d.salary <> i.salary
  

内容的提问来源于stack exchange，提问作者Ashwath Raj