MySQL 트리거

트리거란 테이블에 어떠한 일이 일어나면 자동으로 실행되는 프로그래밍 기능을 말한다.

 

트리거의 개요

테이블에 insert, update, delete 작업이 발생하면 실행되는 코드이다.

ex) market_db 의 회원중 '블랙핑크'가 탈퇴한다면 member 테이블에서 블랙핑크의 정보를 삭제하면 된다.

하지만 나중에 탈퇴한 사람의 정보를 알아야 한다면 데이터베이스에서 삭제했기 때문에 알 수 있는 방법이 없다.

이를 방지하기 위해서 블랙핑크의 행이 삭제되기 전에 그 내용을 다른곳에 복사하면 된다.

이런 방법을 매번 수작업으로 하면 데이터를 복사하는 것을 잊어버리거나 잘못 입력할 가능성이 있다.

따라서 member에서 delete작업이 일어날 경우에 데이터가 삭제되기 전에 다른 곳에 자동으로 저장해주는 기능 구현에 사용되는 것이 트리거이다.

 

트리거의 기본 작동

트리거는 insert, update, delete 등의 dml(Data Manipulation Language) 이벤트가 발생할 때 작동한다.

테이블에 미리 부착되어있는 프로그램 코드라고 볼 수 있다.

 

스토어드 프로시저와 차이점

트리거는 call로 직접 실행시킬 수 없고 오직 테이블에 이벤트가 발생할 경우에만 자동으로 실행된다.

스토어드 프로시저와는 다르게 트리거에는 in, out 매개변수를 사용할 수 없다.

 

트리거의 활용

트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있다.

ex) 은행에서 계좌를 만드는 것은 insert, 입금이나 출금은 update, 폐기는 delete가 작동한다고 가정할 때 누가 계좌를

생성/수정/삭제 했는지를 알 수 없다면 계좌에 문제가 발생했을 때 원인을 파악하기 힘들다.

따라서 이런 상황을 대비하기 위해 데이터에 입력/수정/삭제가 발생할 때 트리거를 자동으로 작동시켜 데이터를 변경한 사용자와 시간등을 기록할 수 있다.

 

market_db 의 singer 테이블에 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하기

 

테이블을 복사하여 새로운 테이블 생성하기

create table singer (select mem_id, mem_name, mem_number, addr from member);

 

가수 테이블에 delete나 update 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 백업 테이블 생성하기

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나 delete가 발생할 때 작동하는 트리거를 singer 테이블에 부착하기

수정 트리거 작성하기

drop trigger if exists singer_updateTrg;
delimiter $$
create trigger singer_updateTrg -- 트리거 이름
	after update -- 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가 수행될 때 변경되기 전의 데이터가 잠깐 저장되는 임시테이블이다.

테이블에 update문이 작동되면 원래의 데이터가 백업테이블에 입력된다.

 

삭제 트리거 작성하기

drop trigger if exists singer_deleteTrg;
delimiter $$
create trigger singer_deleteTrg -- 트리거 이름
	after delete -- 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 singer;

select * from backup_singer;

데이터의 수정과 삭제가 일어나면 

singer에서 BLK 라는 이름을 가진 열은 addr이 영국으로 변경되고 mem_number가 7과 같거나 많은 열들은 사라지고 나머지만 남아있을 것이다.

 

테이블 초기화 테스트

truncate table singer;

select * from singer;

select * from backup_singer;

truncate table은 테이블을 초기화 하는 방식인데 turncate table로 테이블을 초기화 하면 트리거는 작동하지 않는다.

delete 트리거는 오직 delete문에서만 작동하기 때문이다.

 

트리거가 사용하는 임시 테이블

테이블에 insert, update, delete 작업이 수행되면 임시로 사용하는 시스템 테이블이 총 2개가 있다.(new ,old)

임시 테이블은 MySQL이 알아서 생성하고 관리하므로 신경 쓸 필요는 없다.

 

insert 문이 실행되면 new 테이블에 새 값이 들어갔다가 데이터에 이상이 없으면 new 테이블에서 목표 테이블로 이동이 된다.

 

delete 문이 실행되면 목표 테이블에서 old 테이블로 예전 값을 잠깐 옮겨둔다. 따라서 after delete 트리거로 삭제된 후에 old.열 이름 형식으로 예전 값에 접근할 수 있다.

 

update 문을 사용하면 새 값을 new 테이블에 새 값이 들어갔다가 데이터에 이상이 없으면 목표 테이블로 옮기고 목표 테이블에 있던 예전 값은 old 테이블로 옮겨둔다.