SQLite | 트리거(Trigger) | 수정 이전과 이후의 데이터 값을 트리거에서 참조

Tags:

트리거는 데이터가 추가 되었을 때와 수정 되었을 때 지정된 SQL 문을 실행되는데, 새로 추가된 데이터 값이나 수정된 데이터의 수정 이전 및 이후의 값을 트리거 중에 실행되는 SQL문에서 참조 할 수 있다. 여기에서는 수정 이전 및 이후의 데이터 값을 트리거에서 참조하는 방법에 대해 설명한다.

NEW. 컬럼명과 OLD. 컬럼명

새로 추가된 데이터 값이나 수정 되었을 때 데이터의 수정 이전 및 이후의 값은 테이블에 포함 된 데이터의 값을 수정 할 때 다른 테이블에 포함 된 같은 값도 동시에 수정하려는 경우 등에 사용할 수 있다.

컬럼의 값을 참조하려면 트리거에서 실행되는 SQL문 안에 “NEW.컬럼명"과 “OLD.컬럼명"을 사용한다.

NEW.컬럼명
OLD.컬럼명

“NEW.컬럼명"은 INSERT문 또는 UPDATE문에서 트리거가 실행 한 경우에 사용할 수 있다. “OLD.컬럼명"은 DELETE 문 또는 UPDATE 문에서 트리거가 실행 한 경우에 사용할 수 있다. 컬럼명은 INSERT 문이나 DELETE 문에 설정되는 테이블의 컬럼이어야 한다.

간단한 예로 설명보겠다. user 테이블에 새로운 데이터 추가될 때에 실행되는 트리거를 생성한다. 트리거가 실행되면 새로 추가된 데이터의 name 컬럼의 값을 참조 다른 log 테이블에 추가하는 SQL 문을 실행한다. 추가된 데이터의 name 컬럼의 값은 new.name으로 작성하여 참조 할 수 있다.

create trigger mytrigger insert on user
begin
insert into log values (new.name);
end;

이렇게 트리거로 실행되는 SQL 문에서 트리거가 설정된 테이블에 데이터가 추가되거나 삭제된 컬럼의 값을 참조할 수 있다.

DELETE 문의 트리거 예제

DELETE에 대한 트리거를 생성하여 실행해 보겠다. 다음과 같이 두 테이블을 만든다. user 테이블은 고객 정보의 관리 테이블이다. history 테이블은 구매 내역 테이블이다.

create table user (id integer, name text);
create table history (userid integer, goods text, sales integer);

sqlite> create table user (id integer, name text);
sqlite> create table history (userid integer, goods text, sales integer);
sqlite>

각각의 테이블에 다음 데이터를 저장한다.

insert into user values (1, 'devkuma');
insert into user values (2, 'kimkc');
insert into user values (3, 'araikuma');

insert into history values (1, 'PC', 550000);
insert into history values (2, 'Mouse', 34000);
insert into history values (1, 'Watch', 85000);
insert into history values (3, 'Light', 24000);
insert into history values (2, 'Mobile', 720000);

sqlite> insert into user values (1, 'devkuma');
sqlite> insert into user values (2, 'kimkc');
sqlite> insert into user values (3, 'araikuma');
sqlite> 
sqlite> insert into history values (1, 'PC', 550000);
sqlite> insert into history values (2, 'Mouse', 34000);
sqlite> insert into history values (1, 'Watch', 85000);
sqlite> insert into history values (3, 'Light', 24000);
sqlite> insert into history values (2, 'Mobile', 720000);
sqlite>

이어서 트리거를 생성한다. user 테이블에서 데이터가 삭제되면 그 고객에 대한 데이터를 history 테이블에서 삭제하는 트리거를 생성한다.

create trigger deleteuser delete on user
begin
delete from history where userid = old.id;
end;

sqlite> create trigger deleteuser delete on user
   ...> begin
   ...> delete from history where userid = old.id;
   ...> end;
sqlite>

이제 준비는 완료되었다. 그러면 user 테이블에서 고객을 한명을 삭제해 보자. 그러고 history 테이블을 확인해 보면, 그 고객에 대한 기록이 삭제되는 것을 확인할 수 있다.

delete from user where id = 2;

sqlite> delete from user where id = 2;
sqlite> 
sqlite> select * from user;
1|devkuma
3|araikuma
sqlite> 
sqlite> select * from history;
1|PC|550000
1|Watch|85000
3|Light|24000
sqlite>

UPDATE 문의 트리거 예제

다음은 UPDATE문에 대한 트리거를 생성하여 실행해 보겠다. 다음과 같이 두 테이블을 만든다. product 테이블은 제품 관리 테이블이다. history 테이블은 구매 내역 테이블이다.

create table product (id integer, name text);
create table history (user text, name text, sales integer);

sqlite> create table product (id integer, name text);
sqlite> create table history (user text, name text, sales integer);
sqlite>

각각의 테이블에 다음 데이터를 저장한다.

insert into product values (1, 'Android');
insert into product values (2, 'iPhone');
insert into product values (3, 'iPad');

insert into history values ('devkuma', 'iPhone', 1390000);
insert into history values ('kimkc', 'iPad', 999000);
insert into history values ('araikuma', 'Android', 1092000);
insert into history values ('happykuma', 'iPhone', 965800);

sqlite> insert into product values (1, 'Android');
sqlite> insert into product values (2, 'iPhone');
sqlite> insert into product values (3, 'iPad');
sqlite> 
sqlite> insert into history values ('devkuma', 'iPhone', 1390000);
sqlite> insert into history values ('kimkc', 'iPad', 999000);
sqlite> insert into history values ('araikuma', 'Android', 1092000);
sqlite> insert into history values ('happykuma', 'iPhone', 965800);
sqlite>

이어서 트리거를 생성한다. product 테이블의 상품명이 수정되면 그 고객에 대한 데이터를 history 테이블의 같은 상품명도 함께 수정되는 트리거를 생성한다.

create trigger updateproduct update of name on product
begin
update history set name = new.name where name = old.name;
end;

sqlite> create trigger updateproduct update of name on product
   ...> begin
   ...> update history set name = new.name where name = old.name;
   ...> end;
sqlite>

이제 준비는 완료되었다. 그러면 user 테이블에서 고객을 한명을 삭제해 보자. 그러고 history 테이블을 확인해 보면, 그 고객에 대한 기록이 삭제되는 것을 확인할 수 있다.

이제 준비는 완료되었다. 그러면 product 테이블의 상품명을 하나를 수정해 보자. 그러고 history 테이블을 확인해 보면, 수정된 상품명이 포함된 이력도 새 이름으로 수정되는 것을 확인할 수 있다.

update product set name = 'iPhone 11' where name = 'iPhone';

sqlite> update product set name = 'iPhone 11' where name = 'iPhone';
sqlite> 
sqlite> select * from product;
1|Android
2|iPhone 11
3|iPad
sqlite> 
sqlite> select * from history;
devkuma|iPhone 11|1390000
kimkc|iPad|999000
araikuma|Android|1092000
happykuma|iPhone 11|965800
sqlite>

이번에는 DELETE 및 UPDATE의 경우만 대한 트리거를 생성하여 실행해봤지만, INSERT의 경우도 마찬가지로 트리거에서 실행하는 SQL 문에서 트리거의 대상이 되는 테이블에 있는 컬럼의 값을 참조할 수 있다.




최종 수정 : 2019-11-13