[데이터베이스] 트리거(Trigger)에 대하여 - AWS RDS

방아쇠

 

총을 쏠 때 방아쇠(트리거)를 당기면 총알이 총구에서 발사되기까지

총기 내부에서 여러 많은 과정을 거쳐서 총알이 나가지요?

 

이처럼 데이터베이스의 트리거는 특정한 테이블에서

특정한 이벤트가 일어날 때 자동으로 실행되는 작업들을 의미합니다.

트리거는 데이터베이스의 테이블에서 데이터 조작(INSERT, DELETE, UPDATE 등)이 수행될 때

트리거에 정의한 동작들을 실행시킵니다.

 

트리거는 데이터의 일관성, 무결성을 유지하기 위한 목적과 자동화의 목적으로 사용할 수 있어요.

예를 들어, 데이터를 삭제하려고 할 때 참조되는 다른 테이블의 레코드도 삭제한다는 등의 작업을 할 수 있겠지요.

많이 작성하면 유용하게 쓰이겠지만, 그만큼 복잡해져서 유지 보수가 어려워질 수 있겠지요?

 

 

---

사용법은 다음과 같습니다. 저는 로컬 DB가 아닌 AWS RDS의 MySQL 기반 인스턴스에서 실습했습니다.

(실습 전에, AWS RDS에서 진행한다면 파라미터 그룹을 수정하고,

이를 인스턴스에 적용하여 트리거를 활성화해야 할 필요가 있습니다. 이 링크를 참고해 주세요. )

 

DROP TABLE IF EXISTS student_history;
DROP TABLE IF EXISTS students;
show variables like '%log_bin_tr%';

create table students (
    student_id INT AUTO_INCREMENT,
    student_name VARCHAR(32),
    enroll_date DATE,
    PRIMARY KEY (student_id)
);

create table student_history (
    history_id INT AUTO_INCREMENT,
    student_id INT,
    action VARCHAR(50),
    action_date DATE,
    PRIMARY KEY (history_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE
);

-- 트리거 생성
CREATE TRIGGER record_student_history
AFTER INSERT ON students
FOR EACH ROW
    INSERT INTO student_history (student_id, action, action_date)
    VALUES (NEW.student_id, 'New Student Added', NEW.enroll_date);
    
-- 삭제 트리거 생성
CREATE TRIGGER record_delete_history
AFTER DELETE ON students
FOR EACH ROW
    INSERT INTO student_history (student_id, action, action_date)
    VALUES (OLD.student_id, 'Student Graduated', CURDATE());
    
-- 학생 데이터 삽입
INSERT INTO students (student_name, enroll_date)
VALUES ('Shawn Kim', '2015-03-01');

INSERT INTO students (student_name, enroll_date)
VALUES ('Max Kang', '2016-03-01');

INSERT INTO students (student_name, enroll_date)
VALUES ('Steve Jo', '2015-03-01');

INSERT INTO students (student_name, enroll_date)
VALUES ('David Chun', '2015-03-01');

DELETE FROM students
WHERE student_id = 3;

-- 변경 사항 조회해보기
SELECT *
FROM students;

select *
from student_history;

 

1~2번째 DROP 문 - 만약 기존에 존재한 테이블이 있다면 실습을 위해 테이블 전체를 날려버렸습니다.

3번째 show variables 문 - DB의 SUPER 권한을 얻기 위한 RDS의 파라미터 변수가 제대로 변경되었는지 확인하는 쿼리입니다. 올바른 결과 값은 ON입니다. 실습에는 딱히 없어도 됩니다.

 

4번째 CREATE 문 - 학생 테이블인 students 테이블을 만들고, 학생의 히스토리를 기록할 student_history 테이블을 만들었습니다.

 

5번째 CREATE TRIGGER 문 - 트리거를 만드는 쿼리입니다. CREATE TRIGGER 다음에는 트리거 이름이 들어갑니다.

그리고 AFTER INSERT ON students로 students 테이블에 insert 명령어가 실행될 시에 발동할 수 있도록 조건을 걸어줬습니다. 비슷하게 AFTER DELETE ON을 걸어준다면, DELETE 명령어가 실행된 다음에 트리거가 발생될 수 있도록 조건을 만들 수 있습니다. FOR EACH ROW는 각각의 행에 대해 트리거를 실행한다는 뜻입니다.

 

AFTER DELETE 트리거에서는 NEW 키워드 대신 OLD 키워드를 사용는데요, 각 키워드를 정리해보자면

• NEW: INSERT나 UPDATE 트리거에서 사용되는 키워드입니다. 새로 추가되거나 업데이트된 행을 참조합니다.
• OLD: DELETE나 UPDATE 트리거에서 사용되는 키워드로, 삭제되거나 업데이트되기 이전의 행을 참조합니다.

예시로, AFTER DELETE 트리거는 students 테이블에서 행이 삭제된 이후에 실행됩니다. 그래서 OLD 키워드를 사용하여 삭제되기 전의 행에 접근하고, 해당 레코드의 값을 참조할 수 있게 됩니다.

 

또한 AFTER가 아닌 BEFORE를 사용하게 된다면 특정 이벤트가 발생하기 전 트리거를 설정할 수도 있습니다.

이후 쿼리는 쭉 검증하는 과정입니다.

실행 결과

 

마지막으로 트리거의 삭제는 DROP을 쓰면 됩니다. DROP은 테이블, 인덱스, 뷰, 트리거 등의 객체를 삭제하는 명령어입니다.

DROP TRIGGER IF EXISTS record_delete_history;

 

---

 

마지막으로 위 방식의 코드가 아니라 아래처럼 DELIMITER와 BEGIN, END를 이용하여 트리거를 작성할 수 있습니다.

DELIMITER //

-- 생성 트리거 생성
CREATE TRIGGER record_student_history
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_history (student_id, action, action_date)
    VALUES (NEW.student_id, 'New Student Added', NEW.enroll_date);
END//
    
-- 삭제 트리거 생성
CREATE TRIGGER record_delete_history
AFTER DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_history (student_id, action, action_date)
    VALUES (OLD.student_id, 'Student Graduated', CURDATE());
END//
DELIMITER ;

 

DELIMITER 명령어로 SQL 구분자 //로 바꿔줬습니다. BEGIN ~ END를 하나의 SQL 구문으로 인식시켜주기 위함입니다.