[트리거] 한트리거안에서 Insert,Update,Delete Trigger 구별하는 방법

[트리거] 한트리거안에서 Insert,Update,Delete 트리거 구별하는 방법

 

- 한 트리거 안에 Insert,Update,delete 트리거를 구현한 경우 각 Action에 대한 구별법

 

테스트 환경

CREATE TABLE Z_TestTable ( Seq INT IDENTITY(1,1), Name VARCHAR(50), CONSTRAINT [PK_Z_TestTable] PRIMARY KEY CLUSTERED ( Seq ASC ) ) DECLARE @wIndex INT SELECT @wIndex = 1 WHILE ( @wIndex < 1000 ) BEGIN INSERT INTO Z_TestTable (Name) VALUES ( '테스트' + CAST(@wIndex AS VARCHAR) ) SELECT @wIndex = @wIndex + 1 END GO;

 

첫번째방법: COLUMNS_UPDATED() 사용

CREATE TRIGGER IUD_ChangeState_Z_TestTable ON Z_TestTable AFTER INSERT,UPDATE,DELETE AS BEGIN DECLARE @action char(1) IF COLUMNS_UPDATED() > 0 -- insert or update BEGIN IF EXISTS (SELECT * FROM DELETED) -- update SET @action = 'U' ELSE SET @action = 'I' END ELSE -- delete BEGIN SET @action = 'D' END SELECT Name,@action FROM DELETED; END

 

두번째방법: INSERTED, DELETED 테이블 사용 

CREATE TRIGGER IUD_ChangeState_Z_TestTable ON Z_TestTable AFTER INSERT,UPDATE,DELETE AS BEGIN DECLARE @action char(1) SET @action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 'U' -- Set Action to Updated. WHEN EXISTS(SELECT * FROM INSERTED) THEN 'I' -- Set Action to Insert. WHEN EXISTS(SELECT * FROM DELETED) THEN 'D' -- Set Action to Deleted. ELSE NULL -- Skip. It may have been a "failed delete". END) SELECT Name,@action FROM DELETED; END