[Database] MySQL. Trigger 기본 활용
Trigger. 방아쇠라는 명사의 뜻도 있지만 촉발시키다는 동사의 뜻도 있다.
SQL에서 Trigger란 정해진 이벤트에 연이어 곧바로 실행되는 구문을 말한다.
이벤트가 발생했을 때 데이터의 무결성을 지키기 위해 사용된다.
아래는 Trigger에 쓰이는 주요 문법이다.
- 변수선언
declare 변수명 자료형 기본값;
set 변수명 = 값;
new: 이벤트가 발생한 행의 최신 데이터
old: 이벤트가 발생한 행의 이전 데이터
- 생성 전
if not exists [이름] : 이름이 기존에 존재하지 않을 때만 생성함
drop [table/trigger/procedure etc] if exists [이름]: 이름이 기존에 존재할 경우 삭제
delimiter: 문장의 끝을 다른 기호로 표시할 때 사용
- 생성
create trigger [트리거 명] after|before 이벤트 on [테이블명]
for each row
begin
[실행 구문;]
end
delimiter ;
역시나 이론만 봐선 아리송하다. 활용 해보자.
활용을 위해 product와 purchase 테이블을 만들고 product 테이블에 데이터를 임의로 채웠다.
이제 무엇을 할 것인가.
purchase 데이터를 생성 시 product 데이터에 자동반영되게 할 것이다.
purchase_amount에 들어간 값만큼 product 데이터의 amount에서 마이너스, sale_amount에서 플러스가 되게 말이다.
길쭉한 명령문이 될테니 오타에 의한 스트레스를 줄이기 위해 이번엔 커맨드라인말고 워크벤치를 사용하여 트리거를 생성해준다.
15줄 실화냐..
한줄씩 뜯어보면 크게 어려울 것 없다.
아래는 1번 줄부터 라인 하나씩 해석한 것이다;
1) purchase 테이블 전체 데이터 출력(기존 확인용)
2) 기존에 insert_purchase라는 트리거가 존재할 시 삭제(기존에 해당 이름의 트리거가 있을 시 같은 이름으로 생성이 불가하니 미리 삭제해주는 것)
3) 트리거 정의/생성에 필요한 구문 문자들을 사용하기 위해 delimiter를 써주고
4) insert_purchase라는 트리거를.. purchase라는 테이블에 데이터가 삽입된 이후 각 줄을 위해 생성한다
5) 트리거의 내용 시작
6) _amount라는 변수를 int로 선언한다.
7) purchase 테이블에 삽입되는 데이터 중 purchase_amount의 값을 _amount에 대입한다.
8) product 테이블을 업데이트할건데 그 내용은...
9) amount는 기존 amount에서 _amount를 뺀 것이고,
10) sale_amount는 기존 sale_amount에서 _amount를 더한 것이다.
11) 이 때, 어떤 아이템의 amount와 sale_amount나면, 삽입되는 데이터의 product_name과 같은 name을 가지는 아이템의 것들이다.
12) 트리거 내용은 여기까지.
13) 구문자들을 트리거 내에서 다 사용했으니 정상 사용을 되돌리기 위해 delimiter를 써줌
2~13 라인이 트리거 생성이고 15, 16 라인은 테스트를 위해 넣어준 쿼리다.
결과물을 보자.
purchase 데이터 생성에 따라 product 테이블에도 그것이 반영된 것을 볼 수 있다.