1. Mysql Triggers
간단 트리거를 생성해보자.
중요한 테이블이 수시로 값이 변경되는데 나중에 변경전 값을 확인할경우
트리거에 변경될때마다 이전값을 저장해놓으면 편리하다.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
| a_log |
+----------------+
2 rows in set (0.00 sec)
기본 구조: 사용중인 테이블(a)과 변경값이 저장될 테이블(a_log)
각 테이블 구조
mysql> desc a;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| seq | int(10) | NO | PRI | NULL | auto_increment |
| a | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc a_log;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| seq | int(10) | NO | | | |
| a | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
a 테이블의 데이터 내용
mysql> select * from a;
+-----+------+
| seq | a |
+-----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-----+------+
4 rows in set (0.06 sec)
1. 트리거를 생성한다.
mysql> CREATE TRIGGER tru_a BEFORE UPDATE ON a FOR EACH ROW INSERT INTO a_log (seq, a) VALUES (OLD.seq, OLD.a);
Query OK, 0 rows affected (0.02 sec)
2. 데이터를 UPDATE
mysql> update a set a='w' where seq=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3. 확인해보니 이전값이 들어가 있다.
mysql> select * from a_log;
+-----+------+
| seq | a |
+-----+------+
| 1 | a |
+-----+------+
1 row in set (0.00 sec)
4. 트리거 목록을 확인. (다른방법이 있을꺼 같은데..몰라서)
mysql> select * from information_schema.TRIGGERS \G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: tru_a
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: a
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: INSERT INTO a_log (seq, a) VALUES (OLD.seq, OLD.a)
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: root@localhost
1 row in set (0.00 sec)
url http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
Tip shell 상에서 생성문이 필요할때가 있다.
테이블 a 생성문때문에 질의한 query
mysql> show create table a;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`seq` int(10) NOT NULL auto_increment,
`a` varchar(5) default NULL,
PRIMARY KEY (`seq`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)