XML または代替メソッドを使用して SQL Server UPDATE トリガーの変更されたフィールドを効率的にキャプチャする方法

SQL Server で変更されたフィールドをキャプチャするための効率的な更新トリガー

問題:

COLUMNS_UPDATED メソッドは既知ですが、レプリケーション用に変更されたフィールド値を XML 形式でキャプチャするには、より迅速な技術が必要です

自動ルーチン クエリ:

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

代替解決策:

COLUMNS_UPDATED を使用する代わりに、この応答は次のように表示されます。問題に対処する代替方法。これには、挿入されたテーブルと削除されたテーブルの両方をアンピボットして、キー、フィールド値、およびフィールド名の列を作成することが含まれます。これら 2 つのピボットされていないテーブルを結合してフィルタリングすることにより、変更、削除、および挿入された行のみが識別されます。

例:

-- 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;

代替解決策:

• bigint の問題を回避しますビットフィールドと算術オーバーフロー
• カスタマイズ可能な出力形式とデータ型キャスト
• GUID が設定された列を一意の識別子として使用して、自然主キーを変更するシナリオを処理します

以上がXML または代替メソッドを使用して SQL Server UPDATE トリガーの変更されたフィールドを効率的にキャプチャする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。