SQLite User Guide for Android, iPhone - Trigger

SQLite Databae에 대한 일곱번째 포스트입니다. 이번 포스트에서는 SQLite의 Trigger에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

CREATE TRIGGER

CREATE TRIGGER 문으로 트리거를 생성할 수 있습니다. 다들 아시겠지만, 트리거는 INSERT, UPDATE, DELETE 문장이 실행될 때 자동으로 실행되는 코드입니다. 현재 SQLite는 FOR EACH ROW (각 레코드 마다 트리거 코드가 실행됨) 만 지원합니다. 즉, SQL 문장 단위의 트리거는 지원하지 않습니다.

트리거 코드에서는 각 레코드가 INSERT/UPDATE/DELETE 될때 OLD 및 NEW 키워드를 사용하여 변경전 데이터 혹은 변경되는 데이터를 참조할 수 있습니다. 물론 INSERT에서는 NEW 키워드만, DELETE에서는 OLD 키워드만 유효합니다.

특이하게도 SQLite에서는 View에도 트리거를 설정할 수 있는데, INSTEAD OF 트리거로 만들 수 있습니다. SQLite에서는 View가 Read Only이므로 뷰에 대한 I/U/D 작업이 불가능 하지만,  View에 INSTEAD OF 트리거를 만들어서 트리거에서 I/U/D 작업을 수행하는 것은 가능합니다.

SQLite에서는 트리거를 I/U/D에 대해 각각 만들어야 합니다. 오라클 같은 경우는 하나의 트리거에서 모든 것을 처리할 수 있지만 SQLite에서는 그렇지 않아서 조금 귀챦기는 할 것 같습니다.

샘플 코드를 보면서 트리거에 대해서 확인하도록 하겠습니다.

/* SQLite Trigger Test Code */

-- Trigger test를 위해서 user table과 user_log  table을 생성합니다.
create table user (
  id integer primary key autoincrement,
  name varchar(10),
  password varchar(10)
);

create table user_log (
  log_no integer primary key autoincrement,
  id integer,
  name_before varchar(10),
  name_after varchar(10),
  password_before varchar(10),
  password_after varchar(10),
  change_date text, 
  flag varchar(1)
); 

-- update에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_update BEFORE UPDATE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, new.name, old.password, new.password, datetime('now','localtime'),'U');
END;

-- insert에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_insert BEFORE INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- delete에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_delete BEFORE DELETE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, null, old.password, null, datetime('now','localtime'),'D');
END;

-- test를 위한 데이터 insert
INSERT INTO USER(name, password) VALUES('overoid','1234');
INSERT INTO USER(name, password) VALUES('test','12345');
UPDATE USER SET NAME = '보고픈'  WHERE ID = 2;
UPDATE USER SET password = 'abcd' WHERE ID =1;
DELETE FROM USER WHERE id = 2;

-- 데이터 확인 (null 데이터를 명확하게 표기하기 위해서..ifnull 함수를 사용함)
SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D

-- 데이터는 정상적으로 나옵니다.
-- 다만, autoincrement로 생성한 컬럼에 대해서 insert 작업시 실행되는 before trigger에서 NEW.ID 값을
-- 제대로 가져오지 못하고 -1로 입력되는 것을 볼 수 있습니다.

-- 위 문제를 해결하기 위해서 INSERT에 대해서 AFTER 트리거를 생성합니다.
CREATE TRIGGER user_trigger_before_after AFTER INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- 데이터를 삽입합니다.
INSERT INTO USER(name, password) VALUES('after','1234');

SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D
6 -1 <null> after <null> 1234 2010-08-25 17:03:26 I
7 3 <null> after <null> 1234 2010-08-25 17:03:26 I

-- after trigger로 변경했더니, autoincrement 컬럼에 대해서 값을 제대로 가져오는 군요.

이것으로 SQLite Trigger에 대한 일곱번째 포스트를 마칩니다. 다음번 포스트에서는 SQLite의 Tranaction 및 Lock에 기능에 대해서 살펴보도록 하겠습니다.