70세 코딩

SQL서버의 트리거에서는 UPDATE() 함수를 이용해서 컬럼이 수정되었는 지를 확인 할 수 있는데,

해당 함수는 실제로 값이 변경되었는지가 아니라 단순히 UPDATE 구문의 SET 에 해당 컬럼이 사용되었는 지를

나타낸다고 보는게 더 맞는것 같습니다.

아래와 같이 테스트를 해 보았습니다.

우선 테스트용 테이블을 만들고 트리거를 작성 합니다.

CREATE TABLE TriggerTest
(id int identity Primary Key,
 prodName varchar(10),
 Spec varchar(10),
 Qty int
)
go

CREATE TRIGGER TR_TriggerTest_AU 
ON TriggerTest
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
   IF UPDATE(Qty) 
   BEGIN
        SELECT 'Qty is updated', *
        FROM inserted
   END

   SELECT
        'Qty is modified', a.*
   FROM 
    inserted a
        LEFT JOIN deleted b
            ON a.id = b.id
   WHERE
    a.Qty <> b.Qty
END
go

 
insert into TriggerTest
 values ('Prod1', '2*3', 100)

 insert into TriggerTest
 values ('Prod2', '10*3', 200)

 insert into TriggerTest
 values ('Prod3', '8*5', 300)
 go

 insert into TriggerTest
 values ('Prod4', '15*10', 400)
 go

이 때 트리거에 의해서 다음과 같이 출력 되는걸 확인 할 수 있는데

이번에는 아래와 같이 트리거에서 체크하지 않는 컬럼에 대하여 UPDATE를 시도 해 보면

UPDATE 구문에 Spec 컬럼만 있기 때문에 트리거에서 아무것도 처리되고 있지 않습니다.

다음으로 트리거에서 체크 하고 있는 Qty 컬럼에 대하여 UPDATE를 해보면 당연히 다음과 같이 조회가 됩니다.

위에서는 Qty 컬럼의 값을 실제로 변경 시키고 있는데, 이번에는 컬럼 값을 동일한 값으로 UPDATE 해 보겠습니다.

실무적으로는 UPDATE용 SP를 작성 해 놓고 해당 SP에 들어오는 인자 값을 이용해서 UPDATE 구문을 수행하는 경우가 많습니다.

이때 해당 테이블의 모든 컬럼을 UPDATE 구문에 포함하게 되어서 실제로 값이 변경된 컬럼이 아닌 컬럼도 (수정 전후가 동일한 값으로) UPDATE 처리가 되기 때문에 이 경우를 주의 깊게 봐야 할 것 입니다.

결과를 보면 Qty 컬럼은 수정 전후 값이 동일하지만 UPDATE() 함수는 true 값을 반환 했다는 것을 알 수 있습니다.

결론은, 트리거 내에서 특정 컬럼의 수정여부(실제로 값의 변화가 있는 지)를 확인 하려면 UPDATE() 함수를 사용해서는 알 수가 없고 inserted 테이블 과 deleted 테이블을 PK로 JOIN 하여 두 테이블간 컬럼의 값을 서로 비교해야 한다는 것 입니다.

이 방법 말고 더 좋은 방법을 알고 계신 분은 댓글 부탁 드립니다.

트리거 내에서 RAISERROR 로 오류를 생성 할 경우, 심각도(severity)가 20~25인 경우에는

자동으로 rollback 처리 된다.

(예)

CREATE TABLE Test1
(number INT IDENTITY,
 bookname nvarchar(100),
 UnitPrice INT,
 Qty INT,
 Price INT
)

CREATE TRIGGER TR_Test1_INSERT
ON Test1
FOR INSERT
AS
    update Test1
    Set Price = (select UnitPrice from inserted) * (select Qty from inserted)
    where number = (select number from inserted)

    RAISERROR ('Error raised!', -- Message text.
               20, -- Severity.
               1 -- State.
               ) with log;

insert into Test1 (bookname, UnitPrice, Qty)
values ('book1', 600, 2)