MySQL - 触发器（TRIGGER)

一.触发器基本概念

触发器是与表有关的数据库对象，在满足特定的条件触发，并执行触发器中定义的语句集。

说白了，触发器就像一个牛皮糖，依附于某个表上，当表的行记录有增/删/改的操作时，可以触发触发器内提前写好的语句集的执行。

注意，查询时没有触发器的操作。

创建触发器的四大要素

1. 监视谁：table
2. 监视什么事件：表中记录执行insert/update/delete前后
3. 触发条件：after/before
4. 要触发什么事件：insert/update/delete

另外，还需要注意触发频率：针对每一行记录的操作都会触发触发器的执行。

还有：触发器无法与临时表或视图关联。

再来看触发器的类型：

触发器类型	适用语句
INSERT型触发器	INSERT/LOAD DATA/REPLACE
UPDATE型触发器	UPDATE
DELETE型触发器	DELETE/REPLACE

 

LOAD DATA语句是将文件的内容插入到表中，相当于是INSERT语句，而REPLACE语句在一般的情况下和INSERT差不多，但是如果表中存在PRIMARY或者UNIQUE索引的时候，如果插入的数据和原来的PRIMARY KEY或者UNIQUE相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条REPLACE语句相当于执行了一条DELETE和INSERT语句。

在触发器中，还有NEW和OLD语句可用：

触发器类型	NEW和OLD的使用	备注
INSERT型触发器	NEW表示将要或者已经新增的数据	没有OLD
UPDATE型触发器	OLD表示原数据；NEW表示修改后的数据
DELETE型触发器	OLD表示将要或者已经删除的数据	没有NEW

 

二、创建触发器

基本语法

CREATE TRIGGER 触发器名 [BEFORE|AFTER] 触发事件
ON 表名 FOR EACH ROW          -- FOR EACH ROW:基于每一行记录变动而触发
BEGIN    -- 表示被触发的事件开始
-- 要执行的语句
END        -- 表示被触发的事件结束

 

但是，由于MySQL默认以;作为语句的结束符，而在触发器内部的语句集中难免有语句以;结束，为了保证触发器内部语句逻辑完整性和不影响其他SQL的正常执行，通常使用DELIMITER语句来临时修改默认的语句结束符，所以，触发器的一般形式是这样的：

DELIMITER //      -- 将MySQL的默认分隔符修改为 //   当然，你也可以指定别的符号
CREATE TRIGGER 触发器名 [BEFORE|AFTER] 触发事件
ON 表名 FOR EACH ROW          -- FOR EACH ROW:基于每一行记录变动而触发
BEGIN
-- 要执行的语句
END //        -- 这个 // 表示触发器部分逻辑执行完毕
DELIMITER ;    -- 最后将默认分隔符再修改回来，不影响其他SQL的正常执行

少说多练，来上示例，首先我们模拟一个场景，就是，往user表中新增用户，都将会在log表中记录日志

创建表结构

CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(32) NOT NULL
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE t_log(
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(32) NOT NULL,
t_log_type VARCHAR(32) NOT NULL,
t_log_time DATETIME
)ENGINE=INNODB CHARSET=utf8;

 

2.1 before/after insert

创建触发器，每当user表插入一条数据，就往log表写入2条记录：

-- 插入前执行触发器
DELIMITER //
CREATE TRIGGER user_log_t1 BEFORE INSERT
ON t_user FOR EACH ROW
BEGIN
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(NEW.t_name, 'before insert', NOW());
END //
DELIMITER ;

-- 插入后执行触发器
DELIMITER //
CREATE TRIGGER user_log_t2 AFTER INSERT
ON t_user FOR EACH ROW
BEGIN    
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(NEW.t_name, 'after insert', NOW());
END //
DELIMITER ;

我们往user表插入一条数据，在查看下log表：

 

mysql> insert into t_user(t_name) value('张三');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_log;
+----+--------+--------------+---------------------+
| id | t_log  | t_log_type   | t_log_time          |
+----+--------+--------------+---------------------+
|  2 | 张三   | befor_insert | 2021-05-11 20:19:50 |
|  3 | 张三   | befor_insert | 2021-05-11 20:19:50 |
+----+--------+--------------+---------------------+
2 rows in set (0.00 sec)

 2.2 before/after update

 创建执行器

