用SQL触发器实现log日志

发布网友发布时间：2022-04-07 17:47

共2个回答

懂视网时间：2022-04-07 22:08

+-------+---------------+------+-----+---------+----------------+ | 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 triggersG;
*************************** 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记录

标签：

热心网友时间：2022-04-07 19:16

你可以存储过程来调用触发器，
代码段调用存储过程传递参数（用户名、时间、上一部门）追问能提示一些具体的词汇吗?因为是初学者，甚至不知道该搜索什么