MySQL (18) - 트리거(Trigger)를 만들어보자

DB에서 데이터가 변경될 때 추가적인 작업이 필요한 경우가 있습니다. 이 작업은 일반적으로 클라이언트-서버 구조에서는 서버 측에서 처리될 수도 있지만, DB 내부에서도 이를 처리할 수 있습니다. 이를 위해 트리거를 활용하는 방법을 알아보려고 합니다.

Trigger - pixabay

 

Trigger (트리거)

트리거는 방아쇠라는 뜻을 가지고 있습니다. 방아쇠를 당기면 총알이 나가는 것처럼 데이터가 바뀌면 DB에 특정한 쿼리를 자동으로 실행할 수 있습니다. 어떤 테이블에서 이벤트가 발생했을 때, 추가로 필요한 쿼리 작업들을 자동으로 수행할 수 있게 트리거를 설정합니다.

 

Trigger 생성 방법

CREATE TRIGGER ex_trigger
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON my_table
FOR EACH ROW
BEGIN
    -- 트리거 내부 로직 작성
    UPDATE my_table SET
           student_number = NEW.student_number
         , name = OLD.name;
    
END;

먼저 트리거를 조정 할 수 있는 요소는 다음과 같습니다.

• BEFORE : 쿼리 이벤트가 작동하기 전
• AFTER : 쿼리 이벤트가 작동한 후

• INSERT : 생성
• UPDATE : 수정
• DELETE : 삭제

• FOR EACH ROW : 한 줄이 바뀌는 경우에 실행 (구문 생략 시 전체 작업에 대해 한 번만 실행하기)

 

또한 각각의 COLUMN도 변경 전(OLD), 변경 후(NEW)에 대한 데이터를 호출하여 정보를 조작할 수 있습니다.

• old - 변경 전 데이터, delete로 삭제된 데이터 혹은 update에서 바뀌기 전 데이터
• new - 변경 후 데이터, insert로 생성된 데이터 혹은 update로 바뀐 후 데이터

이벤트	OLD	NEW
INSERT	X	O
UPDATE	O	O
DELETE	O	X

 

 

백업데이터 생성 예시

CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
    INSERT INTO backup_table (student_number, name) VALUES (NEW.student_number, NEW.name);
END;

위 코드는 my_table의 데이터에 INSERT 이벤트가 일어난 후 backup_table에 저장하는 트리거 입니다. 데이터가 INSERT 될 때마다 backup_table에 해당 데이터를 저장하는 쿼리입니다.

 

여러 조건을 한 번에 실행시키기

CREATE TRIGGER my_trigger
AFTER INSERT, UPDATE, DELETE ON my_table
FOR EACH ROW
BEGIN
    UPDATE my_table SET
           student_number = NEW.student_number
         , name = NEW.name;

END;

BEFORE과 AFTER은 동시에 실행할 수 없으나 INSERT, UPDATE, DELETE는 동시에 실행시킬 수 있습니다.

 

Trigger 활성화

--트리거 활성화
ALTER TRIGGER ex_trigger ENABLE; 

--트리거 비활성화
ALTER TRIGGER ex_trigger DISABLE;

트리거는 생성 시 기본적으로 활성화가 됩니다. 데이터 조작 시 오류를 최소화하기 위해 활성화 비활성화를 정할 수 있습니다. 해당 쿼리는 위와 같습니다.

 

Trigger 삭제 및 조회

--트리거 조회
SHOW TRIGGERS;

--트리거 삭제
DROP TRIGGER ex_trigger;

 

마지막으로

현재 직장에서 백엔드보다 쿼리를 중심으로 사용하고 있습니다. 자동화를 하기 위해 트리거는 정말 좋은 방법이라고 생각합니다.