t-sql Archives

42 개의 열이있는 테이블 과이 열 중 38 개가 업데이트 될 때 수행해야 할 트리거가 있습니다. 나머지 4 열이 변경되면 논리를 건너 뛸 필요가 있습니다.

내가 사용할 수있는 UPDATE () 기능을 하나 개의 큰 생성 IF조건을,, 짧은 뭔가를 선호합니다. COLUMNS_UPDATED를 사용하면 특정 열이 모두 업데이트되었는지 확인할 수 있습니까?

예를 들어, 열 3, 5 및 9가 업데이트되는지 확인하십시오.

  IF
  (
    (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
     AND
    (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1)
  )
    PRINT 'Columns 3, 5 and 9 updated';

따라서 20column 3및의 5값 1과 column의 값 9은 두 번째 바이트의 첫 번째 비트에 설정되므로 문을 변경 OR하면 열 3및 / 5또는 열 9이 업데이트 되었는지 확인 합니까?

OR1 바이트 컨텍스트에서 로직을 어떻게 적용 할 수 있습니까?

답변

CHECKSUM()실제 값을 비교하여 값이 변경되었는지 확인하는 매우 간단한 방법으로 사용할 수 있습니다 . CHECKSUM()전달 된 값 목록에 걸쳐 체크섬을 생성하며 그 중 숫자와 유형이 결정되지 않습니다. 이와 같은 체크섬을 비교할 가능성이 적다는 점에 유의하십시오. 이를 처리 할 수 없으면 ^1을HASHBYTES 대신 사용할 수 있습니다 .

아래 예는 AFTER UPDATE트리거를 사용 TriggerTest하여 Data1 또는 Data2 열의 값 중 하나가 변경 되는 경우에만 테이블 에 대한 수정 기록을 유지합니다 . 경우 Data3변경, 어떤 작업도 수행하지 않습니다.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    INSERT INTO TriggerResult
    (
        TriggerTestID
        , Data1OldVal
        , Data1NewVal
        , Data2OldVal
        , Data2NewVal
    )
    SELECT d.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i
        LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
    WHERE CHECKSUM(i.Data1, i.Data2) <> CHECKSUM(d.Data1, d.Data2);
END
GO

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

COLUMNS_UPDATED () 함수 를 사용 하지 않는 경우 테이블 정의가 변경되어 하드 코딩 된 값이 무효화 될 수 있으므로 해당 열의 서수 값을 하드 코딩해서는 안됩니다. 시스템 테이블을 사용하여 런타임시 값을 계산할 수 있습니다. 주의하십시오 COLUMNS_UPDATED()열이 수정되는 경우 함수가 주어진 열 비트에 대한 true를 돌려 ANY 에 의해 영향을받는 행 UPDATE TABLE문.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    DECLARE @ColumnOrdinalTotal INT = 0;

    SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal
        + POWER (
                2
                , COLUMNPROPERTY(t.object_id,c.name,'ColumnID') - 1
            )
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
    WHERE s.name = 'dbo'
        AND t.name = 'TriggerTest'
        AND c.name IN (
            'Data1'
            , 'Data2'
        );

    IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
    BEGIN
        INSERT INTO TriggerResult
        (
            TriggerTestID
            , Data1OldVal
            , Data1NewVal
            , Data2OldVal
            , Data2NewVal
        )
        SELECT d.TriggerTestID
            , d.Data1
            , i.Data1
            , d.Data2
            , i.Data2
        FROM inserted i
            LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID;
    END
END
GO

--this won't result in rows being inserted into the history table
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

SELECT *
FROM dbo.TriggerResult;

--this will insert rows into the history table
UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

--this WON'T insert rows into the history table
UPDATE dbo.TriggerTest
SET Data3 = GETDATE()
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

--this will insert rows into the history table, even though only
--one of the columns was updated
UPDATE dbo.TriggerTest
SET Data1 = 'blum'
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

이 데모는 삽입해서는 안되는 행을 기록 테이블에 삽입합니다. 행 Data1에 일부 행에 대한 Data3열이 업데이트되고 일부 행에 대해 열이 업데이트되었습니다. 이것은 단일 명령문이므로 모든 행은 트리거를 통한 단일 패스로 처리됩니다. 비교의 Data1일부인 일부 행이 업데이트 COLUMNS_UPDATED()되었으므로 트리거에 표시된 모든 행이 TriggerHistory테이블에 삽입됩니다 . 시나리오에 “잘못된”경우 커서를 사용하여 각 행을 개별적으로 처리해야합니다.

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
SELECT TOP(10) LEFT(o.name, 10)
    , LEFT(o1.name, 10)
    , GETDATE()
FROM sys.objects o
    , sys.objects o1;

UPDATE dbo.TriggerTest
SET Data1 = CASE WHEN TriggerTestID % 6 = 1 THEN Data2 ELSE Data1 END
    , Data3 = CASE WHEN TriggerTestID % 6 = 2 THEN GETDATE() ELSE Data3 END;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

TriggerResult표는 지금은 (해당 테이블의 두 열을) 전혀 변화를 보여주지 이후 속해 그렇지 않은과 같이 몇 가지 잠재적으로 오해의 소지가 행이 있습니다. 아래 이미지의 두 번째 행 집합에서 TriggerTestID 7은 수정 된 것처럼 보이는 유일한 행입니다. 다른 행에는 Data3열만 업데이트되었습니다. 그러나 배치 의 한 행이 Data1업데이트되었으므로 모든 행이 TriggerResult테이블에 삽입됩니다 .

또는 @AaronBertrand와 @srutzky가 지적했듯이 가상 테이블 inserted과 deleted실제 테이블 의 실제 데이터를 비교할 수 있습니다. 두 테이블의 구조가 동일하므로 EXCEPT트리거 의 절을 사용하여 원하는 정확한 열이 변경된 행을 캡처 할 수 있습니다 .

IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
    ;WITH src AS
    (
        SELECT d.TriggerTestID
            , d.Data1
            , d.Data2
        FROM deleted d
        EXCEPT
        SELECT i.TriggerTestID
            , i.Data1
            , i.Data2
        FROM inserted i
    )
    INSERT INTO dbo.TriggerResult
    (
        TriggerTestID,
        Data1OldVal,
        Data1NewVal,
        Data2OldVal,
        Data2NewVal
    )
    SELECT i.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i
        INNER JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
END
GO

^{1- HASHBYTES 계산으로 인해 충돌이 발생할 수있는 작은 기회에 대한 설명 은 /programming/297960/hash-collision-what-are-the-chances 를 참조 하십시오 . Preshing 은이 문제에 대한 적절한 분석을 가지고 있습니다.}

How IT

언제든지 물어보세요.

태그 보관물: t-sql

COLUMNS_UPDATED를 사용하여 특정 열이 업데이트되었는지 확인하는 방법은 무엇입니까? () 기능을 하나

답변

답변