-
[SQLite] 트리거 (TRIGGER)Software/SQLite 2018. 4. 16. 00:40반응형
1. 트리거 생성
: 특정 테이블이나 뷰에 INSERT, DELETE, UPDATE와 같은 데이터 변경/조작 같은 동작이 일어 났을 때 미리 지정해둔 동작이 수행되도록 하는 것을 말한다.
사용 방법은 아래와 같다.
CREATE TRIGGER [트리거 명]
[동작 시점] [SQL 연산] ON [테이블 명]
BEGIN
[수행 할 SQL 문]
END;
먼저 CREATE TRIGGER 키워드 이후 생성하고자 하는 트리거 이름을 지정하게 되는 데 이 트리거 이름은 해당 데이터베이스 내 유일한 값을 가져야 한다. 그 다음에는 이 트리거가 동작할 시점과 SQL 연산, 그리고 동작이 이루어 질 테이블 명을 정의하는데 트리거 동작 시점 옵션은 아래 표를 참고하면 된다.
키워드
의미
BEFORE
대상 테이블의 INSERT, DELETE, UPDATE 동작 직전에 미리 지정한 동작이 수행
AFTER
INSERT, DELETE, UPDATE 동작 직후에 미리 지정한 동작이 수행. (BEFORE와 반대)
INSTEAD OF
대상 테이블의 INSERT, DELETE, UPDATE 동작 대신 지정한 동작이 대신 수행
마지막으로 BEGIN과 END 키워드 사이에는 수행할 트리거 동작을 작성한다. 이 때 동작은 SQL 문으로 작성하고 수행할 SQL 문은 여러 개 등록이 가능하다.
2. 트리거 삭제
트리거의 삭제는 테이블 삭제와 유사하다.
DROP TRIGGER [트리거 명];
3. 트리거 사용법
그럼 지금 부터는 트리거 사용의 적절한 사용법과 예시에 대해서 살펴보도록 하자.
cars라는 테이블에 새로운 차량 정보가 INSERT/DELETE/UPDATE될 때 마다 이를 기록하기 위한 별도의 cars_data_history table이 있다고 하자. 그럼 우리는 위와 같은 데이터 동작이 cars 테이블에서 이루어 질 때마다 해당 데이터 변경 이력을 <cars_data_history>에 기록해야 할 것이다.
참고로 이러한 데이터 변경 이력 기록을 위해서는 우리는 먼저 "NEW", "OLD"라는 키워드를 알아 두어야 한다. "NEW"는 "NEW.[컬럼 이름]"과 같은 형태로 사용 되어 새롭게 입력 되거나 변경된 레코드의 컬럼 값을 의미한다. "OLD"는 "OLD.[컬럼 이름]"으로 쓰일 수 있고 삭제 혹은 수정되기 이전의 컬럼 값을 나타낸다.
- INSERT
sqlite> CREATE TRIGGER cars_insert
...> BEFORE INSERT ON cars
...> BEGIN
...> INSERT INTO cars_register_history (job, name, new_price)
...> VALUES ('INSERT', NEW.name, NEW.price);
...> END;
위와 같이 cars 테이블에 값이 INSERT 될 때 마다 cars_register_history 테이블에 해당 정보를 추가하는 트리거를 생성했다.
sqlite> INSERT INTO cars(brand_id, name, price)
...> VALUES(1, 'Avante', 1000000);
sqlite> select * from cars_register_history ;
job name old_price new_price
---------- ---------- ---------- ----------
INSERT Avante 1000000
Cars 테이블에 추가한 데이터가 cars_insert 트리거에 의해 cars_register_history에도 추가된 것을 확인할 수 있다.
- DELETE
sqlite> CREATE TRIGGER cars_delete
...> AFTER DELETE ON cars
...> BEGIN
...> INSERT INTO cars_register_history (job, name, old_price)
...> VALUES ('DELETE', OLD.name, OLD.price);
...> END;
이번에는 cars 테이블에 특정 데이터가 삭제될 때 마다 이를 기록하는 트리거를 생성해 보았다.
이 때 "NEW" 키워드 대신 "OLD" 키워드를 사용한 것을 기억 하자.. DELETE의 경우 추가되거나 수정된 데이터가 없고 오로지 삭제만 된 데이터밖에 없으니 "NEW" 대신 "OLD"키워드가 사용되었다.
sqlite> DELETE FROM cars
...> WHERE name='320d';
sqlite> select * from cars_register_history ;
job name old_price new_price
---------- ---------- ---------- ----------
INSERT Avante 1000000
DELETE 320d 4000000
'320d'라는 name을 가지는 레코드를 삭제하였고 이 또한 cars_delete 트리거에 의해 cars_register_history에 기록되었다.
- UPDATE
sqlite> CREATE TRIGGER cars_update
...> AFTER UPDATE OF price ON cars
...> BEGIN
...> INSERT INTO cars_register_history (job, name, old_price, new_price)
...> VALUES('UPDATE', NEW.name, OLD.price, NEW.price);
...> END;
마지막으로 UPDATE를 살펴보도록 하자.
Cars 테이블의 price 컬럼 값이 변경될 때 트리거가 동작할 수 있도록 UPDATE OF 뒤 모니터링할 price 컬럼며을 적어 준 것을 기억하자. 더불어 추가/삭제 와는 다르게 데이터가 변경되기 전 값과 변경 된 후의 정보를 기록하기 위해 "OLD", "NEW" 키워드가 모두 사용되었다.
sqlite> UPDATE cars SET price = 2000000
...> WHERE name = 'Sonata';
sqlite> SELECT * FROM cars_register_history;
job name old_price new_price
---------- ---------- ---------- ----------
INSERT Avante 1000000
DELETE 320d 4000000
UPDATE Sonata 1500000 2000000
이번에도 역시 cars_update 트리거에 의해 수정 전 price와 수정 후 price 값이 각각 cars_register_history 테이블의 old_price, new_price 컬럼에 문제 없이 기록되었음을 확인 할 수 있다.
4. 트리거 동작을 위한 조건 설정
앞서 보여준 트리거 예시들은 특정 조건 없이 해당 테이블에 INSERT/DELETE/UDPATE와 같은 동작이 실행되었을 때 무조건 적으로 트리거가 동작되도록 되어 있었다. 그러나 우리는 특정 조건을 만족했을 경우에만 트리거가 동작하도록 조건을 설정할 수 있다. 이러한 조건은 트리거 생성 시 WHEN 절을 통해 설정 가능하다.
아래의 트리거는 brand_id가 2인 경우에만 해당 트리거가 동작 되도록 하는 조건이 설정되어 있다.
sqlite> CREATE TRIGGER cars_insert_kia
...> BEFORE INSERT ON cars
...> WHEN NEW.brand_id = 2
...> BEGIN
...> INSERT INTO cars_register_history (job, name, new_price)
...> VALUES ('INSERT KIA', NEW.name, NEW.price);
...> END;
sqlite> INSERT INTO cars(brand_id, name, price)
...> VALUES(2, "K9", 4000000);
sqlite> SELECT * FROM cars_register_history;
job name old_price new_price
---------- ---------- ---------- ----------
INSERT Avante 1000000
DELETE 320d 4000000
UPDATE Sonata 1500000 2000000
INSERT KIA K9 4000000
5. 트리거를 이용한 뷰(VIEW) 수정
뷰(View)는 원래 읽기만 지원하는 가장 테이블이므로 뷰에 직접 INSERT나 DELETE/UPDATE와 같은 작업을 시도하면 에러가 발생한다. 그러나 우리는 트리거의 INSTEAD OF를 이용하여 우회적으로 뷰에 위와 같은 동작들이 가능하도록 할 수 있다.
sqlite> SELECT * from car_brand_view;
name price brandName
---------- ---------- ----------
Sonata 2000000 Hyundai
K5 1500000 Kia
K3 1200000 Kia
K7 2200000 Kia
Avante 1000000 Hyundai
K9 4000000 Kia
우리의 목표는 위와 같은 car_brand_view 뷰에 INSERT나 DELETE 혹은 UPDATE를 위한 쿼리문을 작성하고 이 쿼리문이 정상적으로 동작하여 우리가 의도한 데이터 변경 작업이 cars 테이블에서 이루어 지도록 하는 것이다. 말이 어려우니 아래 예시를 살펴 보도록 하자. 그 전에 우리는 이를 위한 트리거를 먼저 작성해 보도록 하자.
sqlite> CREATE TRIGGER cars_view_update
...> INSTEAD OF UPDATE ON car_brand_view
...> BEGIN
...> UPDATE cars SET price = NEW.price
...> WHERE price = OLD.price;
...> END;
INSTEAD OF 트리거를 통해 car_brand_new 뷰에 UPDATE 구문이 실행되면 해당 구문 대신 cars 테이블에 price 컬럼 값이 변경되도록 하였다. 실제로 잘 동작하는 지 한번 살펴보도록 하자
sqlite> UPDATE car_brand_view SET price = 2000000
...> WHERE price = 1500000;
sqlite> SELECT * FROM car_brand_view;
name price brandName
---------- ---------- ----------
Sonata 2000000 Hyundai
K5 2000000 Kia
K3 1200000 Kia
K7 2200000 Kia
Avante 1000000 Hyundai
K9 4000000 Kia
sqlite> SELECT * FROM cars;
car_id brand_id name price
---------- ---------- ---------- ----------
1 1 Sonata 2000000
3 2 K5 2000000
4 2 K3 1200000
5 2 K7 2200000
7 1 Avante 1000000
8 2 K9 4000000
원래 price값이 1500000 였던 K5 차량 가격이 2000000 으로 정상적으로 변경되었음을 확인 할 수 있다.
반응형'Software > SQLite' 카테고리의 다른 글
[SQLite] 락 매커니즘 & 트랜잭션 (0) 2018.05.12 [SQLite] 트랜잭션 (TRANSACTION) (0) 2018.05.09 [SQLite] 뷰 (VIEW) (0) 2018.03.25 [SQLite] 테이블 제약 조건 (0) 2018.03.19 [SQLite] 인덱스(INDEX) (0) 2018.03.19 댓글