共计 3985 个字符,预计需要花费 10 分钟才能阅读完成。
MySQL 从 5.0.2 版本开始支持触发器的功能,本次博客就来介绍一下触发器,mysql 版本:mysql-5.7.19。
什么是触发器
触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
举个例子,比如你现在有两个表【用户表】和【日志表】,当一个用户被创建的时候,就需要在日志表中插入创建的 log 日志,如果在不使用触发器的情况下,你需要使用两条插入语句才能实现,但是如果你定义了一个触发器,触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。
当然触发器并不是只能进行插入操作,还能执行修改,删除。
创建触发器
创建触发器的语法如下:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name:触发器的名称
tirgger_time:触发时机,为 BEFORE 或者 AFTER
trigger_event:触发事件,为 INSERT、DELETE 或者 UPDATE
tb_name:表示建立触发器的表明,就是在哪张表上建立触发器
trigger_stmt:触发器的程序体,可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语句
所以可以说 MySQL 创建以下六种触发器:BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE
tigger_event:
load data 语句是将文件的内容插入到表中,相当于是 insert 语句,而 replace 语句在一般的情况下和 insert 差不多,但是如果表中存在 primary 或者 unique 索引的时候,如果插入的数据和原来的 primary key 或者 unique 相同的时候,会删除原来的数据,然后增加一条新的数据,所以有的时候执行一条 replace 语句相当于执行了一条 delete 和 insert 语句。
trigger_stmt:
trigger_stmt 可以是一条 SQL 语句,也可以是多条 SQL 代码块,那如何创建呢?
DELIMITER $ #将语句的分隔符改为 $
BEGIN
sql1;
sql2;
...
sqln
END $
DELIMITER ; #将语句的分隔符改回原来的分号 ";"
在 BEGIN…END 语句中也可以定义变量,但是只能在 BEGIN…END 内部使用:
DECLARE var_name var_type [DEFAULT value] #定义变量,可指定默认值
SET var_name = value #给变量赋值
NEW 和 OLD 的使用:
根据以上的表格,可以使用一下格式来使用相应的数据:
NEW.columnname:新增行的某列数据
OLD.columnname:删除行的某列数据
说了这么多现在我们来创建一个触发器吧!
现在有表如下:
mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| passwd | varchar(20) | 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 |
| log | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
需求是:当在 userinfo 中插入一条数据,就会在 log 中生成一条日志信息。
创建触发器:
DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON userinfo FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40);
DECLARE s2 VARCHAR(20);
SET s2 = "is created";
SET s1 = CONCAT(NEW.username,s2); #函数 CONCAT 可以将字符串连接
INSERT INTO log(log) values(s1);
END $
DELIMITER ;
在 userinfo 中插入数据并查看数据:
mysql> insert into userinfo(username,passwd) values('frank','123');
Query OK, 1 row affected (0.01 sec)
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank | 123 |
+----+----------+--------+
1 row in set (0.00 sec)
好的,我们再来查看一下 log 表吧!
mysql> select * from log;
+----+------------------+
| id | log |
+----+------------------+
| 1 | frank is created |
+----+------------------+
1 row in set (0.00 sec)
通过上面的例子,可以看到只需要在 userinfo 中插入用户的信息,日志会自动记录到 log 表中,这也许就是触发器给我带来的便捷吧!
删除触发器
一次可以删除一个触发器,语法如下:
DROP TRIGGER [db_name.]trigger_name #如果不指定 db_name,默认为当前的数据库。
比如删除上面例子的触发器:
mysql> drop trigger user_log;
Query OK, 0 rows affected (0.00 sec)
查看触发器
可以通过 show triggers 命令查看触发器的状态:
mysql> show triggers \G;
*************************** 1. row ***************************
Trigger: user_log
Event: INSERT
Table: userinfo
Statement: BEGIN
DECLARE s1 VARCHAR(40);
DECLARE s2 VARCHAR(20);
SET s2 = "is created";
SET s1 = CONCAT(NEW.username,s2);
INSERT INTO log(log) values(s1);
END
Timing: AFTER
Created: 2017-09-22 21:12:46.02
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified
限制和注意事项
触发器会有以下两种限制:
1. 触发程序不能调用将数据返回客户端的存储程序,也不能使用采用 CALL 语句的动态 SQL 语句,但是允许存储程序通过参数将数据返回触发程序,也就是存储过程或者函数通过 OUT 或者 INOUT 类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。
2. 不能再触发器中使用以显示或隐式方式开始或结束事务的语句,如 START TRANS-ACTION,COMMIT 或 ROLLBACK。
注意事项:MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的,其中任何一步发生错误都不会继续执行剩下的操作,如果对事务表进行的操作,如果出现错误,那么将会被回滚,如果是对非事务表进行操作,那么就无法回滚了,数据可能会出错。
总结
触发器是基于行触发的,所以删除、新增或者修改操作可能都会激活触发器,所以不要编写过于复杂的触发器,也不要增加过得的触发器,这样会对数据的插入、修改或者删除带来比较严重的影响,同时也会带来可移植性差的后果,所以在设计触发器的时候一定要有所考虑。
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-09/147063.htm