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)
来源: http://www.bubuko.com/infodetail-2801504.html