一.触发器基本概念
触发器是与表有关的数据库对象，在满足特定的条件触发，并执行触发器中定义的语句集。
说白了，触发器就像一个牛皮糖，依附于某个表上，当表的行记录有增/删/改的操作时，可以触发触发器内提前写好的语句集的执行。
注意，查询时没有触发器的操作。
创建触发器的四大要素
- 监视谁：
table - 监视什么事件：表中记录执行
insert/update/delete前后 - 触发条件：
after/before - 要触发什么事件：
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表中记录日志
创建表结构
1 CREATE TABLE t_user( 2 id INT PRIMARY KEY AUTO_INCREMENT, 3 t_name VARCHAR(32) NOT NULL 4 )ENGINE=INNODB CHARSET=utf8; 5 6 CREATE TABLE t_log( 7 id INT PRIMARY KEY AUTO_INCREMENT, 8 t_log VARCHAR(32) NOT NULL, 9 t_log_type VARCHAR(32) NOT NULL, 10 t_log_time DATETIME 11 )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表：
1 mysql> insert into t_user(t_name) value(‘张三‘); 2 Query OK, 1 row affected (0.00 sec) 3 4 mysql> select * from t_log; 5 +----+--------+--------------+---------------------+ 6 | id | t_log | t_log_type | t_log_time | 7 +----+--------+--------------+---------------------+ 8 | 2 | 张三 | befor_insert | 2021-05-11 20:19:50 | 9 | 3 | 张三 | befor_insert | 2021-05-11 20:19:50 | 10 +----+--------+--------------+---------------------+ 11 2 rows in set (0.00 sec)
2.2 before/after update
创建执行器
1 -- 更新前执行触发器 2 DELIMITER // 3 CREATE TRIGGER user_log_t3 BEFORE UPDATE 4 ON t_user FOR EACH ROW 5 BEGIN 6 INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(CONCAT(NEW.t_name, ‘|‘, OLD.t_name), ‘before update‘, NOW()); 7 END // 8 DELIMITER ; 9 10 -- 更新后执行触发器 11 DELIMITER // 12 CREATE TRIGGER user_log_t4 AFTER UPDATE 13 ON t_user FOR EACH ROW 14 BEGIN 15 INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(CONCAT(NEW.t_name, ‘|‘, OLD.t_name), ‘after update‘, NOW()); 16 END // 17 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 ;
1 mysql> DELETE FROM t_user WHERE t_name=‘张三‘; 2 Query OK, 0 rows affected (0.00 sec) 3 4 5 mysql> select * from t_log; 6 +----+---------------+---------------+---------------------+ 7 | id | t_log | t_log_type | t_log_time | 8 +----+---------------+---------------+---------------------+ 9 | 2 | 张三 | befor_insert | 2021-05-11 20:19:50 | 10 | 3 | 张三 | befor_insert | 2021-05-11 20:19:50 | 11 | 4 | 李四|张三 | before update | 2021-05-11 20:27:41 | 12 | 5 | 李四|张三 | after update | 2021-05-11 20:27:41 | 13 | 6 | 张三 | befor_insert | 2021-05-11 20:33:07 | 14 | 7 | 张三 | befor_insert | 2021-05-11 20:33:07 | 15 | 8 | 张三 | before delete | 2021-05-11 20:34:33 | 16 | 9 | 张三 | after delete | 2021-05-11 20:34:33 | 17 +----+---------------+---------------+---------------------+ 18 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;
另外，当触发器依附的表被删除后，该表相关的触发器也就没了：