首頁 > 資料庫 > mysql教程 > 如何使用 XML 或替代方法有效擷取 SQL Server UPDATE 觸發器中的修改欄位?

如何使用 XML 或替代方法有效擷取 SQL Server UPDATE 觸發器中的修改欄位?

DDD
發布: 2024-12-23 12:56:16
原創
890 人瀏覽過

How to Efficiently Capture Modified Fields in SQL Server UPDATE Triggers Using XML or Alternative Methods?

用於捕獲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解決該問題的替代方法。它涉及對插入的表和刪除的表進行反向透視以建立鍵、欄位值和欄位名稱列。透過連接和過濾這兩個未透視表,僅識別修改、刪除和插入的行。

範例:

-- 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🎜>
  • 避免 bigint🎜>
避免 bigint問題位元域和算術溢位可自訂的輸出格式和資料類型轉換透過使用 GUID 填滿列作為唯一識別碼來處理變更自然主鍵的場景

以上是如何使用 XML 或替代方法有效擷取 SQL Server UPDATE 觸發器中的修改欄位?的詳細內容。更多資訊請關注PHP中文網其他相關文章!

來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板