TRIGGER

✒️ 2025-05-28 17:24 내용 수정

참고 자료 : MySQL Trigger Syntax and Examples, MySQL 트리거(Trigger) 개념 & 사용법, 썸머워즈's Oracle 오라클_트리거(TRIGGER)

어떤 테이블에 일어나는 이벤트를 기반으로 자동 실행되는 동작을 기술한 것

INSERT, UPDATE, DELETE가 발생했을 때 특정 동작을 수행하도록 할 수 있다.
한 테이블에서 이벤트가 발생했을 때 다른 테이블에 동작을 수행하도록 설정할 수 있다.
- 예) 주문 테이블에서 주문이 새로 추가됬을 때 상품 정보를 저장한 상품 테이블에서 해당 상품의 재고 수량을 주문량만큼 감소시킨다.

DELIMITER $
CREATE TRIGGER `트리거이름`
[BEFORE||AFTER] [INSERT||UPDATE||DELETE] ON `트리거 이벤트 대상 테이블`
[FOR EACH ROW]
BEGIN
	[조건문이나 DML]
	END $;
DELIMITER ;

TRIGGER 제거하기

DROP TRIGGER `trigger_name`;

만든 TRIGGER 목록 확인하기

SHOW TRIGGERS;

발동 타이밍과 이벤트

트리거 발동 타이밍은 BEFORE와 AFTER 중에 선택할 수 있으며, 트리거 이벤트는 INSERT, UPDATE, DELETE 등의 DML을 사용한다.
- DML(Data Manipulation Language) 참고.
행 트리거는 조건을 만족하는 여러 행에 대해 트리거를 반복 수행한다.
- 변경 전 행은 OLD, 변경 후 행은 NEW라는 가상 줄 변수를 사용하며, OLD.columnName이나 NEW.columnName으로 변경 전 후의 컬럼을 읽어올 수 있다.
  - 트리거 이벤트에 따라 NEW가 제한(DELETE)되거나, OLD가 제한(INSERT)된다.
  - 데이터를 지운 후에는 변경 후 행이 없고, 데이터를 넣기 전엔 변경 전 데이터가 없기 때문이다.
문장 트리거는 이벤트 발생 시 한 번만 실행하며, 이벤트에 영향을 받는 행이 여러 개 존재하더라도 한 번만 수행한다.
- FOR EACH ROW 옵션을 제외하고 사용한다.

사용 예시

먼저 트리거를 적용시킬 테이블을 생성한다.
- 해당 테이블에 이벤트가 발생하면 트리거를 발동 시킬 예정이다.

-- 트리거 테스트용 테이블 생성
CREATE TABLE `test` (
	`id` int,
    `name` varchar(10)
);

트리거를 작성할 때 CREATE TRIGGER 트리거이름 으로 작성하며, DELIMITER와 BEGIN 절을 사용하여 복잡한 수행 동작을 설정할 수 있다.
- DDL(Data Definition Language), PLSQL 조건문 참고.
- 아래 예시는 test 테이블에 값을 넣을 때 id가 짝수라면 name에 "even"을, 홀수라면 "odd"로 설정한다.

-- 트리거 생성 시작
DELIMITER $
CREATE TRIGGER `trigger_name`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
	IF (NEW.`id`%2) = 0 THEN
		SET NEW.`name` = 'even';
        ELSE 
        SET NEW.`name` = 'odd';
        END IF;
    END $;
DELIMITER ;

-- 확인을 위해 test 테이블에 id 값을 넣는다.
INSERT INTO `test` (id) VALUES (40);
INSERT INTO `test` (id) VALUES (87);

-- 결과를 확인
SELECT * FROM `test`;

trigger 1.png

서로 다른 테이블에 적용한 예시

팀 프로젝트에서 한 테이블의 데이터가 수정되면 다른 테이블의 데이터를 수정하는 형식의 TRIGGER를 적용하였다.
- DB는 MySQL을 사용하여 진행했다.
- AWS에서 생성한 MySQL의 경우 TRIGGER를 생성하려고 할 때 privilege 에러가 발생한다. 이럴 경우 아래 참고 자료로 해결할 수 있다.
  - 참고 자료 : sjk5766's AWS RDS You do not have the SUPER privilege and binary loggin is enabled 에러 발생 시

A 테이블에 데이터 추가 시 B 테이블의 데이터 수정
- A 테이블에 “INSERT” 이벤트 발생 후(AFTER), B 테이블에 UPDATE를 한다.
- NEW(=새로 데이터가 추가된 A 테이블 행)의 id와 같은 id를 가지는 컬럼을 대상으로 score라는 컬럼의 평균을 계산한다.
- B 테이블의 id와 NEW 행의 id가 같은 행을 대상으로 score의 평균값을 B 테이블의 avg 컬럼에 저장한다.
- INSERT 이벤트이므로 NEW 행의 컬럼값을 사용했다.

테이블	컬럼1	컬럼2
A	id	score
B	id	avg

DELIMITER $
DROP TRIGGER IF EXISTS `update_avg_after_insert`;

CREATE TRIGGER `update_avg_after_insert`
AFTER INSERT ON `A`
FOR EACH ROW
BEGIN 
	UPDATE `B` 
	SET 
	`avg` = (SELECT AVG(score) FROM `A` WHERE `A`.id = NEW.id)
	WHERE `B`.id = NEW.id;
END $
DELIMITER ;

C 테이블에 데이터 추가 시 D 테이블의 데이터 수정
- C 테이블에 “INSERT” 이벤트 발생 후(AFTER) D 테이블에 “UPDATE”를 한다.
- C 테이블에 데이터가 들어오면 D 테이블에서 같은 id의 point 컬럼의 값을 1 올려주는 트리거
  - D 테이블의 id와 NEW(=이벤트가 발생한 후의 C 테이블의 행)의 id와 같은 경우에 "point = point + 1”을 수행한다.
- INSERT 이벤트이므로 NEW 행의 컬럼값을 사용했다.

테이블	컬럼1	컬럼2
C	id
D	id	point

DELIMITER $
DROP TRIGGER IF EXISTS `update_point`;

CREATE TRIGGER `update_point`
AFTER INSERT ON `C`
FOR EACH ROW
BEGIN 
	UPDATE `D`
	SET 
	`point` = `point` + 1
	WHERE `D`.id = NEW.id;
END $
DELIMITER ;

C 테이블에 데이터 제거 시 D 테이블의 데이터 수정
- C 테이블에 “DELETE” 이벤트 발생 후(AFTER) D 테이블에 “UPDATE”를 한다.
- C 테이블에 데이터가 제거되면 D 테이블에서 같은 id의 point 컬럼의 값을 1 내리는 트리거
  - D 테이블의 id와 NEW(=이벤트가 발생한 후의 C 테이블의 행)의 id와 같은 경우에 "point = point - 1”을 수행한다.
- DELETE 이벤트이므로 OLD 행의 컬럼값을 사용했다.

DELIMITER $
DROP TRIGGER IF EXISTS `update_point_down`;

CREATE TRIGGER `update_point_down`
AFTER DELETE ON `C`
FOR EACH ROW
BEGIN 
	UPDATE `D`
    SET 
    `point` = `point` - 1
    WHERE `D`.id = OLD.board_id;
END $
DELIMITER ;