1-MySQL - TRIGGER
about
触发器是与表有关的数据库对象，在满足特定的条件触发，并执行触发器中定义的语句集。
说白了，触发器就像一个牛皮糖，依附于某个表上，当表的行记录有增/删/改的操作时，可以触发触发器内提前写好的语句集的执行。
注意，查询时没有触发器的操作。
创建触发器的四大要素
- 监视谁：
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表中记录日志。
创建表结构
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;
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 |
+----+--------+---------------+---------------------+
| 1 | 张开 | before insert | 2020-09-15 21:32:57 |
| 2 | 张开 | after insert | 2020-09-15 21:32:57 |
+----+--------+---------------+---------------------+
2 rows in set (0.00 sec)
OK，没问题，触发器执行成功。
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 |
+----+------------------+---------------+---------------------+
| 1 | 张开 | before insert | 2020-09-15 21:32:57 |
| 2 | 张开 | after insert | 2020-09-15 21:32:57 |
| 3 | 张开腿|张开 | before update | 2020-09-15 21:42:39 |
| 4 | 张开腿|张开 | after update | 2020-09-15 21:42:39 |
+----+------------------+---------------+---------------------+
4 rows in set (0.00 sec)
是不是很完美！
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, 1 row affected (0.01 sec)
mysql> SELECT * FROM t_log;
+----+------------------+---------------+---------------------+
| id | t_log | t_log_type | t_log_time |
+----+------------------+---------------+---------------------+
| 1 | 张开 | before insert | 2020-09-15 21:32:57 |
| 2 | 张开 | after insert | 2020-09-15 21:32:57 |
| 3 | 张开腿|张开 | before update | 2020-09-15 21:42:39 |
| 4 | 张开腿|张开 | after update | 2020-09-15 21:42:39 |
| 5 | 张开腿 | before delete | 2020-09-15 21:48:47 |
| 6 | 张开腿 | after delete | 2020-09-15 21:48:47 |
+----+------------------+---------------+---------------------+
6 rows in set (0.00 sec)
演示到这里，基本上触发器的常用方式也演示完了，通过几个例子，可以发现，一个表中可以建立多个触发器。
查看触发器
查看我们上面创建的几个触发器该怎么办？
SHOW TRIGGERS;
+-------------+--------+--------+--------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-------------+--------+--------+--------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
| user_log_t1 | INSERT | t_user | begin
insert into t_log(t_log,t_log_type,t_log_time) value(new.t_name, 'before insert', now());
end | BEFORE | 2020-09-15 21:32:44.24 | 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 | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci |
| user_log_t2 | INSERT | t_user | BEGIN
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(new.t_name, 'after insert', NOW());
end | AFTER | 2020-09-15 21:32:47.70 | 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 | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci |
| user_log_t3 | UPDATE | t_user | 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 | BEFORE | 2020-09-15 21:41:21.76 | 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 | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci |
| user_log_t4 | UPDATE | t_user | 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 | AFTER | 2020-09-15 21:41:17.00 | 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 | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci |
| user_log_t5 | DELETE | t_user | BEGIN
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'before delete', NOW());
END | BEFORE | 2020-09-15 21:47:17.24 | 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 | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci |
| user_log_t6 | DELETE | t_user | BEGIN
INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'after delete', NOW());
END | AFTER | 2020-09-15 21:47:20.81 | 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 | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci |
+-------------+--------+--------+--------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+
6 rows in set (0.00 sec)
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：
-- 语法
DROP TRIGGER 触发器名;
-- 示例
DROP TRIGGER user_log_t1;
另外，当触发器依附的表被删除后，该表相关的触发器也就没了：
mysql> SELECT trigger_name,event_manipulation,event_object_table,created FROM information_schema.triggers WHERE event_object_table='t_user';
+--------------+--------------------+--------------------+------------------------+
| trigger_name | event_manipulation | event_object_table | created |
+--------------+--------------------+--------------------+------------------------+
| user_log_t1 | INSERT | t_user | 2020-09-16 09:43:15.66 |
| user_log_t2 | INSERT | t_user | 2020-09-16 09:43:18.55 |
| user_log_t3 | UPDATE | t_user | 2020-09-16 09:43:24.40 |
| user_log_t4 | UPDATE | t_user | 2020-09-16 09:43:33.31 |
| user_log_t5 | DELETE | t_user | 2020-09-16 09:43:37.27 |
| user_log_t6 | DELETE | t_user | 2020-09-16 09:43:39.95 |
+--------------+--------------------+--------------------+------------------------+
6 rows in set (0.00 sec)
mysql> DROP TABLE t_user;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT trigger_name,event_manipulation,event_object_table,created FROM information_schema.triggers WHERE event_object_table='t_user';
Empty set (0.00 sec)
小结
限制和注意事项
触发器会有以下两种限制：
-
触发程序不能调用将数据返回客户端的存储程序，也不能使用采用
CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。 -
不能在触发器中使用以显示或隐式方式开始或结束事务的语句，如
START TRANS-ACTION,COMMIT或ROLLBACK。
注意事项：MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作，如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果是对非事务表进行操作，那么就无法回滚了，数据可能会出错。
触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。
触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
数据库触发器有以下的作用：
-
安全性，可以基于数据库的值使用户具有操作数据库的某种权利。
- 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
- 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。
-
审计，可以跟踪用户对数据库的操作。
- 审计用户操作数据库的语句。
- 把用户对数据库的更新写入审计表。
-
实现复杂的数据完整性规则
- 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
- 提供可变的缺省值。
-
实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。
- 在修改或删除时级联修改或删除其它表中的与之匹配的行。
- 在修改或删除时把其它表中的与之匹配的行设成NULL值。
- 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
- 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。
-
同步实时地复制表中的数据。
-
自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。
that's all,see also:
武沛齐：触发器 | MySQL触发器trigger的使用 | mysql触发器trigger 实例详解 | MySQL触发器概念、原理与用法详解
