![[Mysql] Trigger](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2F6y0qO%2FbtrvaIDbcIi%2FSi9wKkPpKMPG2EGkT6nu5K%2Fimg.png)
개념
Trigger란 Trigger가 설정된 테이블에 특정 이벤트가 발생한 경우 미리 설정된 작업이 자동으로 실행되는 것을 말한다.
아래 그림을 보면, Table에 INSERT, UPDATE, DELETE와 같은 이벤트가 발생할 경우 Trigger가 실행된다.
Trigger에는 이벤트가 발생되면 동작되는 SQL명령문과 어떤 이벤트에 Trigger가 실행될지를 정의한다.
Trigger 속성 및 이벤트
실행 시점 속성
이벤트 발생 시점 기준으로 Trigger 실행 순서를 설정하는 속성이다.
종류는 2가지가 있으며 아래와 같다.
- AFTER
이벤트 발생 후 Trigger 실행 - BEFORE
이벤트 발생 전 Trigger 실행
이벤트
이벤트는 Trigger실행 기준이 된다.
아래의 이벤트가 한개라도 발생된다면 Trigger에 정의된 내용이 실행된다.
- INSERT
테이블에 데이터가 insert될 경우 Trigger실행 - UPDATE
테이블에 데이터가 update될 경우 Trigger실행 - DELETE
테이블에 데이터가 delete될 경우 Trigger실행
데이터 속성
Trigger가 실행될 때 어떤 값을 핸들링할지에 대한 속성이다.
종류는 아래에 명시한 2가지 종류가 있으며, 이벤트에 따라 속성 사용여부가 달라진다.
가령 INSERT이벤트의 경우 OLD속성을 사용할 수 없으며 DELETE속성에서 NEW속성을 사용 할 수 없다.
- OLD
이벤트가 발생 후 데이터 - NEW
이벤트가 발생 전 데이터
| INSERT | UPDATE | DELETE | |
| OLD | X | O | O |
| NEW | O | O | X |
Trigger 생성 문법
CREATE TRIGGER [트리거명]
[BEFORE/AFTER 트리거 실행 시점 속성설정]
ON [트리거 설정 테이블명]
FOR EACH ROW
BEGIN
[트리거 실행 내용]
END
예제
예제 테이블 생성
-- super_user table
CREATE TABLE super_user (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(150) NOT NULL,
`email` VARCHAR(150) NOT NULL
);-- super_user_log table
CREATE TABLE super_user_log (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(150) NOT NULL,
`email` VARCHAR(150) NOT NULL
);기본적인 예제를 위해 우선 Trigger예제를 위해 2개의 테이블을 생성하자.
단순하게 테이블명만 다를 뿐 컬럼과 타입은 동일하다.
super_user테이블에 trigger를 설정하고 이벤트가 발생할 경우 super_user_log테이블에 데이터를 핸들링하는 예제를 다뤄볼 것이다.
AFTER UPDATE 이벤트 Trigger
-- create trigger
DELIMITER $$
CREATE TRIGGER `trg_after_update_super_user`
AFTER UPDATE
ON `super_user`
FOR EACH ROW
BEGIN
INSERT INTO `super_user_log`(`name`, `email`) VALUES (OLD.`name`, OLD.`email`);
END $$
DELIMITER ;👉 DELIMITER $$, DELIMITER;
- trigger문법의 시작과 끝을 나타낸다.
👉 CREATE TRIGGER `trg_after_update_super_user`
- trigger를 식별하기 위해 본인이 원하는 trigger이름을 작성한다.
👉 AFTER UPDATE
- trigger가 설정되는 테이블에 UPDATE 이벤트가 발생 후 정의된 trigger가 실행 된다.
👉 ON `super_user`
- trigger를 설정하는 테이블은 super_user테이블이다.
- 즉, super_user테이블에 UPDATE가 발생할 경우 설정된 trigger가 실행 된다.
👉 FOR EACH ROW
- 각각의 row가 UPDATE이벤트가 발생 될 때 마다 trigger가 적용된다.
👉 BEGIN END $$
- 실제 trigger가 실행될 때 동작하는 SQL를 정의하는 시작점과 끝점을 명시한다.
- BEGIN과 END사이에 원하는 SQL를 작성하면 된다.
👉 INSERT INTO super_user_log(name, email) VALUES (OLD.name, OLD.email);
- super_user테이블에 UPDATE이벤트가 발생하면 super_user_log테이블에 name, email값을 insert한다.
- OLD의 경우 super_user에 UPDATE이벤트가 발생하기 전 값을 의미한다. 자세한 내용은 아래 예제 SQL문을 실행하면 쉽게 이해 할 수 있다.
INSERT INTO super_user (`name`, `email`) VALUES ("superpil", "hi@gmail.com");UPDATE super_user SET `email` = "hello@naver.com" WHERE id = "3";
우선 super_user테이블에 임의값을 저장한다.
trigger는 update이벤트가 발생할 경우 실행되기 때문에 insert의 경우 동작 되지 않는다.
insert의 후 super_user테이블에 update를 할 경우 trigger는 update이벤트를 감지해서 super_user_log테이블에 값을 insert하게 된다.
이때 super_user_log에 insert되는 값은 super_user테이블에 update하기 전 값(OLD)인 hi@gmail값이 입력된다.
이번에는 OLD가 아닌 NEW속성을 사용해보자.(그외 모든 설정값은 위 trigger와 동일하다.)
-- create trigger
DELIMITER $$
CREATE TRIGGER `trg_after_update_super_user`
AFTER UPDATE
ON `super_user`
FOR EACH ROW
BEGIN
INSERT INTO `super_user_log`(`name`, `email`) VALUES (NEW.`name`, NEW.`email`);
END $$
DELIMITER ;INSERT INTO super_user (`name`, `email`) VALUES ("superpil", "hi@gmail.com");UPDATE super_user SET `email` = "hello@naver.com" WHERE id = "4";
동일하게 super_user에 insert후 update를 하게되면 super_user_log에 hello@naver.com값이 insert된다.
NEW속성은 super_user가 update이벤트가 발생하고 새롭게 수정된 값을 의미한다.
AFTER DELETE 이벤트 Trigger
-- create trigger
DELIMITER $$
CREATE TRIGGER `trg_delete_super_user`
AFTER DELETE
ON `super_user`
FOR EACH ROW
BEGIN
INSERT INTO `super_user_log`(`name`, `email`) VALUES (OLD.`name`, OLD.`email`);
END $$
DELIMITER ;👉 DELIMITER $$, DELIMITER;
- trigger문법의 시작과 끝을 나타낸다.
👉 CREATE TRIGGER `trg_delete_super_user`
- trigger를 식별하기 위해 본인이 원하는 trigger이름을 작성한다.
👉 AFTER DELETE
- 데이터가 DELETE된 후 trigger가 실행되기 위한 설정이다.
👉 ON super_user
- super_user테이블에서 DELETE이벤트가 발생할 경우 trigger가 실행하기 위한 설정이다.
👉 FOR EACH ROW
- 각각의 row마다 DLETE이벤트가 발생할 경우 trigger가 실행된다.
👉 BEGIN END $$
- 실제 trigger가 실행될 때 동작하는 SQL를 정의하는 시작점과 끝점을 명시한다.
- BEGIN과 END사이에 원하는 SQL를 작성하면 된다.
👉 INSERT INTO super_user_log(name, email) VALUES (OLD.name, OLD.email);
- 일반적인 SQL INSERT문과 동일하게 작성하면 된다.
- super_user테이블에 DELETE이벤트가 발생할 경우 super_user_log테이블에 해당 값을 INSERT하는 구문이다.
- 다만, DELETE된 값을 super_user_log테이블에 INSERT하기 위해서 OLD속성을 사용한다. 자세한 내용은 아래 예제를 보면 쉽게 이해할 수 있다.
INSERT INTO super_user (`name`, `email`) VALUES ("superpil", "hello@naver.com");DELETE FROM super_user WHERE id = "6";
우선 super_user테이블에 delete이벤트를 발생하기 위해 super_user테이블에 임의값을 넣는다.
이후 임의값을 delete하게 되면 super_user테이블에 값은 삭제 되고 trigger가 delete이벤트를 감지하여 삭제된 값이 super_user_log 테이블에 insert되었다.
참고로 DELETE의 경우 NEW속성을 사용할 수 없다.
Trigger 조회
SHOW triggers;
Trigger 삭제
DROP TRIGGER [trigger명]ex) DROP TRIGGER superpilTrg
그래서 언제 사용할까?
검색결과 다른분들은 아래와 같은 경우 Trigger사용 한다고 한다.
- 회원 탈퇴 시 일정기간동안 탈퇴회원의 데이터를 보관하기 위해
- 비밀번호를 변경할 경우 기존 비밀번호 데이터를 일정기간 동안 보관하기 위해
나의 경우도 다른분들과 마찬가지로 trigger의 사용의도가 크게 다르지 않다.
회사 프로젝트에서 필수적으로 외부 모듈을 사용하게 되었는데, 특정 테이블에 데이터를 저장하면 외부 모듈에서 해당 데이터를 외부 서버로 전송 후 저장한 데이터를 삭제 하고 또 다른 테이블에 결과 데이터를 월별로 저장하게 되었다.
우린 결과 데이터를 월별로 구별해서 조회가 아닌 한 곳에서 일괄 조회가 필요했다.
외부 모듈을 직접 핸들링 하고 싶었지만 Side Effect가 야기 되어 trigger를 사용하기로 결정했다.
DBeaver에서 Trigger생성되지 않는 이슈
평소 DB작업을 할때 Mysql Workbanch보다 DBeaver를 사용하는 것을 선호 한다.
그런데 DBeaver에서 Trigger를 생성할려니 위와 같은 결과가 출력되면서 Trigger가 생성되지 않는다. (Workbanch에서는 문제 없이 생성된다.)
따라서 DBeaver에서는 명령문이 아닌 직접 생성해야 한다.
Trigger를 생성하고 싶은 테이블을 클릭하면 Triggers가 보인다.
Triggers폴더에서 마우스 오른쪽을 클릭 하면 첫번째 목록에 Create New Trigger를 선택한다.
선택 후 화면에 출력되는 팝업창에 Trigger명을 작성하면 아래와 같이 Trigger문법을 작성할 수 있는 공간이 나타난다.
Trigger문법을 Source에 넣으면 되는데, 명령문으로 Trigger를 생성할때랑 다른점은 $$표시와 DELIMITER구문을 사용하지 않아도 된다. (사용할경우 문법에러 발생!)
문법 작성 후 저장(Ctrl+s)를 하면 위와 같은 창이 뜨고 Persist버튼을 클릭하면 정상적으로 Trigger가 생성된다.
잘못된 내용이나 보충이 필요한 내용이 있다면 댓글 남겨 주세요!
주니어 개발자에게 큰 도움이 됩니다! 감사합니다! 😃
Reference
[MySQL] 트리거(Trigger)의 활용 - Trigger기본개념
개발 기록
포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!