MySQL/MariaDB数据库的触发器
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.触发器概述
1>.什么是触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。=
2>.创建触发器帮助信息
MariaDB [yinzhengjie]> HELP CREATE TRIGGER Name: 'CREATE TRIGGER' Description: Syntax: CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view. CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in https://mariadb.com/kb/en/binary-logging-of-stored-routines/. The DEFINER clause determines the security context to be used when checking access privileges at trigger activation time. See later in this section for more information. trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified. trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following: o INSERT: The trigger is activated whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements. o UPDATE: The trigger is activated whenever a row is modified; for example, through UPDATE statements. o DELETE: The trigger is activated whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. However, DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either. See [HELP TRUNCATE TABLE]. URL: https://mariadb.com/kb/en/create-trigger/ MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
Syntax: 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：
该触发器作用在表名
二.触发器案例展示
1>.创建测试表
MariaDB [yinzhengjie]> CREATE TABLE student_info ( -> stu_id INT(11) NOT NULL AUTO_INCREMENT, -> stu_name VARCHAR(255) DEFAULT NULL, -> PRIMARY KEY (stu_id) -> ); Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjie]> CREATE TABLE student_count ( -> student_count INT(11) DEFAULT 0 -> ); Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> INSERT INTO student_count VALUES(0); Query OK, 1 row affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_info; Empty set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_count; +---------------+ | student_count | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]>
2>.创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
MariaDB [yinzhengjie]> 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 [yinzhengjie]> MariaDB [yinzhengjie]> 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 [yinzhengjie]> MariaDB [yinzhengjie]>
3>.查看触发器
MariaDB [yinzhengjie]> SHOW TRIGGERSG *************************** 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: 2019-10-28 22:20:07.74 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_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: 2019-10-28 22:20:12.02 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci 2 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yinzhengjie | +--------------------+ 4 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> USE information_schema; Database changed MariaDB [information_schema]> MariaDB [information_schema]> SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert'G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: yinzhengjie TRIGGER_NAME: trigger_student_count_insert EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: yinzhengjie EVENT_OBJECT_TABLE: student_info ACTION_ORDER: 1 ACTION_CONDITION: NULL ACTION_STATEMENT: UPDATE student_count SET student_count=student_count+1 ACTION_ORIENTATION: ROW ACTION_TIMING: AFTER ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2019-10-28 22:20:07.74 SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_general_ci DATABASE_COLLATION: utf8_general_ci 1 row in set (0.01 sec) MariaDB [information_schema]> MariaDB [information_schema]>
4>.测试触发器是否执行
MariaDB [(none)]> use yinzhengjie Database changed MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_count; +---------------+ | student_count | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_info; Empty set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> INSERT INTO student_info VALUES (1,'Jason Yin'),(2,'YinZhengJie'),(3,'Shell'),(4,'Python'),(5,'Java'),(6,'Golang'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_info; +--------+-------------+ | stu_id | stu_name | +--------+-------------+ | 1 | Jason Yin | | 2 | YinZhengJie | | 3 | Shell | | 4 | Python | | 5 | Java | | 6 | Golang | +--------+-------------+ 6 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_count; +---------------+ | student_count | +---------------+ | 6 | +---------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> DELETE FROM student_info WHERE stu_id > 3; Query OK, 3 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_count; +---------------+ | student_count | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM student_count; +---------------+ | student_count | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_info; +--------+-------------+ | stu_id | stu_name | +--------+-------------+ | 1 | Jason Yin | | 2 | YinZhengJie | | 3 | Shell | +--------+-------------+ 3 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> TRUNCATE TABLE student_info; #使用TRUNCATE清空表中的数据库不会触发触发器哟，因为触发器定义时只针对了DELETE命令进行触发操作 Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_info; Empty set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SELECT * FROM student_count; +---------------+ | student_count | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec) MariaDB [yinzhengjie]>
5>.删除触发器
MariaDB [yinzhengjie]> SHOW TRIGGERSG *************************** 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: 2019-10-28 22:20:07.74 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_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: 2019-10-28 22:20:12.02 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci 2 rows in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> DROP TRIGGER trigger_student_count_insert; Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW TRIGGERSG *************************** 1. row *************************** Trigger: trigger_student_count_delete Event: DELETE Table: student_info Statement: UPDATE student_count SET student_count=student_count-1 Timing: AFTER Created: 2019-10-28 22:20:12.02 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW TRIGGERSG *************************** 1. row *************************** Trigger: trigger_student_count_delete Event: DELETE Table: student_info Statement: UPDATE student_count SET student_count=student_count-1 Timing: AFTER Created: 2019-10-28 22:20:12.02 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> DROP TABLE student_count; Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> DROP TABLE student_info; #当删除表时触发器也会随着自动删除，因为触发器是基于表的，当表都不存在了触发器也没有意义了。 Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]> SHOW TRIGGERSG Empty set (0.00 sec) MariaDB [yinzhengjie]> MariaDB [yinzhengjie]>