XML 또는 대체 방법을 사용하여 SQL Server 업데이트 트리거에서 수정된 필드를 효율적으로 캡처하는 방법은 무엇입니까?

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 비트 필드 및 산술 오버플로
• 사용자 정의 가능한 출력 형식 및 데이터 유형 캐스팅
• GUID로 채워진 열을 고유 식별자로 사용하여 자연 기본 키를 변경하는 시나리오를 처리합니다

위 내용은 XML 또는 대체 방법을 사용하여 SQL Server 업데이트 트리거에서 수정된 필드를 효율적으로 캡처하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!