MySQL/혼공SQL

20강. 자동으로 실행되는 트리거(trigger)의 개념과 트리거를 활용하여 데이터 백업하는 방법

묘걍 2023. 12. 13. 15:41

07-3. 자동으로 실행되는 트리거

👉🏻 트리거

출처: 혼공SQL 유튜브

  • 트리거 = 방아쇠
  • 방아쇠를 당기면 자동으로 총알이 튀어나가는 권총처럼
  • 방아쇠에 해당하는 것은 INSERT, UPDATE, DELETE문
    • 위의 것이 실행되면 방아쇠가 당겨져서 어떤 쿼리가 자동으로 실행되는 것
    • SELECT는 해당되지 않는다

✅ 만약 블랙핑크가 탈퇴하면

회원 테이블에서 DELETE로 삭제하면 된다. 그런데 만약 탈퇴 뒤에 블랙핑크가 자신의 회원 기록을 요청하면 이미 삭제해서 데이터가 남아있지 않다 이건 위험한 것

삭제 전에 다른 곳에 저장 (탈퇴 회원 테이블)하는 방법이 있다

하지만 사람이 일하다보면 삭제 전에 탈퇴회원 테이블에 저장하지 않고 바로 삭제해버리는 실수를 저지를 수 있다. 어떤 탈퇴 회원은 탈퇴 회원 테이블에 들어있고, 어떤 탈퇴 회원은 안 들어있게 되는 것이다. 이게 진짜 안 좋은 것이다. 데이터의 무결성 해치기 때문이다 (결함이 있는 데이터)

이걸 자동으로 들어가게하면 안전하고 편하다

🧩 트리거의 기본 작동

✅ 이벤트

  • 트리거가 작동하는 방아쇠 역할
  • INSERT EVENT, DELETE EVENT, UPDATE EVENT
  • 이걸 모두 다 하는 게 아니라 필요한 걸 골라서

- 트리거는 테이블에 부착한다고 한다

- INSERT가 날라가면 자동으로 부착된 트리거가 실행되는 것

출처: 혼공SQL 유튜브

  • 테이블에 트리거를 만들어 부착해 놓는 것
  • 부착을 해놓기만 하면 해당 문이 DML문이 들어왔을 때 자동으로 트리거가 작동한다
  • 스토어드 프로시저와 개념은 비슷하지만 매개변수 개념이 없다
  • 따로 실행은 못한다.

🎮 DELETE 트리거 만들기

- ID와 이름만 있는 간단한 테이블 만들기

- 데이터 넣기

- 트리거 만들기

- 만약 이미 있다면 삭제 아니면 넘어가

- 저장 프로시저 형식을 그대로 따른다 (DELIMITER와 BEGIN, END)

- CREATE TRIGGER 트리거 이름은 내가 지정

- AFTER 다음에 어떤 문이 날라오면 트리거가 작동할지 DML문을 설정

     - 여기서는 DELETE

- ON 테이블 이름, 어떤 테이블에 트리거를 부착할지

- FOR EACH ROW는 무조건

- 트리거는  BEGIN ~ END 사이 내용

     - 변수에 문장을 저장

- 일단 변수에 빈 문자열을 넣는다

- INSERT하고 SELECT해보면

- 아무것도 없다.

- 트리거가 작동하지 않았다

- DELETE 트리거이기 때문에 INSERT에는 작동하지 않는다

- UPDATE에도 역시 작동하지 않는다

- DELETE에는 트리거가 작동함

 

👉🏻 트리거 활용

✅ 은행 창구

은행 창구에서 새로운 계좌를 만들면 INSERT, 계좌 입출금을 하면 UPDATE, 계좌를 폐기하면 DELETE

은행에 있는 계좌는 중요한 정보라서 실수를 하면 큰일난다. 만약 UPDATE나 DELETE가 일어나면 언제 일어난건지 기록을 남기는 게 좋다. 이것을 트리거로 작동되게 할 것

누가 언제 어떤 작업을 했는지 기록을 남기는 작업

🎮 고객 테이블 UPDATE, DELETE에 대한 백업

- 새 테이블 만들기

- 기본 테이블

- 아이디, 이름, 번호, 주소를 백업하고 누가, 언제, 무엇을 했는지 담기도록

- 이게 백업 테이블

- 트리거는 singer 테이블에 만듦

- 이름은 내가 지어줌

- UPDATE가 일어나면 singer 테이블에서 트리거가 작동

- 아이디, 이름, 번호, 주소가 백업에 들어가고

- 변경 타입은 수정, 변경 날짜는 현재 날짜, 변경 사용자는 현재 사용자

     - 지금은 root로 사용중이라 root로 나올 것

- 해당 내용을 backup_singer에 넣는다

✅ OLD

  • 시스템 테이블
  • MySQL이 갖고 있는 이름 (내가 지은 게 아님)
  • 수정/삭제 되기 직전의 데이터값이 OLD라는 테이블에 잠깐 들어감
  • 변경 전의 내용이 백업되어야하기 때문에 그 내용이 잠시 OLD에 들어갔다가 지정한 테이블 (여기선 백업 테이블)로 들어가는 것

 

- 수정 대신 삭제로 바뀐 것 말고는 똑같음

 

* 한 테이블에 트리거를 여러개 부착시켜도 된다

 

- 블랙핑크의 주소를 영국으로 업데이트

-  멤버 수가 7명 이상인 그룹을 삭제

- 블랙 핑크는 경남으로 되어 있음 (수정 전의 내용이 나와야 하기 때문에)

- 7명 이상인 멤버들이 삭제되느라 백업 테이블에 들어와있음

* 수정 후 SINGER 테이블과 비교

✅ TRUNCATE

  • 데이터 지우기

- singer 테이블 내의 모든 데이터 삭제

- 백업 테이블 확인해보면

- 삭제 된게 들어오지 않음

- 트리거는 DELETE에 대해서만 작동되는것이기 때문에 TRUNCATE에 대해서는 작동하지 않는다

 

 

 

 

 

출처: https://youtu.be/bggWVsBmKag?si=goDxwFIDUyT4c97Q