MS-SQL 트리거 사용하기 (간략 공략!?!?)

트리거!

트리거(Trigger) 는 방아쇠라는 뜻인데, (네이버 사전을 찾아보니, "(반응・사건을 유발한) 계기[도화선]" 라는 뜻도 있음.), 맞는것 같기도하고 아닌것 같기도하게 이름을 지었습니다.
네이버 사전의 2번째 뜻이 좀 더 맞는것 같습니다. (요 위에 빨간색으로 해놓은 뜻)

DBA라면 기본으로 자주 접할 것이고, 소규모 회사의 개발자라면 직접 DB까지 관리해야 하는 경우도 있는데, 어느정도는 알아두면 좋을 것 같네요.

개념이나 자세한 이론은 검색하면 많이 나오니, 간단하게 설명하고, 어떻게 사용하면 되는지 간단한 예제로 설명하겠습니다.

트리거는 Oracle 등 다른 RDMBS도 지원하는 기능인데, MS-SQL에서만 사용해 보았습니다. (그래서 제목을 MS-SQL로 달아놓았고, MS-SQL을 기준으로 설명합니다. Oracle 도 사용법 등은 조금 다르지만, 그 사용 목적은 다르지 않습니다.)

위의 뜻처럼 DB에서 어떤 특정 이벤트가 발생하면 자동으로(방아쇠 당기면 총알이 나가듯이) 무언가 실행이 된다는 것으로 보면 되겠습니다.

DB는 그 특성상 이벤트라 하면 테이블에 발생하는 것이겠죠?
테이블에 데이터가 삽입(INSERT)된다던지, 변경(UPDATE)된다던지, 아니면 삭제(DELETE)된다던지. 즉 이런 이벤트가 발생할 때, 부수적으로 어떠한 처리를 함께 해주기 원할 때, 트리거를 사용합니다.

왜 트리거를 사용할까요?
* 사실 그 사용 용도는 가지각색일 수 있고, 아주 많은 케이스를 다 경험한 것은 아니라 딱 어떤 케이스에 사용한다 말씀드리기는 어렵습니다. 단, DB를 자주접하다보면 이 때 트리거를 쓰면 되겠다 하는 생각이 들때가 있을 겁니다.

* 테이블에 데이터의 입력, 수정, 삭제 되었을 때의 로그를 기록하기 위한 처리
* 테이블에 입력되는 데이터의 무결성을 검사하는 처리
* 테이블에 어떤 데이터에 변화를 줄 때, 동시에 다른 테이블의 데이터에도 영향을 주기 위한 처리

사실 위의 처리는 DB를 사용하는 어플리케이션이나 웹 프로그램에서 처리할 수도 있고, DB의 프로시져를 통해서도 할 수 있는 일들입니다.

예를 들어 DB테이블에 데이터를 INSERT하기 전에 데이터의 무결성을 체크하는 코드를 개발자가 어플리케이션으로 개발하였다고 합시다. 그런데 개발자가 실수로 무결성 체크 코드를 잘못 건드려, 문제가 발생하였다고 하면 문제가 있는 데이터가 그대로 DB에 저장되게 됩니다. (개발자가 많으면 많을수록 코드가 잘못될 가능성도 높아집니다.)

DBA는 개발자가 데이터에 무슨 짓(?)을 하더라도 데이터가 최종 DB테이블에 입력되기 전에 데이터에 문제가 없도록 조치를 취해야 합니다. 그래서 DBA는 데이터 관리에 있어서 만큼은 중요한 위치에 있고, 그 책임 또한 큽니다. 트리거는 DBA가 테이블에 데이터 입력,수정,삭제에 있어서 최후의 방어 수단(?) 최후의 보루(?) 라고 할 수 있는 곳에 위치해 있고, 이를 이용하여 데이터의 무결성 관리, 데이터 변화에 따른 부가적인 처리 등을 할 수 있습니다.

(여기서 말하는 무결성이란 실제 DB에서 도메인 제약 조건, 키 제약조건, 종속성 등에 대한 것이라기 보다는 좀 더 넓은 개념의 포괄적인 무결성으로 보면 되겠습니다. ㅠ.ㅠ 뭔 소리야.;;)

---------------------------------------------------------------------------------------------

그럼 Trigger를 사용하는 간단한 코드를 작성해보겠습니다.

* 게시판 테이블에 데이터 삽입(게시물 작성)이 요청되었을 때, 게시물 제목에 '도박', '광고' 라는 텍스트가 포함되어 있으면 별도의 로그 테이블에 로그를 남기고, 글을 등록하지 않는 트리거를 만들어 보겠습니다.

1) 예제에 사용할 테이블을 생성합니다.
 - 아주 간단하게 게시판 내용을 저장할 테이블과 로그를 기록할 테이블을 만듭니다.
 - 아래와 같이 게시물을 저장할 테이블1(TBL_BOARD), 로그를 남길 테이블2(TBL_LOG) 을 만듭니다.

 

 

[TBL_BOARD]

 * idno : 게시물 고유 번호 (PK + 자동 증가)
