[MYSQL] HeidiSQL로 트리거(Trigger) 부착하기

트리거는 테이블의 삽입, 수정, 삭제 등의 작업이 이루어졌을 경우  자동으로 실행되는 기능이다.

 

트리거에 많이 활용되는 사례는 쇼핑몰의 경우 어떤 상품을 실수로 삭제했다고 가정해보자. 삭제된 데이터는 복구하는 것이 어렵고, 누가 지웠는지 추적하는 것도 쉽지가 않다. 만약 이럴때 트리거를 이용해서 다른 테이블에 기록을 해놓는다면 문제점을 해결할 수 있다. 그러면 고의든 실수든 기록되었던 다른 테이블에서 확인하고 문제를 해결하는데 도움이 될 수 있다.

 

트리거의 종류는 AFTER, BEFORE 종류가 있다.

AFTER트리거는  삽입, 삭제, 수정 작업이 이루어진 후에 작동하는 트리거이고, BEFORE트리거는 AFTER트리거와 반대로 작업이 이루어지기 전에 작동하는 트리거이다.

 

AFTER 트리거 예제

 

먼저 트리거를 적용할 2개의 테이블과 트리거를 테스트할 데이터를 넣는다.

CREATE TABLE `product` (
	`product_code` VARCHAR(10) COMMENT '상품코드',
	`product_name` VARCHAR(50) COMMENT '상품명' 
);

CREATE TABLE `product_log` (
	`product_code` VARCHAR(10) COMMENT '상품코드',
	`product_name` VARCHAR(50) COMMENT '상품명' ,
	`reg_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '삭제일'
);

INSERT INTO product SET product_code='A00001', product_name='침대';
INSERT INTO product SET product_code='A00002', product_name='이불';

 

위의 쿼리 실행 후 HeidiSQL툴에서 product 테이블의 마우스 오른쪽 버튼을 클릭하고 새로생성->트리거를 클릭한다.

 

삭제된 데이터의 로그를 저장하기 위해 AFTER트리거 그리고 뒤에는 DELETE를 선택해줍니다.

(UPDATE를 선택하면 UPDATE시 실행되고, INSERT를 선택하면 INSERT후에 트리거가 실행됨.)

 

참고로 OLD데이터는  DELETE가 수행되기 이전의 데이터를 의미한다.

그리고 상품코드 A00001를 삭제하는 쿼리를 실행해준다.

DELETE FROM product WHERE product_code='A00001';
SELECT * FROM product_log;

그러면 product테이블에서 삭제한 데이터가 자동으로 product_log테이블에 등록된 걸 확인할 수 있다.

reg_date(삭제날짜)는 테이블 생성 당시 디폴트 값으로 CURRENT_TIMESTAMP을 주었기 때문에 서버 시간에 맞게 들어간 걸 확인할 수 있다.

 

 

 

 

BEFORE 트리거 예제

BRFORE트리거는 테이블에 변경이 가해지기 전에 작동되는 트리거이다. BEFORE트리거의 좋은 활용 예시는 INSERT하는 데이터를 미리 확인하여 문제가 있는 데이터라고 판단하는 경우 다른 값으로 변경시킬 수 있다.

 

트리거를 작성할 member 테이블과 데이터를 넣을 쿼리를 실행한다.

CREATE TABLE `member` (
	`name` VARCHAR(10) COMMENT '이름',
	`age` INT(11) COMMENT '나이'
);

그리고 아래에 member 테이블에 before, insert 형식의 트리거를 부착하자.

트리거 조건은 0살 이하의 데이터가 들어갈 경우는 0살, 100살 이상인 데이터가 들어갈 경우 100살 외에는 데이터게 맞게 들어가도록 하였다.

 

그리고 아래의 쿼리문을 수행하고 결과를 실행해보면 데이터 검증에 맞게 들어간 것을 확인할 수 있다.

INSERT into member SET name='바비', age=20;
INSERT into member SET name='뽀로로', age=200;