[mysql] trigger

trigger

- insert, update, delete 발생시 이에 따른 후속 행위를 자동 수행

- 아래 예제 : membertbl에서 데이터 하나 삭제시 삭제된 데이터의 저장 테이블(deletedmembertbl)로 자동 insert되고 일시표시

현재 membertbl 데이터

mysql> select * from membertbl;

+----------+------------+-----------------+
| memberid | membername | memberaddress   |
+----------+------------+-----------------+
| Dang     | DangTangYi | Bucheon, Korea  |
| Figure   | Yeona      | GangNam, Korea  |
| Han      | JuYeon     | Incheon, Korea  |
| Jee      | JiWoon     | Seoul, Korea    |
| Sang     | SangGil    | SeongNam, Korea |
+----------+------------+-----------------+
5 rows in set (0.00 sec)

타입확인

mysql> desc membertbl;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| memberid      | char(20) | NO   | PRI | NULL    |       |
| membername    | char(20) | NO   |     | NULL    |       |
| memberaddress | char(20) | YES  |     | NULL    |       |
+---------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

삭제된 데이터 저장 테이블 생성

mysql> create table deletedmembertbl (
    -> memberid char(20),
    -> membername char(20),
    -> memberaddress char(20),
    -> deletedata datetime);
Query OK, 0 rows affected (0.01 sec)

확인

mysql> show tables;
+------------------+
| Tables_in_shopdb |
+------------------+
| deletedmembertbl |
| indextbl         |
| membertbl        |
| producttbl       |
| uv_membertbl     |
+------------------+
5 rows in set (0.00 sec)

트리거 생성

mysql> delimiter //
mysql> create trigger trg_deletedmembertbl
    -> after delete
    -> on membertbl
    -> for each row
    -> begin
    ->     insert into deletedmembertbl values (
    ->     old.memberid, old.membername, old.memberaddress, now());
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

Dang을 삭제해보자
mysql> select * from membertbl;
+----------+------------+-----------------+
| memberid | membername | memberaddress   |
+----------+------------+-----------------+
| Dang     | DangTangYi | Bucheon, Korea  |
| Figure   | Yeona      | GangNam, Korea  |
| Han      | JuYeon     | Incheon, Korea  |
| Jee      | JiWoon     | Seoul, Korea    |
| Sang     | SangGil    | SeongNam, Korea |
+----------+------------+-----------------+
5 rows in set (0.00 sec)

삭제

mysql> delete from membertbl where membername='DangTangYi';
Query OK, 1 row affected (0.00 sec)

확인, 삭제되었음

mysql> select * from membertbl;
+----------+------------+-----------------+
| memberid | membername | memberaddress   |
+----------+------------+-----------------+
| Figure   | Yeona      | GangNam, Korea  |
| Han      | JuYeon     | Incheon, Korea  |
| Jee      | JiWoon     | Seoul, Korea    |
| Sang     | SangGil    | SeongNam, Korea |
+----------+------------+-----------------+
4 rows in set (0.00 sec)

삭제된 Dang이 트리거에 의해 deletedmembertbl에 정상 insert되었는지 확인

mysql> select * from deletedmembertbl;
+----------+------------+----------------+---------------------+
| memberid | membername | memberaddress  | deletedata          |
+----------+------------+----------------+---------------------+
| Dang     | DangTangYi | Bucheon, Korea | 2019-03-01 21:21:46 |
+----------+------------+----------------+---------------------+
1 row in set (0.00 sec)

mysql>