스토어드 프로시저 3 - 자동 실행되는 트리거

10월 말에 공부하다 말았던 SQL 마지막 장을 공부해보려고 한다. 그때 스토어드 프로시저를 공부하다 너무 어려워서 세번째 장과 마지막 단원 학습을 하지 않았다.

저녁을 먹고 나니 집중이 잘 되지 않고 공부하기 싫은 마음이 크지만 그래도 하는 데까지 해볼 것이다. 오늘은 전처럼 다시 일찍 취침하고 새벽에 공부하는 습관을 들이려고 한다. 요 며칠 밤 늦게까지 잠을 자지 않아 새벽에 일어나지 못했다.

 

트리거는 INSERT, UPDATE, DELETE 문이 작동할 때 자동으로 실행되는 프로그래밍 기능이다. 예를 들어 트리거를 활용하면 데이터가 삭제될 때 해당 데이터를 다른 곳에 자동으로 백업할 수 있다.

 

시작하기 전에

트리거는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다. 예를 들어 회사원이 퇴사하면 직원 테이블에서 삭제하면 된다. 이런 작업을 수동으로 할 경우 백업하지 않고 데이터를 삭제할 수 있다.

트리거는 이런 실수를 방지할 수 있다. 직원 테이블에서 사원을 삭제하면 해당 데이터를 자동으로 퇴사자 테이블에 들어가도록 설정할 수 있다. 즉, 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있다. 이런 것을 데이터의 무결성이라고 부른다.

---

 

 

 

 

 

 

트리거의 기본

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

 

트리거의 개요

트리거란 테이블에 INSERT나 UPDATE 또는 DELDETE 작업이 발생하면 실행되는 코드이다. 예를 들어, market_db의 회원 중 '블랙핑크'가 탈퇴하는 경우를 생각해보자. 블랙핑크의 행 데이터를 DELETE로 지우면 나중에 탈퇴한 사람의 정보를 어떻게 알 수 있을까? 이를 방지하는 방법은 블랙핑크의 행을 삭제하기 전에 그 내용을 다른 곳에 복사해 놓으면 된다.

이 작업을 자동으로 저장해주는 기능이 있다면 어떨까? 모든 것이 자동으로 처리되므로 삭제된 모든 사용자 정보는 완벽하게 별도의 장소에 저장된다. 이것이 트리거의 대표적인 용도이다.

 

 

트리거의 기본 작동

트리거는 테이블에서 DML 문(INSERT, UDATE, DELETE 등)의 이벤트가 발생할 때 작동한다. 테이블에 미리 부착되는 프로그램 코드라고 생각하면 된다. 

 

트리거는 스토어드 프로시저와 문법이 비슷하지만 CALL 문으로 직접 실행할 수는 없고 오직 테이블에 INSER, UPDATE, DELETE 등의 이벤트가 발생할 경우에만 자동으로 실행된다. 또한 스토어드 프로시저와 달리 트리거에는 IN, OUT 매개변수를 사용할 수 없다.

우선 간단한 테이블을 만들어보겠다.

 

 

 

이제 테이블에 트리거를 부착해보겠다.

 

AFTER DELETE는 이 트리거에서 DELDETE 문이 발생된 이후에 작동하라는 의미이다. 이제 트리거를 부착한 테이블에 값을 삽입하고 수정해보겠다.

 

 

 

결과를 보면 아무것도 나오지 않았다. @msg 변수에 빈 문자를 넣고 INSERT 문을 실행했다. trigger_table에는 DELETE에만 작동하는 트리거를 부착해 놓았다. 그러므로 트리거가 작동하지 않아 빈 @msg가 그대로 출력된 것이다.

 

 

이제 DELETE 문을 테이블에 적용해보겠다. 예상대로 DELETE 문을 실행하니, 트리거가 작동해서 @msg 변수에 트리거에서 설정한 내용이 입력된 것을 확인할 수 있다.

 

 

이렇게 트리거는 테이블에 부착해서 사용할 수 있다. 이 예제에서는 간단히 @msg에 값을 대입하는 내용만 코딩했지만, 그 부분을 실제로 필요로 하는 복잡한 SQL 문들로 대치하면 유용한 트리거로 작동할 것이다.

---

 

 

 

 

 

트리거 활용

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

다음과 같은 사례를 생각해보겠다. 은행의 창구에서 새로 계좌를 만들 때는 INSERT를 사용한다. 계좌에 입금하거나 출금하면 UPDATE를 사용해서 값을 변경하며, 계좌를 폐기하면 DELETE가 작동한다.

 

그런데 계좌라는 중요한 정보를 누가 입력/수정/삭제했는지 알 수 없다면 나중에 계좌에 문제가 발생했을 때 원인을 파악할 수 없을 것이다. 이럴 때를 대비해서 트리거를 자동으로 작동시켜 데이터를 변경한 사용자와 시간 등을 기록할 수 있다.

 

이런 개념을 적용해서 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거를 작성해보겠다.

 

 

