[MySQL_MariaDB] 트리거

* 트리거(방아쇠)

> 트리거는 사전적 의미로 방아쇠를 뜻한다. 방아쇠를 당기면 총알이 나가듯 테이블에 무슨일이 발생하면 자동으로 실행된다.

> 테이블에서 insert나 update 또는 delete 작업이 발생되면 실행되는 코드

> 누군가 고의 혹은 실수로 테이블을 삭제한다면 누가 지웠는지 추적하는 일이 쉽지 않다. 이럴 때 트리거를 걸어놓는다면 이러한 문제를 해결할 수 있다.

> 만약에 회원 중 당탕이가 회원탈퇴를 했다. 그런데 나중에 회원 탈퇴한 사람이 누구누구인지 알 방법은 없을까?

- 데이터를 삭제하기 전에 다른 곳에 저장하면된다. 깜빡 잊을 수도 있다.

> 위와 같은 경우에 삭제되기 전에 미리 다른 곳에 저장해주는 기능이 있다면 실수가 없을 것이다. 이것이 트리거의 대표적인 사용용도다.

> 다른 일부 DBMS는 View에도 트리거를 걸 수 있지만, MySQL은 불가능하다.

 

10. AFTER 트리거 : 테이블에 insert, update, delete 등의 작업일 일어났을 때 작동하는 트리거를 말하며, 해당 작업 후에 작동한다.

1~6. 백업 테이블을 생성

8, 17. DELIMITER : 구분문자. 뒤에 //가 나오면 기존의 세미콜론(;)을 //로 대신한다. 이는 CREATE PROCEDURE ~~ END까지를 하나의 단락으로 묶어주는 효과를 갖는다. 그리고 제일 마지막 행에서 세미콜론(;)으로 돌려놓아야 한다.

15. OLD : 기존(트리거를 부착한 테이블)

1) NEW : 신규(백업테이블)

* 위처럼 하면 회원테이블을 삭제하면 백업테이블에 삭제한 행이 쌓이는 것을 알 수 있다.

 

1. NEW테이블 : insert, update 작업 시 변경할 새로운 데이터를 잠깐 저장 후 테이블에 입력/변경한다. 그러므로 NEW테이블을 조작하면 입력되는 새로운 값을 다른 값으로 대치할 수 있음.

2. OLD테이블 : delete, update 작업이 수행되면서 삭제 또는 변경되기 전의 예전 값이 잠깐 저장된다. 예전 데이터를 참조하기 위해서는 OLD 테이블을 참조하면 된다.

 

필요없는 테이블 날리고 테이블 생성

 

수정, 삭제 후 트리거

 

Truncate table로 삭제시에는 트리거가 발동하지 않는다. Delete 트리거는 delete문에만 작동한다.

 

8. signal sqlstate ‘45000’ : 사용자가 오류를 강제로 발생시키는 함수. 이 구문에 의해 사용자가 정의한 오류 메시지가 출력되고, 사용자가 시도한 INSERT는 롤백이 되어 테이블에 적용되지 않는다.

 

* BEFORE 트리거

9~13. 입력되는 값이 들어 있는 NEW 테이블의 값을 검사해서 1900미만인 경우 아예0으로, 현재 연도보다 초과라면 현재연도로 바꾼다.

 

생성된 트리거 확인

 

 

* 다중트리거

: 하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것. 예로 AFTER INSERT 트리거가 한 개 테이블에 2개 이상 부착되어 있을 수도 있다.

 

* 중첩트리거

> 트리거가 또 다른 트리거를 작동하는 것.

> 물건을 구매하면, 물품테이블에서 남은 개수를 감소시키고, 배송 테이블 건수 입력

* MySQL은 현재 중첩트리거는 지원하지만 재귀트리거는 지원하지 않는다. 다른 DBMS는 재귀 트리거를 지원하기도 한다.

 

중첩트리거를 테스트해보기 위한 테이블 생성. 13은 물품테이블

 

3~12. 구매테이블에 구매가 발생하면 물품테이블에 주문한 개수를 빼고

16~28. 물품테이블에 수정이 발생하면 배송테이블에 배송할 물건과 개수를 입력

 

위처럼 중첩트리거가 모두 작동한 것을 확인할 수 있다. Insert 및 중첩트리거 중 하나라도 에러가 발생하면 롤백된다.