트리거 실습 (회원 탈퇴 테이블을 이용한 트리거 생성)

트리거는 어떤 이벤트가 발생하면 자동적으로 방아쇠가 당겨져 총알이 발사되듯이
특정 테이블이 변경되면 이를 이벤트로 다른 테이블이 자동으로 변경되도록 하기 위해서 사용된다.

형식 : 
DELIMITER //
CREATE TRIGGER <트리거 이름> <활성화시간> <이벤트> ON <테이블명>
FOR EACH ROW
BEGIN
    실행문 ; 
    : 
    :(생략)
END //
DELIMITER ;

활성화 시간
- 트리거 실행 시점
- BEFORE : 이벤트 발생 전에 실행
- AFTER : 이벤트 발생 후에 실행

이벤트
- 트리거가 실행 될 이벤트 ( INSERT / UPDATE / DELETE )


member 테이블 있는 디비에~!!(naver_db)

use naver_db;

create table trig_test (
event_time char(16) ,
count int unsigned );

insert into trig_test values('INSERT_BEFORE', 0);
insert into trig_test values('INSERT_AFTER', 0);
insert into trig_test values('UPDATE_BEFORE', 0);
insert into trig_test values('UPDATE_AFTER', 0);
insert into trig_test values('DELETE_BEFORE', 0);
insert into trig_test values('DELETE_AFTER', 0);

MariaDB [naver_db]> show tables;
+--------------------+
| Tables_in_naver_db |
+--------------------+
| member             |
| trig_test          |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [naver_db]> 
MariaDB [naver_db]> desc trig_test;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| event_time | char(16)         | YES  |     | NULL    |       |
| count      | int(10) unsigned | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [naver_db]> select * from trig_test;
+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |     0 |
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
6 rows in set (0.00 sec)


트리거 등록

delimiter //
create trigger trig_test AFTER UPDATE On member
for each row
begin
    update trig_test set count = count +1
    where event_time = 'UPDATE_AFTER';

end //

delimiter ; 

MariaDB [naver_db]> show triggers \G
*************************** 1. row ***************************
             Trigger: trig_test
               Event: UPDATE
               Table: member
           Statement: begin
    update trig_test set count = count +1
    where event_time = 'UPDATE_AFTER';

end
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)


MariaDB [naver_db]> select name, sex from member where sex='M';
+-----------+------+
| name      | sex  |
+-----------+------+
| 황영주    | M    |
| 설기형    | M    |
| 박철호    | M    |
| 이상훈    | M    |
| 배용진    | M    |
| 김문수    | M    |
| 차범길    | M    |
| 김길수    | M    |
| 김수련    | M    |
| 이성현    | M    |
+-----------+------+
10 rows in set (0.00 sec)

MariaDB [naver_db]> select * from trig_test;
+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |     0 |
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
6 rows in set (0.00 sec)


MariaDB [naver_db]> update member set sex='A' where sex='M';
Query OK, 10 rows affected (0.03 sec)
Rows matched: 10  Changed: 10  Warnings: 0

MariaDB [naver_db]> select name, sex from member where sex='A';
+-----------+------+
| name      | sex  |
+-----------+------+
| 황영주    | A    |
| 설기형    | A    |
| 박철호    | A    |
| 이상훈    | A    |
| 배용진    | A    |
| 김문수    | A    |
| 차범길    | A    |
| 김길수    | A    |
| 김수련    | A    |
| 이성현    | A    |
+-----------+------+
10 rows in set (0.00 sec)

MariaDB [naver_db]> 
MariaDB [naver_db]> select * from trig_test;
+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |    10 |  <-- 10개의 업데이트가 발생했기 때문에 count 는 0 -> 10으로 변경되었다.
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
6 rows in set (0.00 sec)

---

실습> 회원 탈퇴 테이블을 이용한 트리거 생성하기

