SQLite3에서 트리거 설치하기

트리거는 데이터 베이스 내에 특정한 동작이 수행될 때, 연관된 다른 쿼리를 자동으로 실행하는 기능이다. SQLite3에서도 트리거를 지원한다. 이러한 트리거는 언제 활용될 수 있을끼? 예를 들어 게시판을 관리하는 테이블이 하나 있다고 가정하자. 이 테이블에서 전체 글 수를 조회하려면 흔히 count() 함수를 써서 다음과 같이 조회할 수 있다.

SELECT count(*) FROM BOARD;

이런 쿼리를 작성할 때 흔히 간과하는 문제는 이 쿼리는 레코드의 수를 세기위해서 전체 테이블을 스캔해야 한다는 것이다. 글의 수가 수백단위라면 큰 문제는 아닐 수 있지만, 글의 수가 매우 많아서 테이블의 크기가 크다면, 그리고 카운터의 특성상 이 쿼리가 빈번하게 요청된다면 I/O에 걸리는 부하가 커져서 전체 성능을 떨어뜨리는 문제가 생길 수 있다.

이 문제를 회피하는 방법으로는 전체 글의 수를 따로 세어 두는 테이블을 만드는 것이다. 별도의 카운터 테이블을 만들고 글이 등록될 때마다 카운터 값을 1 증가시키고 삭제되면 1만큼 감소시키는 것이다. 이를 위해서 글을 등록하는 프로그램에서 글 등록 후 카운터 테이블을 업데이트 하는 쿼리를 추가로 요청하고, 반대로 글 삭제 처리 후에도 카운터 테이블의 값을 감소시키는 쿼리를 추가로 호출해야 한다. 하지만 작업을 하나 하는데 필요한 쿼리가 두 개가 된다는 것은 제법 귀찮은 일이 된다. 이럴 때 트리거를 사용하면 이러한 작업을 단순화할 수 있다. 즉 게시물을 삽입하는 쿼리에 대해 카운터 값을 1 올리는 트리거를 설치하고, 반대로 게시물을 삭제하는 쿼리에 대해서 카운터 값을 1 내리는 트리거를 설치하면 카운터 값은 항상 올바르게 게시물 수와 일치하게 될 것이다.

먼저 글의 수를 카운트할 테이블을 하나 만들고, num 값을 초기화한다.

CREATE TABLE counter (num INTEGER);
INSERT INTO counter values (0);

그런 후에 다음과 같이 트리거를 설치할 수 있다.

CREATE TRIGGER increase_counter INSERT ON board -- board 테이블에 INSERT 가 일어나면
BEGIN
  UPDATE counter SET num = (num + 1);  -- counter 테이블의 num 칼럼값을 +1 한다.
END;

마찬가지로 글 삭제에 대해서도 트리거를 설치할 수 있다. 특히 글 삭제는 하나의 쿼리에서 여러 개의 게시물이 삭제될 수 있기 때문에 FOR EACH ROW 라는 절을 추가해서 삭제되는 행의 개수 만큼 트리거가 일어나도록 한다.

CREATE TRIGGER decrease_counter DELETE ON board
FOR EACH ROW  -- 여러 글을 한 번에 삭제하는 경우에는 삭제되는 레코드 개수만큼
BEGIN
  UPDATE counter SET num = (num - 1);
END;

이렇게하면 글이 추가/삭제될 때마다 트리거에 의해서 counter 테이블의 값이 자동으로 갱신되어, 전체 글 수를 추적하게 된다. 따라서 단순히 글의 개수를 파악하기 위해서 테이블 전체 스캔하는 일을 피할 수 있다.

트리거 SQL 문법

트리거 설치 문법은 다음과 같다.

  1.  CREATE [TEMP|TEMPORARY] TRIGGER : 트리거 설치
  2. [IF NOT EXISTS] : 해당 이름의 트리거가 설치되지 않았다면 새로 설치
  3. {트리거이름}
  4. [BEFORE | AFTER | INSTEAD OF] : 트리거가 수행될 순서로 기본값은 AFTER 이다.
  5. DELETE | INSERT | UPDATE [OF {cols...}] : 트리거를 촉발시킬 동작을 정의한다. 업데이트의 경우에 특정 칼럼에 대한 업데이트만 선택적으로 취할 수 있다.
  6. ON {테이블} : 트리거가 촉발될 대상 테이블이다. 즉 이 테이블에 앞에서 명시한 동작이 이루어질 때 트리거가 시작된다.
  7. [FOR EACH ROW] : 만약 여러 행이 업데이트/삭제되는 경우에 각 행의 변경에 한 번씩 호출되도록 한다.
  8. BEGIN ... END; : 실행될 트리거의 내용

참고로 변경된 행은 BEGIN ~ END 블럭 내에서 OLD, NEW 로 참조된다. INSERT의 경우 NEW만 있고, DELETE의 경우에는 OLD만 있다. UPDATE의 경우에는 변경전 행은 OLD, 변경 후 행은 NEW가 된다.

다른 예제 – 글의 변경 시 이전 내용 백업하기

다른 예를 만들어보자. 블로그나 게시판에서 글의 내용을 변경할 때 이전 내용을 백업하는 트리거를 작성해보자. 백업되는 칼럼은 post_id, title, body, modified_date 이다. (최종 변경 시점이 해당 리비전의 작성 시점이 될 것이므로) 따라서 백업을 위한 테이블을 다음과 같이 생성한다고 한다.

CREATE TABLE backup (
  post_id INTEGER, 
  title VARCHAR(1024),
  body TEXT,
  reg_date DATETIME );

그리고 다음과 같이 트리거를 설치하면 된다.

CREATE TRIGGER backup_post 
AFTER UPDATE OF title, body  -- 1
ON Board                     -- 2
FOR EACH ROW                 -- 3
BEGIN
                             -- 4
  INSERT INTO backup VALUES (
    OLD.post_id, 
    OLD.title, 
    OLD.body, 
    OLD.modified_date
  );
END;

위 쿼리는 트리거를 설치하는데, 그 내용은 다음과 같다고 해석된다.

  1. title, body 칼럼 중 하나가 변경되는 UPDATE 동작에 대해 실행되는 트리거이다.
  2. 업데이트되는 테이블이 Board 일 때 실행된다.
  3. 업데이트가 한 쿼리에서 여럿 발생할 때, 매 행에 대해서 트리거가 실행된다.
  4. 트리거링된 쿼리에 OLD를 사용해서 업데이트 되기 전의 행의 값을 백업한다.

부록 – INSTEAD OF 트리거

트리거는 에 대해서도 설치될 수 있다. 기본적으로 뷰에 변경을 가하는 쿼리를 호출하면 에러가 나지만, 트리거의 타입과 같은 변경 쿼리가 호출되면 에러가 나는 대신에 트리거가 실행된다. 이는 명시적으로 뷰나 뷰의 원본 테이블이 변경되는 것이 아니라, 그저 트리거만 실행될 뿐이다. (만약 그 트리거가 원본 테이블을 변경하도록 되어 있다면 그 때는 원본 테이블이 변경될 것이다.)