Php로 웹 개발하기 : MySQL Trigger

SQL에서는 특정 데이터베이스에 이벤트가 일어났을 때 특정 Action을 취하도록 하는 Trigger를 만들 수 있다.

이번 실습에서는 회원가입으로 새로운 아이디 / 비밀번호가 생성되면, 비밀번호를 자동으로 Hashing 하여 저장하는 Trigger를 만들어보자. (앞부분은 시행착오가 포함되어 있다.)

 

우선 Trigger를 만드는 형태부터 살펴보자.

mysql> CREATE TRIGGER trigger_name trigger_time trigger_event ON tbname
    -> FOR EACH ROW
    ->     trigger_action

특정 테이블(tbname)에 어떤 이벤트 (trigger_event : insert, delete, update)가 발생하는 시기(trigger_time : before, after)에 따라 액션을 취해라(trigger_action)라는 뜻이다.

 

Trigger를 만들기 전에, delimiter에 대해 잠깐 알아보자.

SQL에서는 Delimiter로 statement의 terminator를 바꿀 수 있는데, 뜻 자체도 '구획 문자'로 SQL에서는 문장 끝에 세미콜론(;)을 붙여 문장이 끝났음을 표시하므로 SQL의 Delimiter는 ';'이다.

 

Php에서 MySQL을 연동해서 SQL 구문을 실행할 때는 다음과 같이 작성할 수 있는데

$sql = "SELECT * from tbname;";

쌍따옴표가 SQL의 구문의 Delimiter(;)와 Php의 Delimiter(;)를 구분해준다.

 

그런데 MySQL안에서 Trigger를 만들 때는 Trigger내의 Delimiter(;)와 Trigger가 끝났음을 표시하는 Delimiter(;)의 구분이 힘들다. 그럴 때 Delimiter를 사용할 수 있다.

mysql> delimiter //
mysql> create trigger set_hashpw after insert on tbname
    -> for each row
    -> begin
    -> if new.login_pw is not null then
    ->     update tbname
    ->     set tbname.hash_pw = sha2(tbname.login_pw, 256)
    ->     where tbname.hash_pw = new.login_pw;
    -> end if;
	-> end //
mysql> delimiter ;

맨 위와 맨 밑줄은 delimiter를 //로 바꾸고 다시 ;로 돌려놓는다는 의미이다.

 

우리가 만들고 싶은 Trigger는 데이터베이스에 ID와 PW 값이 들어오면, hash_pw값을 update 하는 것이므로 after insert on을 사용하면 된다.

 

*Error*

① 만약 "You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)"라는 에러가 발생한다면, root 계정으로 접속해주면 된다.

② 그런데 Trigger를 만든 후 tbname에 해당하는 테이블에 값을 추가해보면 "Can't update table 'login' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."라는 Error가 뜬다.

 

Trigger에 대해 다시 알아보니, Table1에 Event가 발생했을 때 Table 2의 내용을 바꾸어야 유효한 사용 방식이었다.

아마도 Table1에서 insert 일어남 → Table1이 update 됨 = Table1에 insert가 일어난 셈 → table1이 update 됨 → 무한 루프라서 그런 것 같다.

 

그래서 그냥 Php 내부 코드로 회원가입 시에 hasing 하여 pw를 따로 넣어줄까 하다가 기왕 trigger를 공부한 김에 trigger로 만들고자 Table을 하나 더 만들어주었다. (원래 보안을 신경 쓰는 곳에서는 비밀번호가 아니라 Hasing 된 비밀번호를 저장해놓는다고 하니 안전한 DB 버전을 만드는 셈 치고)

 

우선 만들어주었던 Trigger를 삭제하자.

mysql> DROP trigger set_hashpw;

 

기존 테이블(tbname1)에서 hash_pw column을 삭제하고 id와 pw만 가진 테이블로 만들고, 새로운 테이블(tbname2)에 id와 hashing 된 pw를 넣어주자.

mysql> ALTER TABLE `tbname1` DROP `hash_pw`;

mysql> CREATE table `tbname2` (
    ->    `login_id` varchar(20) NOT NULL,
    ->    `hash_pw` varchar(256) NOT NULL,
    ->    PRIMARY KEY(login_id)
    ->);

테이블을 만들어 줄 때 따옴표('')가 아닌 백 틱(``)을 쓰니 Error 없이 생성되었다.

다음과 같은 모습이 되면 성공!

다시 Trigger를 만들어주자.

mysql> delimiter //
mysql> CREATE TRIGGER trigger_name after insert on tbname1
    -> for each row
    -> begin
    -> insert into tbname2 values(new.login_id, sha2(new.login_pw, 256));
    -> end //
mysql> delimiter ;

tbname1 테이블에 insert가 일어나면 tbname2에 [new(새로 들어온 데이터)의 login_id]와 [new(새로 들어온 데이터)의 login_pw를 SHA256으로 Hashing 한 값]을 넣어준다.

tbname1에만 새로운 값을 넣어본 후 tbname2에 잘 들어가나 확인을 해주었다.

웹 사이트에서 회원가입으로 넣어도 잘 되나 확인해보자.

테이블이 많아지고 서로 관련되는 부분이 많아지면 Trigger가 유용하게 사용될 것이다.