-- 회원 탈퇴 테이블 --
1. 회원 탈퇴 테이블 : out_member
create table out_member 
( 
    no int unsigned auto_increment primary key, 
    id varchar(32) not null unique,
    name varchar(32) not null 
);
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| no    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id    | varchar(32)      | NO   | UNI | NULL    |                |
| name  | varchar(32)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2. 트리거 명 : out_mem_trigger 
3. 회원이 탈퇴할 경우 member 테이블에서 삭제하고 회원 탈퇴 테이블(out_member)에 
   최소한의 회원 정보만 저장하도록 설정

- member 테이블 delete 시 out_member 에 id , 이름이 저장된다.
ex) delete from member where id = 'yjhwang';

4. member 테이블

MariaDB [naver_db]> desc member;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| no       | int(11)     | NO   | PRI | NULL    | auto_increment |
| id       | varchar(10) | NO   | UNI | NULL    |                |
| name     | varchar(20) | NO   |     | NULL    |                |
| sex      | char(1)     | YES  |     | NULL    |                |
| post_num | char(8)     | YES  |     | NULL    |                |
| address  | varchar(80) | YES  |     | NULL    |                |
| tel      | varchar(15) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

MariaDB [naver_db]> select no, id, name from member where id='yjhwang';
+----+---------+-----------+
| no | id      | name      |
+----+---------+-----------+
|  1 | yjhwang | 황영주     |
+----+---------+-----------+
1 row in set (0.00 sec)

-- 회원 탈퇴 테이블 --


DELIMITER //
CREATE TRIGGER out_mem_trigger BEFORE DELETE ON member
FOR EACH ROW
BEGIN
    INSERT INTO out_member VALUES('', OLD.id, OLD.name);
END //
DELIMITER ;

MariaDB [naver_db]> show triggers \G
*************************** 1. row ***************************
             Trigger: out_mem_trigger
               Event: DELETE
               Table: member
           Statement: BEGIN
    INSERT INTO out_member VALUES('', OLD.id, OLD.name);
END
              Timing: BEFORE
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

MariaDB [naver_db]> select * from member where name='황영주';
+----+---------+-----------+------+----------+--------------------------------+----------+------+
| no | id      | name      | sex  | post_num | address                        | tel      | age  |
+----+---------+-----------+------+----------+--------------------------------+----------+------+
|  1 | yjhwang | 황영주     | A    | 100-011  | 서울시 중구 충무로1가              | 234-8879 |   35 |
+----+---------+-----------+------+----------+--------------------------------+----------+------+
1 row in set (0.00 sec)

MariaDB [naver_db]> select * from out_member;
Empty set (0.00 sec)

MariaDB [naver_db]> delete from member where name='황영주';
Query OK, 1 row affected (0.02 sec)

MariaDB [naver_db]> select * from member where name='황영주';
Empty set (0.00 sec)

MariaDB [naver_db]> select * from out_member;
+----+---------+-----------+
| no | id      | name      |
+----+---------+-----------+
|  1 | yjhwang | 황영주     |
+----+---------+-----------+
1 row in set (0.00 sec)


MariaDB [naver_db]> select * from member where name='설기형';
+----+--------+-----------+------+----------+-------------------------------+----------+------+
| no | id     | name      | sex  | post_num | address                       | tel      | age  |
+----+--------+-----------+------+----------+-------------------------------+----------+------+
|  2 | khshul | 설기형     | A    | 607-010  | 부산시 동래구 명륜동               | 764-3784 |   33 |
+----+--------+-----------+------+----------+-------------------------------+----------+------+
1 row in set (0.00 sec)

MariaDB [naver_db]> delete from member where name='설기형';
Query OK, 1 row affected (0.02 sec)

MariaDB [naver_db]> select * from member where name='설기형';
Empty set (0.00 sec)

MariaDB [naver_db]> select * from out_member;
+----+---------+-----------+
| no | id      | name      |
+----+---------+-----------+
|  1 | yjhwang | 황영주     |
|  2 | khshul  | 설기형     |
+----+---------+-----------+
2 rows in set (0.00 sec)