SQLite 활용 팁 1 - 트리거 활용하기

by digipine posted Nov 01, 2017
?

Shortcut

PrevPrev Article

NextNext Article

ESCClose

Larger Font Smaller Font Up Down Go comment Print

1. 트리거 정의 
 

   일반적으로 트리거(Trigger)라 함은 데이터베이스에 어떤 Event (행위)가 발생하였을 때, 
 
   이어서 실행되어져야 하는 동작을 말합니다. 
 

 

   정의만을 읽어보고 고개를 갸우뚱~~ 하시는 분들이 있을 것 좀 더 구체적인 사례를 언급하여 보겠습니다.
 
   일반적으로 DB를 사용하여, 게시판을 만들 경우 가장 크게 성능상의 문제를 일으키는 부분은, 대개의 경우 다음과 같습니다.
 

 

   - 전체 게시글의 갯수를 조회하는 카운트 쿼리 
 
   - 검색시 오버헤드 
 

 

   이 때 검색시 오버헤드는 인덱스를 사용하면 적절히 해결이 가능합니다.
 
   하지만 전체 게시글의 갯수를 조회하는 카운트 쿼리의 성능을 높이기 위해서는 다음과 같은 몇 가지 절차를 수행해야 합니다.
 
   
 
   먼저, 매번 테이블 전체를 스캔하여 갯수를 구하는 카운트 쿼리의 오버헤드를 줄이기 위해서, 카운트 정보만을 가지고 있는
 
   메타데이터 테이블을 별도로 생성합니다. (분리된 메타데이터 테이블 내에는 카운트 쿼리만 저장이 되어야 하겠죠 ^^)
 

 

   그리고, 게시글이 새로 등록될 때 마다, 메타데이터 테이블을 카운트를 +1 씩 증가시켜주는 트리거와
 
   게시글이 삭제될 때 마다, 메타데이터 테이블의 카운트를 -1 씩 감소시켜주는 트리거를 등록합니다.
 

 

   트리거를 등록하는 방법은 2절에서 다루고 있습니다. ^^
 

 

2. TRIGGER 사용의 이해를 돕기 위한 예제 SQL 문장 
 
/* 테스트 테이블 생성 */
CREATE TABLE address (uid INTEGER PRIMARY KEY, name TEXT, gender INTEGER);
CREATE TABLE metadata (uid INTEGER PRIMARY KEY, man_count INTEGER, woman_count INTEGER);

/* address 테이블에 DELETE 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */ 
CREATE TRIGGER delete_address DELETE ON address
BEGIN
  UPDATE metadata SET man_count = man_count - 1 WHERE old.gender = 1;     /* 남자 카운트 증가 */
  UPDATE metadata SET woman_count = woman_count - 1 WHERE old.gender = 0; /* 여자 카운트 증가 */
END;

/* address 테이블에 INSERT 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */
CREATE TRIGGER insert_address INSERT ON address 
BEGIN 
  UPDATE metadata SET man_count = man_count + 1 WHERE new.gender = 1;     /* 남자 카운트 증가 */
  UPDATE metadata SET woman_count = woman_count + 1 WHERE new.gender = 0; /* 여자 카운트 증가 */
END;

/* metadata 테이블에 기본 행(Row) 추가 */
INSERT INTO metadata VALUES (1, 0, 0);

/* address 테이블에 남자(gender=1) 셋, 여자(gender=0) 셋 추가 */
INSERT INTO address(name, gender) VALUES ('James', 1);   /* insert_address 트리거 (남자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Richard', 1); /* insert_address 트리거 (남자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Daniel', 1);  /* insert_address 트리거 (남자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Jane', 0);    /* insert_address 트리거 (여자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Suzan', 0);   /* insert_address 트리거 (여자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Alice', 0);   /* insert_address 트리거 (여자 카운트 증가) */

위와 같은 쿼리문장을 실행할 경우, insert_address 트리거에 의해서, metadata 테이블의 
 
man_count의 컬럼에는 3이 저장되며, woman_count의 컬럼에는 3이 저장이 됩니다.
 
물론, DELETE FROM address WHERE name = 'James' 와 같은 쿼리 실행시에는 delete_address 트리거에 의해서
 
man_count의 값이 -1 씩 감소하게 됩니다.

TAG •

Articles

1 2