一、简介
MySQL触发器和存储过程类似，都是可以在MySQL执行的一段程序。触发器是由事件来触发的，这些事件包括INSERT、UPDATE、DELETE语句。如果定义了触发器，当数据库执行这些语句的时候，就会执行触发器的程序。
触发器(trigger)是一个特殊的存储过程，不同的是，存储过程需要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动。而是通过定义SQL语句调用的事件来触发。触发器可以查询其他表，也可以执行复杂的SQL语句。
二、创建触发器
1.创建只有一个执行语句的触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
- trigger_name：触发器名称
- trigger_time：触发时机，可以指定为before或after
- trigger_event：触发事件，包括INSERT、UPDATE、DELETE
- tbl_name：建立触发器的表名，即在哪张表上建立触发器
- trigger_stmt：触发器执行的语句
CREATE TABLE `fruits` (
`id` bigint NOT NULL AUTO_INCREMENT,
`number` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`price` double DEFAULT NULL,
PRIMARY KEY (`id`)
)
例：在fruits表创建一个触发器，计算器插入数据前，总的行数
mysql> CREATE TRIGGER sum_fruits_trigger BEFORE INSERT ON fruits FOR EACH ROW SET @sum =(select count(1) from fruits);//
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO fruits (number, name, price) VALUES ('watermelon', '西瓜', 40);//
Query OK, 1 row affected (0.00 sec)
mysql> select @sum ;//
+------+
| @sum |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> select * from fruits;//
+------+------------+--------+-------+
| id | number | name | price |
+------+------------+--------+-------+
| 10 | banana | 香蕉 | 20 |
| 1321 | apple | 苹果 | 100 |
| 1333 | watermelon | 西瓜 | 40 |
| 1334 | watermelon | 西瓜 | 40 |
+------+------------+--------+-------+
4 rows in set (0.00 sec)
可以看出在插入第四行之前，数据库中有3条记录，所以@sum返回3是正确的
2.创建有多个执行语句的触发器
CREARE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGIN
trigger_stmt
END
- trigger_name：触发器名称
- trigger_time：触发时机，可以指定为before或after
- trigger_event：触发事件，包括INSERT、UPDATE、DELETE
- tbl_name：建立触发器的表名，即在哪张表上建立触发器
- trigger_stmt：触发器执行的语句列表，可以是多个
例：创建一个触发器，在插入一条数据时，同时给其他多个表插入数据
CREATE TABLE test1 (a1 INT);
CREATE TABLE test2 (a2 INT);
CREATE TABLE test3 (a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4 (a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0);CREATE TRIGGER testMultiAdd BEFORE INSERT ON test1 FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 +1 WHERE a4 = NEW.a1;
END;//mysql> INSERT INTO test3 (a3) VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
-> INSERT INTO test4 (a4) VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
-> //
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test1 VALUES (1),(3),(1),(7),(1),(8),(4),(4);//
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT * from test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * from test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.01 sec)
mysql> SELECT * from test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
+----+
3 rows in set (0.00 sec)
mysql> SELECT * from test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
从结果看，向表test1插入数据时，test2、test3、test4都发生了变化，证明触发器执行了。
三、查看触发器
1.使用SHOW TRIGGERS查看触发器状态信息
SHOW TRIGGERS;
查看所有的触发器
mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
Trigger: sum_fruits_trigger
Event: INSERT
Table: fruits
Statement: SET @sum =(select count(1) from fruits)
Timing: BEFORE
Created: 2024-06-29 15:27:18.99
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
Trigger: testMultiAdd
Event: INSERT
Table: test1
Statement: BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 +1 WHERE a4 = NEW.a1;
END
Timing: BEFORE
Created: 2024-06-29 15:50:10.12
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)
注：这里\G意思是将输出结果从横向转换为纵向的
2.在triggers表中查看触发信息
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;
例如，查看名称为testMultiAdd的触发器
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = 'testMultiAdd'\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: dh_sys
TRIGGER_NAME: testMultiAdd
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: dh_sys
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 +1 WHERE a4 = NEW.a1;
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: 2024-06-29 15:50:10.12
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
四、删除触发器
DROP TRIGGER [schema_name.]trigger_name
- DROP TRIGGER：删除触发器关键字
- schema_name：表示数据库名称，可选，如果省略了表示行当前数据库删除
- trigger_name：触发器名
mysql> DROP TRIGGER sum_fruits_trigger;
Query OK, 0 rows affected (0.01 sec)
=========================================================================
创作不易，请勿直接盗用，使用请标明转载出处。
喜欢的话，一键三连，您的支持是我一直坚持高质量创作的原动力。