하지만 이런 오류가 발생하고 있다. 

 

가수 테이블에 INSERT나 UPDATE 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 백업 테이블을 미리 생성해놓았다. 백업 테이블에는 추가로 수정 또는 삭제인지 구분할 변경된 타입, 변경된 날짜, 변경한 사용자를 추가했다.

 

 

이 코드에서도 역시 오류가 발생한다. 여기서 문제를 해결하고 넘어가야 할 것 같다.

---

 

 

문제 1 : "Table 'singer' already exists"

• 원인: singer 테이블이 이미 데이터베이스에 존재하기 때문에 다시 생성하려고 하면 오류가 발생.
• 해결 방법: 테이블이 이미 존재한다면, 필요에 따라 삭제하거나 중복으로 생성하지 않도록 해야 해.

---

 

 

 

문제 2 : "Syntax error in CREATE TABLE backup_singer"

• 원인: CREATE TABLE 구문에 있는 코멘트(--) 사용 또는 구문 오류가 발생했을 가능성.
• 해결 방법: 코멘트를 제거하거나 구문을 올바르게 작성.

 

이제 오류가 뜨지 않고 잘 된다. 본격적으로 변경과 삭제가 발생할 때 작동하는 트리거를 singer 테이블에 부착하겠다. 먼저 변경이 발생했을 때 작동하는 singer_updateTrg 트리거를 만들 것이다.

 

 

 

 

OLD 테이블은 UPDATE나 DELETE가 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블이다. OLD 테이블에 UPDATE 문이 작동되면 이 행에 의해서 업데이트되기 전의 데이터가 백업 테이블에 입력된다. 즉 원래 데이터가 보존된다.

CURDATE()는 현재 날짜를, CURRENT_USER()는 현재 작업 중인 사용자를 알려준다.

 

 

이번에는 삭제가 발생했을 때 작동하는 singer_deleteTrg 트리거를 생성하겠다.

 

 

singer_updateTrg와의 차이점은 DELETE 트리거로 지정한 것과 변경된 타입을 '삭제'로 입력한 것뿐이다. 이제 데이터를 변경해보겠다. 한 건의 데이터를 업데이트하고, 여러 건을 삭제해본다.

 

 

백업 테이블을 조회해보겠다. 1건이 수정되고 4건이 삭제된 것을 확인할 수 있다. 수정 또는 삭제 전의 데이터가 백업 테이블에 잘 보관되어 있다.

 

 

 

이번에는 테이블의 모든 행 데이터를 삭제해보겠다. DELETE 대신에 TRUNCATE TABLE 문으로 삭제해본다.

 

 

 

삭제가 잘 되었는지 백업 테이블을 다시 확인해본다. 백업 테이블에 삭제된 내용이 들어가지 않는다. 이유는 TRUNCATE TABLE로 삭제 시에는 트리거가 작동하지 않기 때문이다. DELETE 트리거는 오직 DELETE 문에서만 작동한다.

---

 

 

 

좀더 알아보기 - 트리거가 사용하는 임시 테이블

테이블에 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블이 2개 있는데 ,이름은 NEW와 OLD이다. 두 테이블은 사용자가 만드는 것이 아니고 MySQL이 알아서 생성하고 관리하므로 신경 쓸 필요는 없다.

 

먼저 NEW 테이블은 INSERT 문이 실행되면 다음 그림과 같이 작동한다.

 

지금까지 배운 바로는 INSERT(새 값) 형태로 테이블에 새 값이 바로 들어간다. 하지만 사실 새 값은 테이블에 들어가기 전에 NEW 테이블에 잠깐 들어가 있다.

 

 

 

이번에는 DELETE(예전 값)의 작동을 살펴보겠다. OLD 테이블은 DELETE 문이 실행되면 다음 그림과 같이 작동한다.

 

삭제될 예전 값이 삭제되기 전에 OLD 테이블에 잠깐 들어가 있다. 그래서 AFTER DELETE 트리거를 만들어도 삭제된 후에 OLD.열 이름 형식으로 예전 값에 접근할 수 있었던 것이다.

 

 

마지막으로 UPDATE(새값, 예전 값)을 사용하면 다음 그림과 같이 NEW 테이블과 OLD 테이블을 모두 사용한다.

---

 

 

 

 

단원 마무리 및 확인 문제 풀기

 

 

 

 

 

 

TRUNCATE는 트리거를 작동시키지 못한다.

---

 

 

 

 

학습을 마치고

1시간만에 마칠 수 있을 줄 공부는 1시간 반이나 걸렸지만 그래도 꼼꼼이 학습하면서 정말 많은 것들을 배울 수 있었다. 중간에 코드 오류도 수정했고 임시 테이블에 대해서도 공부했다.

이제 8단원 학습만이 남아있다. 파이썬과 연결하는 건데 이 공부는 다른 학습에 비해 더 재미있을 것 같다. 조금 전에는 그토록 지루하게 느껴졌던 공부가 지금은 할만해졌다.