* user_id : 작성자 ID
* subject : 게시물 제목
* contents : 게시물 내용
* date_write : 작성 시간
* db_sts : 데이터 상태 (1 : 정상, 0 : 삭제된 데이터)

 

[TBL_LOG]

* user_id : 게시물 등록에 실패한 작성자ID
* event_date : 게시물 등록 시도 시간
* reason : 게시물 등록이 차단된 이유

2) 트리거를 생성합니다.
 - 트리거는 테이블 단위로 생성할 수 있습니다.
 - 새 쿼리 만들기에서 트리거 쿼리를 직접 작성하여 생성해도 되고, 아래 그림처럼 트리거 메뉴를 통해서 트리거를 생성해도 됩니다. (아래 처럼 새 트리거 메뉴를 통해서 생성하면 트리거 기본 코드를 생성하여 줍니다.)

 

3) 트리거 쿼리 작성

USE [EXAMPLE]
GO
/****** Object:  Trigger [dbo].[TRG_EXAMPLE_LOG_BOARD_INSERT]    Script Date: 08/15/2014 00:32:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TRG_EXAMPLE_LOG_BOARD_INSERT] on [dbo].[TBL_BOARD] -- 1. TRG_EXAMPLE_LOG_BOARD_INSERT 란 트리거를 만듭니다. (이름은 마음대로 정할 수 있습니다.)

INSTEAD OF INSERT -- 2. INSERT에 대한 Trigger를 만듭니다. 즉 해당 쿼리는 TBL_BOARD 테이블에 INSERT요청이 온 경우 에 발동합니다. (수정은 INSTEAD OF UPDATE, 삭제는 INSTEAD OF DELETE 로 만들면 됩니다.)

AS
BEGIN
 SELECT * FROM inserted WHERE subject LIKE '%도박%' OR subject LIKE '%광고%' -- 3. '도박' 또는 '광고' 라는 텍스트가 subject에 포함된 데이터가 INSERT요청이 들어왔다면 (여기서 inserted 는 실제 INSERT 요청이 들어온 레코드를 가리키는 keyword입니다.)
 IF @@RowCount > 0 -- 4. '도박'이나 '광고'라는 텍스트가 포함되어 있다면
  BEGIN
  -- TBL_LOG 테이블에 로그를 남깁니다. 로그만 남기고, Trigger가 종료되므로 실제 TBL_BOARD에 데이터가 삽입되지 않습니다.
   INSERT INTO TBL_LOG (user_id, event_date, reason)
   SELECT user_id, GETDATE(), '금지어포함' FROM inserted
  END
 ELSE -- 도박이나 광고라는 단어가 제목에 포함되어 있지 않다면 
  BEGIN
   -- 삽입 요청 들어온 레코드 데이터를 그대로 TBL_BOARD 에 INSERT합니다.
   INSERT INTO TBL_BOARD
   SELECT user_id, subject, contents, date_write, db_sts FROM inserted
  END
END

 위의 쿼리를 모두 작성하였으면 F5를 눌러 실행시킵니다. (CREATE TRIGGER명령어로 인해 새로운 트리거가 생성됩니다. 이미 생성된 TRIGGER를 수정하려면 코드의 CREATE을 ALTER로 변경한 후, F5를 눌러 실행시킵니다.)
 트리거에 문법 오류가 없다면 '명령이 완료되었습니다.'란 메시지가 표시되고, 트리거가 생성된 것을 확인하실 수 있습니다. (새로 생성된 트리거가 보이지 않는다면 새로고침합니다.)

 

4) 참 쉽죠~ 트리거는 완성이 되었고, 트리거를 발동시키는 쿼리를 실행시켜 봅시다.
 4-1) '도박' 또는 '광고' 텍스트가 제목에 없는 게시물을 등록 시도합니다.

[쿼리]

INSERT INTO TBL_BOARD(user_id, subject, contents, date_write, db_sts)
VALUES ('sarang', '트리거테스트', '내용 없음', GETDATE(), '1')

[결과] 

레코드가 TBL_BOARD 테이블에 정상적으로 삽입(INSERT)되었습니다.

 4-2) '도박'이라는 텍스트를 포함하고 있는 게시물을 등록 시도합니다.

[쿼리]

INSERT INTO TBL_BOARD(user_id, subject, contents, date_write, db_sts)
VALUES ('sarang', '트리거테스트(광고)', '내용 없음', GETDATE(), '1')

[결과]  

TBL_BOARD 테이블을 열어보면 새로 등록된 데이터가 없습니다. 그러나 TBL_LOG 테이블을 열어보면 위와 같은 레코드가 등록된 것을 확인할 수 있습니다. Trigger에 의해서 데이터 삽입이 차단된 것을 알 수 있습니다.

 

이제 트리거를 어떻게 사용하시는지 조금은 감이 잡히셨을 것 같습니다.
전문가적인 지식은 없어서 자세하고, 깊게 설명은 못했지만, 트리거를 이해하는데에 있어서 조금이나마 도움이 되셨다면 정말 기쁘겠습니다!