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)
