如何用Update触发器记录变更值？Items表字段变更存至Events表

Alright, let's walk through how to set up an UPDATE trigger to log every field change in your Items table to an Events log table. First, we need to make sure the Events table is structured to capture all the necessary details—since you didn't provide its schema, I'll start with a common, flexible setup that works for this use case.

First, let's build the Events table to store change details. This table will track which item was modified, which field changed, the old/new values, when the change happened, and who made it:

CREATE TABLE [dbo].[Events] (
    [EventId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ItemId] [int] NOT NULL, -- Links back to the Items.Id
    [ChangedField] [nvarchar(100)] NOT NULL, -- Name of the modified field
    [OldValue] [nvarchar(max)] NULL, -- Previous value (converted to string for consistency)
    [NewValue] [nvarchar(max)] NULL, -- Updated value
    [ChangeDate] [datetimeoffset](7) NOT NULL DEFAULT SYSDATETIMEOFFSET(), -- Timestamp of the change
    [ChangedBy] [nvarchar(128)] NOT NULL DEFAULT SUSER_SNAME() -- User who made the change
);

-- Optional: Add an index on ItemId to speed up querying logs for a specific item
CREATE NONCLUSTERED INDEX IX_Events_ItemId ON [dbo].[Events] (ItemId);

Now, let's write the trigger that fires after an update on Items, compares old and new values, and logs only the fields that actually changed:

CREATE TRIGGER [dbo].[trg_Items_LogFieldChanges]
ON [dbo].[Items]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- Prevent extra row-count messages from interfering with apps

    -- Insert a log entry for every field that changed in the updated rows
    INSERT INTO [dbo].[Events] (ItemId, ChangedField, OldValue, NewValue)
    SELECT
        i.Id AS ItemId,
        field.ChangedField,
        -- Convert old values to strings, handling different data types properly
        CASE 
            WHEN field.ChangedField IN ('StartDate', 'DueDate') THEN CONVERT(nvarchar(50), d.[field.ChangedField], 127)
            WHEN field.ChangedField IN ('Budget', 'Cost') THEN CONVERT(nvarchar(50), d.[field.ChangedField])
            ELSE CONVERT(nvarchar(max), d.[field.ChangedField])
        END AS OldValue,
        -- Convert new values the same way for consistency
        CASE 
            WHEN field.ChangedField IN ('StartDate', 'DueDate') THEN CONVERT(nvarchar(50), i.[field.ChangedField], 127)
            WHEN field.ChangedField IN ('Budget', 'Cost') THEN CONVERT(nvarchar(50), i.[field.ChangedField])
            ELSE CONVERT(nvarchar(max), i.[field.ChangedField])
        END AS NewValue
    FROM inserted i
    INNER JOIN deleted d ON i.Id = d.Id -- Link updated rows to their previous state
    -- Use CROSS APPLY to "unpivot" each field into a separate row for checking
    CROSS APPLY (
        VALUES
            ('Name', i.Name, d.Name),
            ('Description', i.Description, d.Description),
            ('ParentId', i.ParentId, d.ParentId),
            ('EntityStatusId', i.EntityStatusId, d.EntityStatusId),
            ('ItemTypeId', i.ItemTypeId, d.ItemTypeId),
            ('StartDate', i.StartDate, d.StartDate),
            ('DueDate', i.DueDate, d.DueDate),
            ('Budget', i.Budget, d.Budget),
            ('Cost', i.Cost, d.Cost),
            ('Progress', i.Progress, d.Progress),
            ('StatusTypeId', i.StatusTypeId, d.StatusTypeId),
            ('ImportanceTypeId', i.ImportanceTypeId, d.ImportanceTypeId)
            -- Add any additional Items fields here if you extend the table later
    ) AS field(ChangedField, NewVal, OldVal)
    -- Only log fields where the value actually changed (including NULL vs non-NULL)
    WHERE (field.NewVal <> field.OldVal)
       OR (field.NewVal IS NULL AND field.OldVal IS NOT NULL)
       OR (field.NewVal IS NOT NULL AND field.OldVal IS NULL);
END;

Let's break down what this trigger does and things to keep in mind:

• inserted & deleted Tables: These are system-generated virtual tables in SQL Server triggers. inserted holds the updated version of rows, deleted holds the pre-update version. We join them on Id to compare values.
• CROSS APPLY: This lets us turn each field comparison into a separate row, so we can log individual field changes instead of one big entry per row update.
• NULL Handling: The WHERE clause explicitly checks for NULL vs non-NULL changes, since NULL <> anything returns UNKNOWN in SQL and would be ignored otherwise.
• Data Type Conversion: We convert non-string fields (dates, decimals) to strings so they fit in the nvarchar(max) columns of Events—adjust the conversion formats if you need a different date/number display.
• Scalability: If you add new fields to Items later, you'll need to update the VALUES list in the CROSS APPLY section to include them in the logging.
• Performance: Triggers add overhead to update operations, so make sure the Events table is indexed appropriately (the IX_Events_ItemId index I added helps with querying logs for specific items).

内容的提问来源于stack exchange，提问作者Greg Gum