Bagaimana untuk Mendapatkan Hanya Medan Diubah Suai dengan Cekap daripada Pencetus Kemas Kini Pelayan SQL?

Mendapatkan Medan Diubah Suai Sahaja untuk Pencetus Kemas Kini Pelayan SQL

Pengenalan

Dalam senario ini, kami berhasrat untuk mendapatkan semula XML yang mengandungi hanya lajur yang diubah suai nilai semasa melaksanakan pencetus kemas kini pada pangkalan data SQL Server. Maklumat ini penting untuk tujuan replikasi data.

Pendekatan

Pilihan 1: Menggunakan Dynamic SQL (Pendekatan Deklaratif)

Satu kaedah melibatkan penggunaan SQL dinamik untuk membina kod yang mengekstrak nilai lajur yang diubah suai. Walau bagaimanapun, pendekatan ini boleh membosankan jika jadual mempunyai bilangan lajur yang banyak.

Pilihan 2: Nyahpaut dan Menyertai (Pendekatan Bukan Deklaratif)

Penyelesaian yang lebih cekap ialah menyahpangsi kedua-dua memasukkan dan memadam jadual, mencipta jadual dengan medan untuk kunci unik (ContactID), nama medan (FieldName) dan nilai medan (FieldValue). Dengan menyertai kedua-dua jadual ini, adalah mungkin untuk mengenal pasti mana-mana baris yang FieldValue telah berubah, memberikan kita nilai yang diubah suai.

Contoh

Pertimbangkan kod berikut:

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

Pendekatan bukan deklaratif ini mengendalikan perubahan, pemadaman dan sisipan dengan berkesan tanpa menggunakan SQL dinamik yang kompleks atau menghadapi aritmetik medan bit isu limpahan. Ia juga tidak terjejas oleh perubahan dalam kunci primer semula jadi selagi lajur GUID tambahan digunakan untuk pengenalan.

Atas ialah kandungan terperinci Bagaimana untuk Mendapatkan Hanya Medan Diubah Suai dengan Cekap daripada Pencetus Kemas Kini Pelayan SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!