Trigger (트리거)
트리거(Trigger)는 사전적 의미로 '방아쇠'라는 뜻이다.
MySQL에서 트리거는 테이블에서 어떤 이벤트가 발생했을 때 자동으로 실행되는 것을 말한다.
즉, 어떤 테이블에서 특정한 이벤트(update, insert, delete)가 발생했을 때, 실행시키고자 하는 추가 쿼리 작업들을 자동으로 수행할 수 있게끔 트리거를 미리 설정해 두는 것이다.
예를 들어 고객이 물건을 구매해 구매 테이블에 정보가 insert되면, 등록된 트리거가 발동해 물품 테이블을 자동으로 update 쿼리문을 실행하게 하고, 또 등록된 트리거가 발동해 배송테이블에 insert 쿼리문을 실행시키게 끔 할 수 있다.
- 데이터베이스 트리거(Database Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미
- 테이블에 DML문(Insert, Update, Delete 등) 이벤트가 발생될 때 작동
- 테이블에 부착되는 프로그램 코드
- 자기가 직접 실행 불가. 테이블에 이벤트 일어나야 자동 실행
- IN, OUT 매개 변수를 사용할 수 없음
- MySQL은 View에 트리거 부착 불가
트리거 종류
행 트리거
- 테이블 안의 영향을 받은 행 각각에 대해 실행된다.
- 변경 전 또는 변경 후의 행은 OLD, NEW라는 가상 줄 변수를 사용하여 읽을 수 있다
- old - 예전 데이터 즉, delete 로 삭제 된 데이터 또는 update 로 바뀌기 전의 데이터
- new - 새 데이터 즉, insert 로 삽입된 데이터 또는 update 로 바뀐 후의 데이터
- old - 예전 데이터 즉, delete 로 삭제 된 데이터 또는 update 로 바뀌기 전의 데이터
| 트리거 이벤트 | OLD | NEW |
| INSERT | X | O |
| UPDATE | O | O |
| DELETE | O | X |
※ 참고
update일 경우, 기존의 데이터(old)를 새로운 데이터로(new)로 교체하는 거니까 old, new 둘다 쓸 수 있다.
하지만 delete일 경우 기존 데이터를 삭제하는 거니 new를 인식할수가 없다.
insert 역시 새로 삽입하는거니 old가 있을리 없다.
문장 트리거
- INSERT, UPDATE, DELETE 문에 대해 한번만 실행된다
- 삽입, 갱신 또는 삭제되는 행 수에 관계없이 각 트랜잭션에 대해 명령문 레벨 트리거가 한 번 실행된다.
BEFORE 또는 AFTER : 트리거가 실행되는 시기를 지정한다.
- AFTER 트리거 : 쿼리 이벤트 작동하기 후
- BEFORE 트리거 : 쿼리 이벤트 작동하기 전에 -> 미리 데이터를 확인 가능!
INSTEAD OF : 트리거를 원래 문장 대신 수행한다.
트리거 사용법
예제 1. UPDATE
트리거 생성
DELIMITER $$
CREATE TRIGGER update_item
AFTER UPDATE -- {BEFORE | AFTER} {INSERT | UPDATE| DELETE } 중 언제 어떤 작업을 할지 정한다
ON sale_table -- 트리거를 부착할 테이블
FOR EACH ROW -- 아래 나올 조건에 해당하는 모든 row에 적용한다는 뜻
BEGIN
-- 트리거시 실행되는 코드
IF NEW.discount_rate != OLD.discount_rate THEN -- update 트리거는 old와 new 값이 존재한다.
UPDATE item_table SET discount_rate = NEW.discount_rate WHERE discount_rate = OLD.discount_rate;
END IF;
END $$
DELIMITER ;BEGIN~END 사이에 조건문과 실행문을 작성한다.
sale_table 테이블의 변경 전/후를 기준으로 필드 앞에 변경 전은 OLD, 변경 후는 NEW 키워드가 붙는다
따라서 IF NEW.discount_rate != OLD.discount_rate THEN의 의미는,
sale_table의 AFTER UPDATE 후 discount_rate 필드의 값과 변경 전 discount_rate 필드의 값이 불일치한다는 조건을 의미한다.
이 조건을 만족하는 row는 IF문 아래 작성된 UPDATE 쿼리문을 실행하게 된다.
트리거 실행
TRIGGER update_item 은 AFTER UPDATE ON sale_table 이 실행되면 자동으로 트리거가 작동하는 구조라, 당연히 sale_table에 update쿼리를 주면 된다.
UPDATE sale_table SET ... WHERE ...;
트리거 확인
show triggers;
트리거 삭제
DELETE TRIGGERS;
예제 2. 자동 백업
예제로 만들 트리거는 채팅 내용을 삭제 시, 해당 내용을 백업 테이블에 자동으로 백업하는 트리거를 만들 것이다.
테이블(table) 생성
CREATE TABLE chat( id VARCHAR(32), answer VARCHAR(32) NOT NULL );
CREATE TABLE chatBackup( idBackup VARCHAR(32), answerBackup VARCHAR(32) NOT NULL );chat 과 chatBackup 두개의 테이블이 있고
INSERT INTO chat VALUE ('pigg', '안녕');
INSERT INTO chat VALUE ('lemon', '반가워');
INSERT INTO chat VALUE ('pigg', '오늘 날씨 어때?');
INSERT INTO chat VALUE ('lemon', '날씨 좋아');chat 테이블에만 4개의 데이터를 넣었다.
트리거 생성
DELIMITER $$
CREATE TRIGGER autoBackup
BEFORE DELETE ON chat
FOR EACH ROW
BEGIN
DECLARE idTemp VARCHAR(32);
DECLARE answerTemp VARCHAR(32);
SET idTemp = OLD.id;
SET answerTemp = OLD.answer;
INSERT INTO chatBackup VALUE (idTemp, answerTemp);
END $$
DELIMITER ;트리거를 다 작성하고
SHOW TRIGGERS;
를 통해서 정상적으로 만들어졌는지 확인할 수 있다.
SHOW TRIGGERS;
트리거가 정상적으로 만들어졌으니 테스트를 해보자.
트리거의 발동 조건은 chat 테이블의 테이터를 삭제했을 경우다.
(BEFORE DELETE ON chat)
DELETE FROM chat WHERE id='pigg';pigg 의 모든 데이터(채팅)을 삭제하도록 하겠다.
SELECT * FROM chat;
chat 테이블에서는 정상적으로 삭제했다.
이제 트리거 결과를 확인해보자.
트리거 결과
트리거로 건들었던 chatBackup 테이블을 확이해보면 트리거가 정상적으로 작동되었는지 확인할 수 있다.
SELECT * FROM chatbackup;
chat 테이블에서 삭제했던 데이터가 트리거로 인해서 chatBackup 테이블에 자동으로 백업된 것을 확인할 수 있다.
트리거 vs 프로시저
- 트리거 이벤트(INSERT | UPDATE | DELETE)가 실행된 테이블을, 트리거를 통해 수정하려 하면 에러 발생
- 예를 들어 A 테이블에 INSERT 트리거를 생성하는 과정에서 트리거 처리에서 A테이블을 UPDATE를 하게 되는 경우 에러가 발생한다.
- 트리거 테이블을 수정하려면 이러한 경우에는 프로시저로 처리해야함
- 트리거는 매 이벤트(INSERT | UPDATE | DELETE)마다 동일하게 처리하여 적용하는 경우 사용하고 프로시저는 그렇지 않은 경우 사용한다
- 예를 들어 통신사가 회원 등급을 결정할 때 전년도 사용 비용을 기준으로 처리하기 때문에 회원 등급을 결정하는 처리를 트리거가 아닌 프로시저로 해야한다.
그래서 언제 사용할까?
검색 결과 보통 아래와 같은 경우 Trigger 사용 한다고 한다.
- 회원 탈퇴 시 일정기간동안 탈퇴회원의 데이터를 보관하기 위해
- 비밀번호를 변경할 경우 기존 비밀번호 데이터를 일정기간 동안 보관하기 위해
이렇게 트리거는 임시 저장, 데이터 복구, 채팅 로그 등 다양한 곳에 활용이 가능하다!
예를 계정을 해킹 당해서 아이템을 털리는 경우가 있다. 트리거를 활용하여 해당 아이템의 이동 경로를 기록해 놓는다면 해킹으로 인해 잃은 아이템을 복구할 수 있다.
또 다른 예시는 다음과 같다.
회사 프로젝트에서 필수적으로 외부 모듈을 사용하게 되었을 때, 특정 테이블에 데이터를 저장하면 외부 모듈에서 해당 데이터를 외부 서버로 전송 후 저장한 데이터를 삭제 하고 또 다른 테이블에 결과 데이터를 월별로 저장하게 되었다.
이때 만약 결과 데이터를 월별로 구별해서 조회가 아닌 한 곳에서 일괄 조회가 필요하고 외부 모듈을 직접 핸들링 하고 싶었지만 Side Effect가 야기 될 때 trigger를 활용할 수 있다.
참고