时间：2021-07-01 10:21:17 帮助过：17人阅读
1、什么叫触发器：
当满足一定的条件以后，它会触发一个动作的执行，trigger
2、触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
3、触发器是由一定的事件来触发的，对表的增删改操作、不包括查询，查询是没有触发器的
4、生产中一般不通过MySQL中触发器来实现这功能，是通过Java程序、Python程序代码来实现触发器
5、创建触发器：
CREATE：
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名
6、查看触发器
SHOW TRIGGERS
查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。
mysql> USE information_schema;
Database changed
mysql> SELECT * FROM triggers WHERE
7、删除触发器
DROP TRIGGER trigger_name;
快速的清空表：truncate table student_info
创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
MariaDB [hellodb]> use db1
Database changed
MariaDB [db1]> CREATE TABLE student_info (
-> stu_id INT(11) NOT NULL AUTO_INCREMENT,
-> stu_name VARCHAR(255) DEFAULT NULL,
-> PRIMARY KEY (stu_id)
-> );
CREATE TABLE student_count (
student_count INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);Query OK, 0 rows affected (0.11 sec)
MariaDB [db1]> CREATE TABLE student_count (
-> student_count INT(11) DEFAULT 0
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> INSERT INTO student_count VALUES(0);
Query OK, 1 row affected (0.01 sec)
MariaDB [db1]> select * from student_info;
Empty set (0.00 sec)
创建增加和减少学生数量的触发器
MariaDB [db1]> CREATE TRIGGER trigger_student_count_insert
-> AFTER INSERT
-> ON student_info FOR EACH ROW
-> UPDATE student_count SET student_count=student_count+1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> CREATE TRIGGER trigger_student_count_delete
-> AFTER DELETE
-> ON student_info FOR EACH ROW
-> UPDATE student_count SET student_count=student_count-1;
Query OK, 0 rows affected (0.00 sec)
# 查看创建好的触发器：
MariaDB [db1]> show triggers\G
*************************** 1. row ***************************
Trigger: trigger_student_count_insert
Event: INSERT
Table: student_info
Statement: UPDATE student_count SET student_count=student_count+1
Timing: AFTER
Created: 2018-10-09 20:27:09.78
sql_mode: STRICT_TRANS_TABLES,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
*************************** 2. row ***************************
Trigger: trigger_student_count_delete
Event: DELETE
Table: student_info
Statement: UPDATE student_count SET student_count=student_count-1
Timing: AFTER
Created: 2018-10-09 20:27:27.31
sql_mode: STRICT_TRANS_TABLES,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
2 rows in set (0.00 sec)
查看建好的student_info表，目前是空的
MariaDB [db1]> select * from student_info;
Empty set (0.00 sec)
往里面插入一条数据；
MariaDB [db1]> insert student_info values(1,‘cobbler‘);
Query OK, 1 row affected (0.01 sec)
查看student_info表里面已经有了一条信息
MariaDB [db1]> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | cobbler |
+--------+----------+
1 row in set (0.00 sec)
查看student_count里面的统计数，
MariaDB [db1]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)MySQL-触发器
标签：char sql_mode not 动作 tran -- orm UNC 0.00