Bagaimana untuk Cekap Menangkap Medan Diubah Suai dalam SQL Server UPDATE Pencetus Menggunakan XML atau Kaedah Alternatif?

Pencetus Kemas Kini yang Cekap untuk Menangkap Medan Diubah Suai dalam Pelayan SQL

Masalah:

Kaedah COLUMNS_UPDATED diketahui, tetapi teknik yang lebih pantas diperlukan untuk menangkap nilai medan yang diubah suai dalam format XML untuk replikasi tujuan.

Pertanyaan Rutin Automasi:

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


    -- HELP NEEDED FOR FOLLOWING LINE...
    -- I can manually write every column, but need an automated way that works with any column specifications
    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

Penyelesaian Alternatif:

Daripada menggunakan COLUMNS_UPDATED, respons ini membentangkan kaedah alternatif yang menangani masalah tersebut. Ia melibatkan menyahpaut kedua-dua jadual yang disisipkan dan dipadamkan untuk mencipta kunci, nilai medan dan lajur nama medan. Dengan menyertai dan menapis dua jadual tidak berpivot ini, hanya baris yang diubah suai, dipadam dan disisipkan dikenal pasti.

Contoh:

-- Setup tables and 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]',24);
...

-- Create trigger
CREATE TRIGGER TriggerName ON dbo.Sample_Table FOR DELETE, INSERT, UPDATE AS
BEGIN
    -- Unpivot deleted and inserted tables
    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
    ),
    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 tables and capture 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 -- 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

-- Execute changes
UPDATE Sample_Table SET age = age+1;
...

-- Output results
SELECT *, SQL_VARIANT_PROPERTY(FieldValueWas, 'BaseType') FieldBaseType, SQL_VARIANT_PROPERTY(FieldValueWas, 'MaxLength') FieldMaxLength from Sample_Table_Changes;

Faedah daripada Penyelesaian Alternatif:

• Mengelakkan isu dengan medan bit besar dan limpahan aritmetik
• Format output dan penghantaran jenis data yang boleh disesuaikan
• Mengendalikan senario dengan menukar kunci primer semula jadi dengan menggunakan lajur yang diisi GUID sebagai pengecam unik

Atas ialah kandungan terperinci Bagaimana untuk Cekap Menangkap Medan Diubah Suai dalam SQL Server UPDATE Pencetus Menggunakan XML atau Kaedah Alternatif?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!