[MySQL] 자동으로 실행되는 트리거 (Trigger) 에 대해 알아보자

트리거 (Trigger)

• 데이터의 무결성을 위한 기능이다.
• 테이블에 INSERT, UPDATE, DELETE 작업이 발생하면 실행되는 코드 (DML 작업)
• 예시
  • 퇴사자의 정보를 DELETE 하려고 할 때 다른 별도의 장소에 자동으로 저장해둔다.

 

- 트리거 이해를 위한 테이블 생성 및 트리거 부착하기

// 테이블 생성

USE market_db;
CREATE TABLE IF NOT EXISTS trigger_table (id INT, txt VARCHAR(10));
INSERT INTO trigger_table VALUES(1, '레드벨벳');
INSERT INTO trigger_table VALUES(2, '잇지');
INSERT INTO trigger_table VALUES(3, '블랙핑크');

// 테이블에 트리거 부착(attach)하기

DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$
CREATE TRIGGER myTrigger;
	AFTER DELETE // DELETE문이 발생한 이후에 트리거가 작동하라는 의
	ON trigger_table
	FOR EACH ROW // 트리거를 각 행마다 적용
BEGIN
	SET @msg = '가수 그룹이 삭제됨' ; // 트리거에서 실제 작동할 부분
END $$
DELIMITER ;

// 테이블에 DELETE문 적용
DELETE FROM trigger_table WHERE id = 2;
SELECT @msg;

 

 

 

트리거의 활용

트리거는 테이블에 INSERT, UPDATE, DELETE 되는 정보를 백업하는 용도로 사용할 수 있다.

 

 

- member 테이블의 회원 정보 변경 시, 변경한 사용자, 시간, 변경 전 데이터를 기록하는 트리거 작성

// 트리거 실습에 사용할 테이블 생성
// CREATE TABLE ~ (SELECT ~) 문을 이용해
// 기존의 테이블을 복사해서 새로운 테이블 생성 
USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

// 백업 테이블 생성
CREATE TABLE backup_singer
( mem_id CHAR(8) NOT NULL,
  mem_name VARCHAR(10) NOT NULL,
  mem_number INT NOT NULL,
  addr CHAR(2) NOT NULL,
  modType CHAR(2),  // 변경된 타입 (데이터가 수정된 것인지 삭제된 것인지)
  modDate DATE,  // 데이터 변경 날짜
  modUser VARCHAR(30) // 데이터를 변경한 사용자
);

// 변경(UPDATE) 이벤트 발생 시 작동하는 트리거 생성
DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg
	AFTER UPDATE
	ON singer
	FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number,
																		OLD.addr, '수정', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

→ OLD 테이블 : UPDATE나 DELETE가 수행될 때 변경 전 데이터가 잠깐 저장되는 임시 테이블

→ OLD 테이블에 UPDATE 문 작동 시 업데이트 전의 데이터가 백업 테이블에 입력

→ 원래 데이터 보존 가능

 

// 삭제(DELETE) 이벤트 발생 시 작동하는 트리거 생성
DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg
	AFTER DELETE
	ON singer
	FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUES ( OLD.mem_id, OLD.mem_name, OLD.mem_number,
																		 OLD.addr, '삭제', CURDATE(), CURRENT_USER() );
END $$
DELIMITER ;

// 데이터 변경하기
UPDATE singer SET addr = '영국', WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;

// 수정, 삭제된 내용을 백업 테이블로 조회하기
SELECT * FROM backup_singer;

 

TRUNCATE 구문은 DELETE 트리거를 실행시키지 못한다