[mysql] 데이터 백업을 위한 trigger 만들기

소량의 sub 데이터를 지우고 업데이트 할 시, 이전에 적재했던 데이터를 시점 별로 백업하고 싶다면 업데이트 전 지운 레코드를 다른 테이블에 저장해두는 trigger를 생성해서 작업을 수행할 수 있다.

1. 백업 데이터를 저장할 테이블 만들기

source 테이블과 똑같은 스키마에다가 백업 시점 정보를 담을 칼럼 하나를 date 형식으로 추가하여 백업을 저장할 새 테이블을 하나 생성한다.

 

CREATE TABLE `tb1_bk` (
  `column1` varchar(50) DEFAULT NULL,
  `column2` varchar(50) DEFAULT NULL,
  `column3` varchar(50) DEFAULT NULL,
  `column4` double DEFAULT NULL,
  `delete_date` date )

2. source 테이블에서 deletion이 이루어지면 target 테이블로 지워진 레코드를 저장하도록 하는 Trigger 생성

source 테이블로부터 deletion의 행위가 이루어지면 target 테이블로 지워진 레코드들을 insert 하는 트리거를 생성해준다.

단문 쿼리에 대해서 trigger 문을 생성할 때는 DELIMITER, BEGIN-END 구문이 필요 없다.

insert 하는 value는 mysql에서 제공하는 temp 테이블인 OLD 테이블을 활용하여 target에서 지워진 레코드들의 값을 가져온다.

 

시점 데이터 칼럼은 curdate() 함수를 사용하여 당일 날짜가 (yyyy-mm-dd) 찍히도록 설정해준다.

만약 timestamp까지 필요하다면 적절한 데이터 타입을 변경하여 사용한다.

 

 CREATE TRIGGER `backup_deletion_tb1`
  AFTER DELETE ON `table1`
  FOR EACH ROW
  INSERT INTO `backup_deletion_tb1` (`column1`,`column2`, `column3`, `column4`,`delete_date` )
  VALUES (OLD.`column1`, OLD.`column2`, OLD.`column3`, OLD.`column4`, curdate()); 

 

그러면 아래와 같이 db navigation에 trigger가 생성된 것을 확인 할 수 있다.

 

 

3. 테스트 해보기

트리거가 잘 작동되는지는 안전하게 다음과 같이 테스트 해본다.

1. 테스트용 레코드 한 줄을 source db에 insert 한 후
2. 그 레코드만 삭제하는 쿼리를 실행 후 (delete)
3. target 백업 테이블에 지워진 레코드가 저장되었는지 확인

테스트로 트리거가 정상 작동된다면, 앞으로 source 테이블에서 deletion이 이루어진 레코드들은 시점 정보와 함께 자동으로 target 테이블에 적재되게 된다.