SQLite3에서 트리거 설치하기

sooopd

6년 ago

트리거는 데이터 베이스 내에 특정한 동작이 수행될 때, 연관된 다른 쿼리를 자동으로 실행하는 기능이다. 예를 들어 어떤 레코드를 변경하거나 삭제할 때, 그와 관련된 다른 정보를 조작해야 한다거나 할 때 사용할 수 있다. 두 정보 사이의 관련성이 매우 밀접하다면, 클라이언트 코드에서 매번 쿼리를 두 번 실행하는 것보다 트리거를 통해서 관리하는 편이 더 나을 수 있는 것이다.

트리거를 사용하는 가장 일반적인 패턴은 카운트를 대체하기 위한 것이다. BOARD라는 테이블에 레코드가 몇 개인지 알기 위해서는 다음과 같은 쿼리를 실행할 수 있다.

SELECT count(*) FROM BOARD;

문제는 count(*) 함수는 값을 계산하기 위해서 해당 쿼리의 모든 레코드 개수를 세어야 한다는 것이다. 테이블 크기가 작다면 별 문제가 되지 않겠지만, 수백만개의 레코드를 가지고 있는 테이블이라면 문제가 달라진다.

이 문제를 회피하는 방법으로는 전체 글의 수를 따로 세어 두는 테이블을 만드는 것이다. 별도의 카운터 테이블을 만들고 글이 등록될 때마다 카운터 값을 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 테이블의 값이 자동으로 갱신되어, 전체 글 수를 추적하게 된다. 따라서 단순히 글의 개수를 파악하기 위해서 테이블 전체 스캔하는 일을 피할 수 있다.

문법

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

CREATE TRIGGER 이름 INSERT ON 테이블
BEGIN
  쿼리
END;
CREATE TRIGGER 이름 [BEFORE] DELETE ON 테이블
FOR EACH ROW -- 생략가능
BEGIN
  쿼리
END;
CREATE TRIGGER 이름 UPDATE OF 칼럼 ON 테이블
FOR EACH ROW  -- 생략 가능
BEGIN
  쿼리
END;

CREATE TRIGGER 이름 : 트리거 설치, CREATE TEMP TRIGGER는 임시 트리거를 생성한다.
이름 [IF NOT EXISTS] : 이름 뒤에 IF NOT EXISTS를 쓰면 해당 트리거가 없을 때에만 만든다.
[BEFORE|AFTER|INSTEAD OF] : 트리거가 실행될 시점이다. 기본적으로 AFTER이며, BEFORE는 트리거를 먼저 실행한다. INSTEAD OF는 트리거만 실행하고 실제 동작을 하지 않는다. 예를 들어 뷰에 대한 삽입이나 업데이트가 허용되지 않으므로, 뷰에 대해서 INSTEAD OF 트리거를 걸고 실제 테이블을 조작하도록 할 수 있다.
DELETE | INSERT | UPDATE [OF {cols...}] : 트리거를 촉발시킬 동작을 정의한다. 업데이트의 경우에 특정 칼럼 조건을 OF로 추가할 수 있다.
ON {테이블} : 트리거가 촉발될 대상 테이블이다. 즉 이 테이블에 앞에서 명시한 동작이 이루어질 때 트리거가 시작된다.
[FOR EACH ROW] : 만약 여러 행이 업데이트/삭제되는 경우에 각 행의 변경에 한 번씩 호출되도록 한다. INSERT의 경우에는 한 쿼리에 여러 데이터를 넣어 실행하더라도 삽입은 한 번에 한 개씩 일어난다.
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;

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

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

문법

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

이 글 공유하기: