Bagaimana untuk Mendapatkan Hanya Medan Yang Dikemas Kini Dengan Cekap dalam Replikasi Pelayan SQL?

Mendapatkan Medan Kemas Kini untuk Replikasi dalam SQL Server

Pengenalan

Apabila mereplikasi data antara pangkalan data SQL Server, adalah perlu untuk mengenal pasti medan yang telah diubah suai. Mekanisme lalai, COLUMNS_UPDATED, menyediakan perwakilan bitwise bagi medan yang dikemas kini. Walau bagaimanapun, artikel ini membentangkan pendekatan alternatif untuk mendapatkan semula coretan XML yang mengandungi hanya nilai lajur yang dikemas kini.

Fungsi Pencetus Kemas Kini Tersuai

Untuk mencapai ini, buat tersuai kemas kini fungsi pencetus seperti berikut:

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

    WITH UpdatedColumns AS (
        SELECT c.name AS ColumnName
        FROM sys.columns AS c
        JOIN inserted AS i ON c.object_id = i.object_id
        WHERE c.is_identity = 0
            AND i.Original_value <> i.value
    )
    SELECT @sql = @sql + ',' + ColumnName
    FROM UpdatedColumns

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

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

    .. use @x

END

Penyelesaian Alternatif tanpa COLUMNS_UPDATED

Daripada COLUMNS_UPDATED, pendekatan yang berbeza melibatkan menyahpaut jadual yang dimasukkan dan dipadam, menyertainya dan menapis untuk sebarang perubahan:

CREATE TRIGGER TriggerName ON dbo.Sample_Table FOR DELETE, INSERT, UPDATE AS
BEGIN
    SET NOCOUNT ON;

    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
    ),
    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
    )

    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

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan Hanya Medan Yang Dikemas Kini Dengan Cekap dalam Replikasi Pelayan SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!