[MySQL] 트리거 trigger

참고 자료

유투브 쉬운코드 SQLtrigger

SQL Trigger?

데이터에 변경이 생겼을 때 DB에 insert, update, delete가 발생했을 때 이것이 계기가 되어 자동적으로 실행되는 프로시저(procedure)를 의미 

SQL Trigger 예제1

유저의 닉네임을 바꾸면(update) 유저의 이름을 기록하는 로그에 이전 이름을 INSERT 하는 트리거를 만들어 보자

 

users, nickname_logs 테이블 

CREATE TABLE users (
    id int primary key auto_increment,
    nickname varchar(20) not null
);

CREATE TABLE nickname_logs (
    id int primary key auto_increment,
    users_id int,
    prev_nickname varchar(20) not null,
    until datetime,
    foreign key (users_id) references users(id) on delete cascade
);

이전 stored procedure 예제를 연습하면 만든 테이블을 재사용 한다. 

 

trigger 생성 

DROP TRIGGER IF EXISTS log_user_nickname_trigger;
delimiter ^^
CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE
ON users FOR EACH ROW
BEGIN
    INSERT INTO nickname_logs (users_id, prev_nickname, until) values(OLD.id, OLD.nickname, now());
END
^^
delimiter ;

 

BEFORE UPDATE 

OLD: update 되기 전의 tuple을 가리킴

 

BEFORE  DELETE

OLD: delete 되기 전의 tuple을 가리킴

 

update 이전 users 테이블 

update 이전 nickname_logs 테이블 

 

update

UPDATE users SET nickname = 'JEUS' where nickname = 'zeus1212';

 

update 이후 users 테이블 

update 이후 nickname_logs 테이블 

 

SQL Trigger 예제2

상품 구매를 하면 USER_BUY_STATS 테이블에 구매한 가격을 더하는 트리거를 만들어보자 

 

BUY, USER_BUY_STATS 테이블 생성 

CREATE TABLE BUY (
    id int primary key auto_increment,
    user_id int,
    price int not null,
    buy_at datetime,
    check(price >= 0),
    foreign key (user_id) references users(id) ON DELETE CASCADE
);
CREATE TABLE USER_BUY_STATS (
    user_id int primary key,
    price_sum int,
    foreign key (user_id) references users(id) ON DELETE CASCADE
);

 

트리거 SUM_BUY_PRICES_TRIGGER 생성 

DROP TRIGGER IF EXISTS sum_buy_prices_trigger;
delimiter ^^
CREATE TRIGGER sum_buy_prices_trigger
AFTER INSERT
ON BUY FOR EACH ROW
BEGIN
    UPDATE USER_BUY_STATS SET price_sum = price_sum + NEW.price WHERE user_id = NEW.user_id;
END
^^
delimiter ;

 

AFTER INSERT

NEW: INSERT 된 tuple을 가리킴

 

AFTER UPDATE 

NEW: UPDATE 된 tuple을 가리킴

 

INSERT 이전 BUY 테이블 

 

INSERT 이전 USER_BUY_STATS  테이블 

 

INSERT 실행 

INSERT INTO BUY (user_id, price, buy_at) VALUES(1, 9999, now());

 

INSERT 이후 BUY 테이블 

 

INSERT 이후 USER_BUY_STATS  테이블 

트리거 여러 이벤트 한번에 감지하기 

MySQL은 불가능 ;;

 

PostgreSQL 예제

CREATE TRIGGER avg_empl_salary_trigger
AFTER INSERT OR UPDATE OR DELETE
ON employee FOR EACH ROW
EXECURE FUNCTION update_avg_empl_salary();

 

for each row VS for wach statement

MySQL은 불가능 ;;;;

 

PostgreSQL 예제 

 

해당 트리거는 평균 연봉을 계산하는 트리거이다. 

CREATE TRIGGER avg_empl_salary_trigger
AFTER INSERT OR UPDATE OR DELETE
ON employee FOR EACH ROW
EXECUTE FUNCTION update_avg_empl_salary();

 

만약 아래와 같은 sql이 실행된다면?

UPDATE employee SET salary = 1.5 * salary WHERE dept_id = 1003;

 

현재 employee 테이블에 dept_id가 1003인 직원이 5명 있다고 하면 

총 5개의 row에 연봉이 1.5배로 update 될 것이다.

 

그럼 트리거 또한 5번 실행된다 

이는 비효율적이다. 평균 연봉 계산은 마지막에 1번만 실행돼도 똑같다. 

CREATE TRIGGER avg_empl_salary_trigger
AFTER INSERT OR UPDATE OR DELETE
ON employee FOR EACH STATEMENT
EXECUTE FUNCTION update_avg_empl_salary();

 

이렇게 트리거를 FOR EACH ROW에서 FOR EACH STATEMENT로 바꿔 

TRIGGER가 1번만 실행되게 바꿔줬다. 

트리거 사용 시 주의 사항

소스 코드로는 발견할 수 없는 로직이기 때문에 어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다

프로시저는 그래도 애플리케이션 서버에서 호출 하니까 가시성이 있지만

트리거는 가시적이지 않아서 문제 파악이 어렵다. 

 

또한 트리거가 재귀적으로 호출되면 더욱 파악이 어렵다 

트리거 A → 트리거 B

 

이러한 특징으로 디버깅이 어렵다 

그래서 문서 정리가 특히나 중요하다 

 

과도한 트리거 사용은 DB에 부담을 주고 응답을 느리게 만든다