用mysql触发器实现log记录

+-------+---------------+------+-----+---------+----------------+ | 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: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8_general_ci
  1  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记录

标签：

查看更多关于用mysql触发器实现log记录的详细内容...