用mysql觸發器實現log記錄

標籤：

首先建立兩張測試用表

mysql> desc pay;+-------+---------------+------+-----+---------+----------------+| Field | Type          | Null | Key | Default | Extra          |+-------+---------------+------+-----+---------+----------------+| id    | int(11)       | NO   | PRI | NULL    | auto_increment || money | decimal(10,2) | NO   |     | NULL    |                || op    | char(10)      | YES  |     | NULL    |                |+-------+---------------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql> desc log;+-------------+-----------+------+-----+-------------------+-----------------------------+| Field       | Type      | Null | Key | Default           | Extra                       |+-------------+-----------+------+-----+-------------------+-----------------------------+| id          | int(11)   | NO   | PRI | NULL              | auto_increment              || op          | char(10)  | YES  |     | NULL              |                             || oid         | int(11)   | NO   |     | NULL              |                             || last_update | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+-------------+-----------+------+-----+-------------------+-----------------------------+4 rows in set (0.00 sec)

建立一個觸發器

mysql> delimiter $$mysql> create trigger pay_log after insert on pay for each row begin insert into log set oid=new.id, op=new.op; end;$$mysql> delimiter ;

參看觸發器是否建立成功

mysql> show triggers\G;*************************** 1. row ***************************             Trigger: pay_log               Event: INSERT               Table: pay           Statement: begin insert into log set oid=new.id, op=new.op; end              Timing: AFTER             Created: NULL            sql_mode:              Definer: [email protected]character_set_client: latin1collation_connection: latin1_swedish_ci  Database Collation: utf8_general_ci1 row in set (0.00 sec)ERROR: No query specified

測試

mysql> insert into pay set money=123,op=‘jimmy‘;
mysql> select * from pay;+----+--------+-------+| id | money  | op    |+----+--------+-------+|  3 | 123.00 | jimmy ||  4 | 123.00 | jimmy |+----+--------+-------+2 rows in set (0.00 sec)mysql> select * from log;+----+-------+-----+---------------------+| id | op    | oid | last_update         |+----+-------+-----+---------------------+|  1 | jimmy |   3 | 2015-05-16 16:32:05 ||  2 | jimmy |   4 | 2015-05-16 16:32:51 |+----+-------+-----+---------------------+2 rows in set (0.00 sec)

同理還可以再建立一個update和delete的觸發器

 

題外話

      建立觸發器的時候發現沒有許可權，啟動參數加上--skip-grant-table解決

用mysql觸發器實現log記錄