SQL Server中如何通过触发器获取Point列更新记录并写入PointLog？

Absolutely you can capture these point updates and log them to a PointLog table using an AFTER UPDATE trigger in SQL Server. This is a common, straightforward use case for triggers—let’s walk through the implementation step by step:

First, set up a table to store all point change records. We’ll include key details to track the context of each update:

CREATE TABLE PointLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    AccountID INT NOT NULL, -- The account number (e.g., 100 in your example)
    OldPointValue INT NOT NULL, -- Point value before the update
    NewPointValue INT NOT NULL, -- Point value after the update
    UpdateDateTime DATETIME DEFAULT GETDATE(), -- Timestamp of the change
    ActionDescription NVARCHAR(100) -- Optional: note what triggered the change, like "Purchased MILK"
);

Next, build a trigger that fires after an update on your account table (we’ll assume your account table is named Accounts with columns AccountID and Point). This trigger will detect changes to the Point column and log the old/new values:

CREATE TRIGGER trg_Accounts_PointUpdate
ON Accounts
AFTER UPDATE
AS
BEGIN
    -- Prevent extra result sets from being returned to the application
    SET NOCOUNT ON;

    -- Only log changes where the Point column was actually modified
    IF UPDATE(Point)
    BEGIN
        INSERT INTO PointLog (AccountID, OldPointValue, NewPointValue, ActionDescription)
        SELECT 
            i.AccountID,
            d.Point AS OldPointValue,
            i.Point AS NewPointValue,
            'Purchased MILK' -- You can make this dynamic if you track transaction types elsewhere
        FROM inserted i
        JOIN deleted d ON i.AccountID = d.AccountID
        -- Optional: filter out cases where the point value didn't actually change
        WHERE i.Point <> d.Point;
    END
END;

Key details explained: #

• AFTER UPDATE: The trigger runs only after the update operation on Accounts completes successfully.
• INSERTED & DELETED tables: These are special system tables available inside triggers. DELETED holds the row values before the update, while INSERTED holds the values after. We join them on AccountID to link old and new point values.
• UPDATE(Point): This checks if the Point column was included in the update (even if the value stayed the same). The WHERE i.Point <> d.Point clause ensures we only log actual value changes.

Let’s simulate your example where account 100’s points jump from 50 to 75 after buying MILK:

-- Update the account's point value
UPDATE Accounts
SET Point = 75
WHERE AccountID = 100;

-- Verify the log entry was created
SELECT * FROM PointLog;

You’ll see a new entry in PointLog with AccountID=100, OldPointValue=50, NewPointValue=75, and the timestamp of the update.

• Batch updates: If you update multiple accounts at once, the trigger will handle all of them—each changed row gets a separate log entry, since we’re selecting from INSERTED/DELETED (which can hold multiple rows).
• Performance: Triggers add small overhead to update operations. Make sure PointLog is indexed appropriately (e.g., on AccountID if you frequently query logs by account) and avoid heavy logic inside the trigger.
• Transaction consistency: The trigger runs in the same transaction as the update. If the update rolls back, the log insert will also roll back—this keeps your account data and logs in sync.
• Dynamic action descriptions: If you have a column in Accounts or a related transaction table that tracks the reason for the point change, you can replace the hardcoded "Purchased MILK" with that column value.

内容的提问来源于stack exchange，提问作者nova tria