SQL Server 更新触发器，仅获取修改的字段

我知道COLUMNS_UPDATED，好吧，我需要一些快速的捷径（如果有人做了，我已经在做了，但如果有人可以节省我的时间，我会感激的）

我基本上需要一个仅包含更新的列值的 XML，我需要它用于复制目的。

SELECT * FROM Insert 为我提供了每一列，但我只需要更新的列。

像下面这样的东西

CREATE TRIGGER DBCustomers_Insert
    ON DBCustomers
    AFTER UPDATE
AS
BEGIN
    DECLARE @sql as NVARCHAR(1024);
    SET @sql = 'SELECT ';


    I NEED HELP FOR FOLLOWING LINE ...., I can manually write every column, but I need 
    an automated routin which can work regardless of column specification
    for each column, if its modified append $sql = ',' + columnname...

    SET @sql = $sql + ' FROM inserted FOR XML RAW';

    DECLARE @x as XML;
    SET @x = CAST(EXEC(@sql) AS XML);


    .. use @x

END

我有另一个完全不同的解决方案，它根本不使用 COLUMNS_UPDATED，也不依赖在运行时构建动态 SQL。 （您可能想在设计时使用动态 SQL，但那是另一回事了。）

基本上你从插入和删除的表 http://msdn.microsoft.com/en-us/library/ms191300.aspx，对每个字段进行逆透视，这样您就只剩下每个字段的唯一键、字段值和字段名称列。然后将两者结合起来并过滤出任何更改的内容。

这是一个完整的工作示例，包括一些测试调用以显示记录的内容。

-- -------------------- Setup tables and some initial data --------------------
CREATE TABLE dbo.Sample_Table (ContactID int, Forename varchar(100), Surname varchar(100), Extn varchar(16), Email varchar(100), Age int );
INSERT INTO Sample_Table VALUES (1,'Bob','Smith','2295','[email protected] /cdn-cgi/l/email-protection',24);
INSERT INTO Sample_Table VALUES (2,'Alice','Brown','2255','[email protected] /cdn-cgi/l/email-protection',32);
INSERT INTO Sample_Table VALUES (3,'Reg','Jones','2280','[email protected] /cdn-cgi/l/email-protection',19);
INSERT INTO Sample_Table VALUES (4,'Mary','Doe','2216','[email protected] /cdn-cgi/l/email-protection',28);
INSERT INTO Sample_Table VALUES (5,'Peter','Nash','2214','[email protected] /cdn-cgi/l/email-protection',25);

CREATE TABLE dbo.Sample_Table_Changes (ContactID int, FieldName sysname, FieldValueWas sql_variant, FieldValueIs sql_variant, modified datetime default (GETDATE()));

GO

-- -------------------- Create trigger --------------------
CREATE TRIGGER TriggerName ON dbo.Sample_Table FOR DELETE, INSERT, UPDATE AS
BEGIN
    SET NOCOUNT ON;
    --Unpivot deleted
    WITH deleted_unpvt AS (
        SELECT ContactID, FieldName, FieldValue
        FROM 
           (SELECT ContactID
                , cast(Forename as sql_variant) Forename
                , cast(Surname as sql_variant) Surname
                , cast(Extn as sql_variant) Extn
                , cast(Email as sql_variant) Email
                , cast(Age as sql_variant) Age
           FROM deleted) p
        UNPIVOT
           (FieldValue FOR FieldName IN 
              (Forename, Surname, Extn, Email, Age)
        ) AS deleted_unpvt
    ),
    --Unpivot inserted
    inserted_unpvt AS (
        SELECT ContactID, FieldName, FieldValue
        FROM 
           (SELECT ContactID
                , cast(Forename as sql_variant) Forename
                , cast(Surname as sql_variant) Surname
                , cast(Extn as sql_variant) Extn
                , cast(Email as sql_variant) Email
                , cast(Age as sql_variant) Age
           FROM inserted) p
        UNPIVOT
           (FieldValue FOR FieldName IN 
              (Forename, Surname, Extn, Email, Age)
        ) AS inserted_unpvt
    )

    --Join them together and show what's changed
    INSERT INTO Sample_Table_Changes (ContactID, FieldName, FieldValueWas, FieldValueIs)
    SELECT Coalesce (D.ContactID, I.ContactID) ContactID
        , Coalesce (D.FieldName, I.FieldName) FieldName
        , D.FieldValue as FieldValueWas
        , I.FieldValue AS FieldValueIs 
    FROM 
        deleted_unpvt d

            FULL OUTER JOIN 
        inserted_unpvt i
            on      D.ContactID = I.ContactID 
                AND D.FieldName = I.FieldName
    WHERE
         D.FieldValue <> I.FieldValue --Changes
        OR (D.FieldValue IS NOT NULL AND I.FieldValue IS NULL) -- Deletions
        OR (D.FieldValue IS NULL AND I.FieldValue IS NOT NULL) -- Insertions
END
GO
-- -------------------- Try some changes --------------------
UPDATE Sample_Table SET age = age+1;
UPDATE Sample_Table SET Extn = '5'+Extn where Extn Like '221_';

DELETE FROM Sample_Table WHERE ContactID = 3;

INSERT INTO Sample_Table VALUES (6,'Stephen','Turner','2299','[email protected] /cdn-cgi/l/email-protection',25);

UPDATE Sample_Table SET ContactID = 7 where ContactID = 4; --this will be shown as a delete and an insert
-- -------------------- See the results --------------------
SELECT *, SQL_VARIANT_PROPERTY(FieldValueWas, 'BaseType') FieldBaseType, SQL_VARIANT_PROPERTY(FieldValueWas, 'MaxLength') FieldMaxLength from Sample_Table_Changes;

-- -------------------- Cleanup --------------------
DROP TABLE dbo.Sample_Table; DROP TABLE dbo.Sample_Table_Changes;

因此，不要搞乱 bigint 位域和 arth 溢出问题。如果您知道在设计时要比较的列，那么您不需要任何动态 SQL。

缺点是输出采用不同的格式，并且所有字段值都转换为 sql_variant，第一个可以通过再次旋转输出来修复，第二个可以通过根据您对数据的了解重新转换回所需的类型来修复表的设计，但是这两者都需要一些复杂的动态sql。在 XML 输出中，这两个问题可能都不是问题。这question https://stackoverflow.com/questions/49758169/merge-pending-data-changes-into-a-view执行类似于以相同格式获取输出的操作。

编辑：查看下面的评论，如果您有一个可以更改的自然主键，那么您仍然可以使用此方法。您只需使用 NEWID() 函数添加默认填充有 GUID 的列。然后，您可以使用该列代替主键。

您可能想为此字段添加索引，但由于触发器中删除和插入的表位于内存中，因此可能不会被使用，并且可能会对性能产生负面影响。