-- 更新前执行触发器
DELIMITER //
CREATE TRIGGER user_log_t3 BEFORE UPDATE
ON t_user FOR EACH ROW
BEGIN
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(CONCAT(NEW.t_name, '|', OLD.t_name), 'before update', NOW());
END //
DELIMITER ;

-- 更新后执行触发器
DELIMITER //
CREATE TRIGGER user_log_t4 AFTER UPDATE
ON t_user FOR EACH ROW
BEGIN    
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(CONCAT(NEW.t_name, '|', OLD.t_name), 'after update', NOW());
END //
DELIMITER ;

 

 

 更新记录看效果

mysql> update t_user set t_name='李四' where t_name="张三";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t_log;
+----+---------------+---------------+---------------------+
| id | t_log         | t_log_type    | t_log_time          |
+----+---------------+---------------+---------------------+
|  2 | 张三          | befor_insert  | 2021-05-11 20:19:50 |
|  3 | 张三          | befor_insert  | 2021-05-11 20:19:50 |
|  4 | 李四|张三     | before update | 2021-05-11 20:27:41 |
|  5 | 李四|张三     | after update  | 2021-05-11 20:27:41 |
+----+---------------+---------------+---------------------+
4 rows in set (0.00 sec)

 

 2.3 before/after delete

 创建触发器：

-- 删除前执行触发器
DELIMITER //
CREATE TRIGGER user_log_t5 BEFORE DELETE
ON t_user FOR EACH ROW
BEGIN
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'before delete', NOW());
END //
DELIMITER ;

-- 删除后执行触发器
DELIMITER //
CREATE TRIGGER user_log_t6 AFTER DELETE
ON t_user FOR EACH ROW
BEGIN    
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'after delete', NOW());
END //
DELIMITER ;

 

 

mysql> DELETE FROM t_user WHERE t_name='张三';
Query OK, 0 rows affected (0.00 sec)


mysql> select * from t_log;
+----+---------------+---------------+---------------------+
| id | t_log         | t_log_type    | t_log_time          |
+----+---------------+---------------+---------------------+
|  2 | 张三          | befor_insert  | 2021-05-11 20:19:50 |
|  3 | 张三          | befor_insert  | 2021-05-11 20:19:50 |
|  4 | 李四|张三     | before update | 2021-05-11 20:27:41 |
|  5 | 李四|张三     | after update  | 2021-05-11 20:27:41 |
|  6 | 张三          | befor_insert  | 2021-05-11 20:33:07 |
|  7 | 张三          | befor_insert  | 2021-05-11 20:33:07 |
|  8 | 张三          | before delete | 2021-05-11 20:34:33 |
|  9 | 张三          | after delete  | 2021-05-11 20:34:33 |
+----+---------------+---------------+---------------------+
8 rows in set (0.00 sec)

三、查看触发器

SHOW TRIGGERS;

 

 

SHOW TRIGGERS返回了所有的触发器概要信息，无法查看指定的触发器信息，但可以通过视图表来查看：

-- 所有的触发器都在 information_schema.triggers 表中
SELECT * FROM information_schema.triggers;  

-- 可以跟 where条件过滤指定的触发器
SELECT * FROM information_schema.triggers WHERE trigger_name='user_log_t1';

-- 触发器返回字段过多，可以过滤指定字段
SELECT trigger_name,event_manipulation,event_object_table,created
FROM information_schema.triggers 
WHERE trigger_name='user_log_t1';
+--------------+--------------------+--------------------+------------------------+
| trigger_name | event_manipulation | event_object_table | created                |
+--------------+--------------------+--------------------+------------------------+
| user_log_t1  | INSERT             | t_user             | 2020-09-15 21:32:44.24 |
+--------------+--------------------+--------------------+------------------------+
1 row in set (0.00 sec)

 

 

四、删除触发器

-- 语法
DROP TRIGGER 触发器名;

-- 示例
DROP TRIGGER user_log_t1;

 

 

另外，当触发器依附的表被删除后，该表相关的触发器也就没了：