[MS SQL] 트리거 특정 컬럼 변경 시(업데이트) 작동

SQL SERVER AFTER 트리거 사용 시

특정 컬럼 업데이트를 감지하여 작동하는 쿼리 예제 입니다.

 

흔히 이력 정보를 저장할 때, 특정 컬럼의 값이 변하는지 감지하고

감지하는 컬럼의 값이 변할때만 작동하도록 해야하는 상황이 많습니다.

 

예제.

사용자 테이블과 사용자 이력 테이블 트리거 작성

 

트리거를 가지고 있는 USERS 테이블 

*[USER_ID], [USER_NM], [USER_PWD], [USER_ST], [EDT_DT]

 

USERS 테이블의 이력을 저장할 USERS_HIS 테이블

*[USER_ID], *[HIS_IDX], [USER_NM], [USER_ST], [EDT_DT]

 

목표는 USERS 테이블의 [USER_ST] (유저 상태)컬럼 값이 변할 때 이력을 저장하는 것 입니다.

[USER_ST]가 변할 때 USERS_HIS 테이블에 데이터를 삽입하도록 트리거를 작성하면 됩니다.

 

예제는 다음과 같습니다.

 

 

 

 

트리거 INSERTED 와 DELTED 시에는 그냥 이력을 삽입하면되니 UPDATED만 예제로 작성하겠습니다.

 

1. 트리거 쿼리문

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		davi
-- Create date: 210323
-- Description:	Insert history when [USERS]'s USER_ST updated
-- =============================================
CREATE TRIGGER [dbo].[TRG_USERS_UPDATED]
   ON  [dbo].[USERS]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SET NOCOUNT ON;
    --USER 테이블에서 이력을 저장할 컬럼 변수 생성
	DECLARE
	@USER_ID varchar(20),
	@HIS_IDX int,
	@USER_NM varchar(10),
	@USER_ST	char(1),
	@EDT_DT datetime

		--I : INSERTED
		--U : UPDATED
		--D : DELETED
		DECLARE @TRG_ACTION char(1)

		--INSERTED
		SET @TRG_ACTION = 'I'

		--U : UPDATED
		--D : DELETED
		IF EXISTS(SELECT * FROM deleted)
		  BEGIN
		    SET @TRG_ACTION = (CASE WHEN EXISTS (SELECT * FROM inserted) THEN 'U' ELSE 'D' END)
		  END


		  --UPDATED 만 작성
		  IF @TRG_ACTION = 'U'
		   BEGIN
			SELECT 
			  @USER_ID = INS.USER_ID, @USER_NM = INS.USER_NM, @USER_ST = INS.USER_ST, @EDT_DT = INS.EDT_DT
			FROM DELETED DEL
			JOIN INSERTED INS ON DEL.USER_ID = INS.USER_ID --PK로 JOIN
			WHERE 
			DEL.USER_ID = INS.USER_ID AND
			DEL.USER_ST != INS.USER_ST --컬럼 값이 다르면 USER_ST가 변경되었을때이다.  
		   END

		   IF(@USER_ID IS NOT NULL)
		     BEGIN
			   --USERS_HIS 테이블에서 해당 USER_ID의 변경 순서 +1
			   SELECT @HIS_IDX = ISNULL(MAX(HIS_IDX),0)+1 FROM USERS_HIS WITH(NOLOCK) WHERE USER_ID = @USER_ID

			   --USERS_HIS 삽입
			   INSERT INTO USERS_HIS 
			   (USER_ID, HIS_IDX, USER_NM, USER_ST, EDT_DT) VALUES
			   (@USER_ID, @HIS_IDX, @USER_NM, @USER_ST, @EDT_DT)
			 END

END
GO

 

해당 쿼리는 업데이트 후 정보를 이력으로 저장 했습니다.

 

 

 

2. 트리거 검증 

 

 1) 검증 쿼리

--변경전
SELECT * FROM USERS

SELECT * FROM USERS_HIS

--USER_ST 및 다른 값 업데이트
UPDATE USERS
  SET USER_ST = 2, USER_NM = '이지은', EDT_DT = GETDATE()
WHERE USER_ID = 'davi'

-- USER_ST 업데이트 후
SELECT * FROM USERS

SELECT * FROM USERS_HIS

 

 2) 결과

 

 

간단하게 작성하려고 했는데 은근히 시간이 오래 걸리네요.

 

참고 하시고 오류 있으면 댓글 부탁드립니다.