반응형
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) 결과
간단하게 작성하려고 했는데 은근히 시간이 오래 걸리네요.
참고 하시고 오류 있으면 댓글 부탁드립니다.
반응형
'MSSQL' 카테고리의 다른 글
| [MS SQL] OPENQUERY 8000자 제한 (0) | 2022.12.17 |
|---|---|
| [MS SQL] ORDER BY CASE 정렬 조건 (0) | 2021.04.13 |
| [MS SQL] IF EXISTS 테이블 존재 유무 확인 조건문 (0) | 2020.06.11 |
| MS SQL Express 자동 백업 스케줄러 설정 (2) | 2020.05.05 |
| MS SQL TCP/IP 접속 에러 해결 (0) | 2020.05.05 |
