MSSQL 트리거(TRIGGER) 사용법(생성) 및 예제

MS SQL에서 트리거(TRIGGER)는 어떤 테이블 또는 뷰 에 저장, 수정, 삭제 이벤트가 발생하면 자동으로 실행되는 특수한 종류의 저장 프로시저입니다. 저장 프로시저는 직접 호출해야 되지만 트리거는 이벤트가 발생 시 자동으로 작동합니다. 그리고 저는 운영상 반드시 필요하다고 생각되는 테이블에서만 트리거를 생성하는 편입니다.

그럼 간단한 예제를 통해서 트리거(TRIGGER) 사용법(생성)에 대해서 알아보죠.

포스트 기준

– Microsoft SQL Server 2019

– SSMS 18.11

1. SSMS 툴로 트리거(TRIGGER) 생성

1. 트리거(TRIGGER) 사용 예시

사용자 계정 정보 신설, 변경, 삭제인 경우 로그 데이터 생성
임시/회계전표 신규, 변경, 삭제인 경우 로그 데이터 생성
Master(주)-Detail(종) 관계에서 Master(주)삭제 시 Detail(종) 데이터 삭제

2. 사용법 (구문)

트리거(TRIGGER)를 생성하는 방법은 SSMS 툴로 생성하는 방법과 쿼리문으로 생성하는 방법이 있는데 쿼리문으로 생성하는 방법에 대해서만 알아보죠.

3. 데이터베이스 → 테이블 → 새 트리거(N)…

2. 트리거(TRIGGER) 사용 예제

▶ 애플리케이션 사용자 정보 테이블 로그 데이터 생성

1. INSERT 트리거

/* 사용자 테이블에 사용자 추가 시 로그 생성 */
CREATE TRIGGER BS_USR_MAST_I   -- 트리거명
ON BS_USR_MAST   -- 적용 테이블
FOR INSERT   -- 삽입
AS
     
BEGIN
     
   -- 변수 선언
   DECLARE @USR_ID	        NVARCHAR(30),   -- 사용자ID
           @USR_NM	        NVARCHAR(50),   -- 사용자명
           @INSRT_DT		DATETIME        -- 생성일
     
   -- 생성된 데이터 변수에 저장
   SELECT @USR_ID = USR_ID,
          @USR_NM = USR_NM,
          @INSRT_DT = INSRT_DT
     FROM INSERTED
     
   -- 로그 테이블에 저장
   INSERT INTO BK_BS_USR_MAST (INSRT_DT, FLAG, USR_ID, USR_NM)
   VALUES (@INSRT_DT, 'I', @USR_ID, @USR_NM)
     
END

▶ 실행 결과

2. UPDATE 트리거

/* 사용자 테이블에 데이터 변경 시 로그 생성 */
CREATE TRIGGER BS_USR_MAST_U   -- 트리거명
ON BS_USR_MAST   -- 적용 테이블
FOR UPDATE   -- 변경
AS
     
BEGIN
     
   -- 변수 선언
   DECLARE @USR_ID			NVARCHAR(30),
           @USR_NM			NVARCHAR(50),
           @UPDT_DT			DATETIME
     
   -- 사용자명 또는 비밀번호가 변경되었을때 로그 생성
   IF UPDATE(USR_NM) OR UPDATE(USR_PWD)
   BEGIN
        
      -- 변경된 데이터 변수에 저장
      -- 참고) UPDATED 테이블은 없고 INSERTED 테이블로 사용해야 됨.
      SELECT @USR_ID = USR_ID,
             @USR_NM = USR_NM,
             @UPDT_DT = UPDT_DT
        FROM INSERTED
        
      -- 로그 테이블에 저장
      INSERT INTO BK_BS_USR_MAST (INSRT_DT, FLAG, USR_ID, USR_NM)
      VALUES (@UPDT_DT, 'U', @USR_ID, @USR_NM)
        
   END
     
END

▶ 실행 결과

3. DELETE 트리거

/* 사용자 테이블에 사용자 삭제 시 로그 생성 */
CREATE TRIGGER BS_USR_MAST_D
ON BS_USR_MAST
FOR DELETE AS
     
BEGIN
     
   -- 변수 선언
   DECLARE @USR_ID			NVARCHAR(30),
           @USR_NM			NVARCHAR(50)
     
   -- 삭제된 데이터 변수에 저장
   SELECT @USR_ID = USR_ID,
          @USR_NM = USR_NM
     FROM DELETED
     
   -- 로그 테이블에 저장
   INSERT INTO BK_BS_USR_MAST (INSRT_DT, FLAG, USR_ID, USR_NM)
   VALUES (GETDATE(), 'D', @USR_ID, @USR_NM)
   
END