SQLite | 트리거(Trigger) | 트리거 생성

특정 테이블의 데이터가 수정되면 동시에 다른 테이블의 데이터를 수정하고 싶은 경우가 있다. 이러한 경우에 사용되는 것이 트리거이다. 여기에서는 트리거를 만드는 방법에 대해 설명한다.

트리거 생성

트리거를 생성하여 지정된 SQL 문이 실행될 때 다른 SQL 문을 자동으로 수행할 수 있다. 트리거를 생성하는 형식은 다음과 같다.

CREATE TRIGGER 트리거명 [ BEFORE | AFTER | INSTEAD OF]
 { DELETE | UPDATE [OF 컬럼명, ...] | INSERT } ON 테이블명
 [ FOR EACH ROW | FOR EACH STATEMENT ]
 [ WHERE 조건식 ]
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

복잡하게 보이지만, 어떤한 수행이 있어 트리거가 실행 하는지를 지정하는 방법 SQL 문을 실행하는 방법을 작성한다. 수행이 되는 SQL 문은 UPDATE 문, INSERT 문, 그리고 DELETE 문이 있다.

테이블에 대해 UPDATE가 발생했을 때 트리거를 설정하려면 다음과 같이 작성할 수 있다.

CREATE TRIGGER 트리거명 UPDATE ON 테이블명
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

또한 특정 컬럼에 UPDATE가 수행되었을 때 트리거를 설정할 수 있다.

CREATE TRIGGER 트리거명 UPDATE OF 컬럼명 ON 테이블명
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

마찬가지로 테이블에 DELETE 문이 실행되었을 때와 INSERT 문이 실행되었을 때 트리거를 설정하는 경우는 각각 다음과 같다.

CREATE TRIGGER 트리거명 DELETE OF 컬럼명 ON 테이블명
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

CREATE TRIGGER 트리거명 INSERT OF 컬럼명 ON 테이블명
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

예를 들어 데이터가 수정되었을 때 실행하는 트리거를 설정해두고, 수정 이전 혹은 이후의 데이터를 받아와서 트리거에서 실행하는 SQL문에서 사용할 수 있다. 이에 대해서는 다음 페이지에서 설명한다.

그러면 실제로 트리거를 만들어 보자. 다음과 같이 두 테이블을 만든다.

create table product(id integer, name text, price integer);
create table log(id integer primary key, act text);

sqlite> create table product(id integer, name text, price integer);
sqlite> create table log(id integer primary key, act text);
sqlite> 

다음은 product 테이블을 대상으로 3개의 트리거를 생성한다. product 테이블에 데이터를 추가/삭제/ 수정 되었을 때 각 트리거가 실행되어 log 테이블 act 컬럼에 로그를 기록한다.

create trigger itrigger insert on product
begin
insert into log(act) values('INSERT Action');
end;

create trigger dtrigger delete on product
begin
insert into log(act) values('DELETE Action');
end;

create trigger utrigger update on product
begin
insert into log(act) values('UPDATE Action');
end;

sqlite> create trigger itrigger insert on product
   ...> begin
   ...> insert into log(act) values('INSERT Action');
   ...> end;
sqlite> 
sqlite> create trigger dtrigger delete on product
   ...> begin
   ...> insert into log(act) values('DELETE Action');
   ...> end;
sqlite> 
sqlite> create trigger utrigger update on product
   ...> begin
   ...> insert into log(act) values('UPDATE Action');
   ...> end;
sqlite> 

그러면 product 테이블에 데이터를 추가하거나 데이터를 수정하거나 삭제했을 때, 트리거가 실행 되었는지 확인한다. 먼저 product 테이블에 데이터를 추가한다. 그러고 log 테이블을 확인한다.

insert into product values (1, 'Book', 15000);

product 테이블에 데이터를 추가할 때 트리거가 실행되어 log 테이블에 데이터를 추가된다.

sqlite> insert into product values (1, 'Book', 15000);
sqlite> 
sqlite> select * from log;
1|INSERT Action
sqlite> 

다음은 product 테이블에 추가한 데이터를 일부 수정한다. 그러고 log 테이블을 확인한다.

update product set price = 25000 where id = 1;

product 테이블의 데이터가 수정 될 때마다 트리거가 실행되어 log 테이블에 데이터를 추가하고 있다.

sqlite> update product set price = 25000 where id = 1;
sqlite> 
sqlite> select * from log;
1|INSERT Action
2|UPDATE Action
sqlite> 

마지막으로 product 테이블에 추가한 데이터를 삭제한다. 그러고 log 테이블을 확인한다.

delete from product where id = 1;

product 테이블의 데이터가 삭제될 때마다 트리거가 실행되어 log 테이블에 데이터를 추가하고 있다.

sqlite> delete from product where id = 1;
sqlite> 
sqlite> select * from log;
1|INSERT Action
2|UPDATE Action
3|DELETE Action
sqlite> 

이렇게 생성된 트리거에 설정한 동작을 했을 때, 각각 트리거에 설정된 SQL 문이 실행되는 것을 확인할 수 있다.

BEFORE 트리거와 AFTER 트리거

예를 들어 데이터가 추가되었을 때에 실행되는 트리거를 생성하는 경우에는 먼저 데이터가 추가되고 나서 트리거에 설정된 SQL 문이 실행되지만, BEFORE 키워드를 지정하면 데이터가 추가 되기 전에 먼저 트리거에 작성된 SQL 문이 실행 된 후에 데이터의 추가가 된다.

형식은 다음과 같다. 트리거명 뒤에 BEFORE를 작성한다. 다음 형식은 INSERT를 작성하는 경우를 예로 들었지만, DELETE와 UPDATE에도 동일하다.

CREATE TRIGGER 트리거명 BEFORE INSERT ON 테이블명
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

동일하게 AFTER를 작성할 수도 있지만, AFTER가 기본 동작이기에 명시적으로 작성할 필요는 없다. (그러나 정말 그런지는 확인이 되지 않았다.)

CREATE TRIGGER 트리거명 AFTER INSERT ON 테이블명
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

구체적인 예를 들 수 없지만, BEFORE 트리거를 사용할 때는 주의가 필요한다. 아래 공식 사이트에 기재되어 있는 내용을 그대로 게시하도록 하겠다. 가능하다면 AFTER 트리거를 사용하는 것이 좋다는 내용이다.

SQLite Query Language: CREATE TRIGGER

Cautions On The Use Of BEFORE triggers

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.

The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the rowid is not explicitly set to an integer.

Because of the behaviors described above, programmers are encouraged to prefer AFTER triggers over BEFORE triggers.

FOR EACH ROW 및 FOR EACH STATEMENT

트리거를 만들 때 FOR EACH ROW를 지정하면 대상 테이블에 포함된 데이터에 대해 UPDATE 또는 DELETE가 1행이 실행될 때마다 트리거로 설정된 SQL 문이 실행된다. 또한 FOR EACH STATEMENT를 지정하면 몇 행의 데이터에 대한 작업을 수행 되어도 트리거로 설정된 SQL 문은 한 번만 실행되지 않는다.

CREATE TRIGGER 트리거명 INSERT ON 테이블명 FOR EACH ROW
 BEGIN
  SQL문1;
  SQL문2;
  ...
 END;

다만, SQLite에서는 현재 FOR EACH ROW 밖에 대응되지 않는다. 그러기에 FOR EACH ROW를 생략해도 FOR EACH ROW이 설정된 것으로 간주된다.