Получение только измененных полей для запуска обновления SQL Server

введение

В этом сценарии мы стремимся получить XML, содержащий только модифицированные значения столбца при выполнении обновления на дату SQL Server Database. Эта информация имеет решающее значение для целей репликации данных.

подход

опция 1: использование динамического SQL (декларативный подход)

один метод включает в себя использование динамического SQL для создания кода, который извлекает модифицированные значения столбца. Однако этот подход может быть утомительным, если таблица имеет большое количество столбцов.

Вариант 2: Освобождение и соединение (не декларативный подход)

более эффективное решение-это раскрыть как вставленное, так и удаленное таблицы, создавая таблицу с помощью Fields для уникального ключа (контакт), поля (поля). Присоединив эти две таблицы, можно идентифицировать любые строки, в которых FieldValue изменилась, давая нам модифицированные значения.

]

пример

рассмотрим следующий код:

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, Forename, Surname, Extn, Email, 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, Forename, Surname, Extn, Email, Age FROM inserted) p
        UNPIVOT (FieldValue FOR FieldName IN (Forename, Surname, Extn, Email, Age)) AS inserted_unpvt
    )

    -- Join them and identify changes
    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
    OR (D.FieldValue IS NOT NULL AND I.FieldValue IS NULL)
    OR (D.FieldValue IS NULL AND I.FieldValue IS NOT NULL);
    
    -- Use the contents of Sample_Table_Changes for replication
END
GO

] Этот не декоративный подход обрабатывает изменения, удаления и вставки эффективно без использования сложных динамических SQL или обращения к вопросам переполнения арифметического битфилда. Также не влияют изменения в естественном первичном ключе, если для идентификации используется дополнительная колонка GUID.