Mysql 简明教程
MySQL - SHOW TRIGGERS
MySQL 中的触发器类似于过程，是一些存储程序。它们可以在与事件关联的表、架构、视图和数据库上创建，并且每当事件发生时便会调用相应的触发器。
MySQL 提供语句来列出数据库中所有现有的触发器。了解触发器信息在创建新触发器时非常有用，这样用户就不会在多个触发器中使用相同名称。
Show Triggers in MySQL
SHOW TRIGGERS 语句用于在 MySQL 中显示当前数据库中定义的所有触发器的信息。
Syntax
以下是 MySQL SHOW TRIGGERS 语句的语法 −
SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]Example
在本示例中，我们使用以下请求创建名为 STUDENT 的表 −
CREATE TABLE STUDENT(
Name varchar(35),
Age INT,
Score INT
);以下请求创建触发器 sample_trigger，如果将负分值输入到表中，它会将得分值设置为 0。
DELIMITER //
CREATE TRIGGER sample_trigger
BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
IF NEW.score < 0 THEN SET NEW.score = 0;
END IF;
END //
DELIMITER ;假设我们使用 AFTER 子句创建了另一个触发器 −
DELIMITER //
CREATE TRIGGER testTrigger
AFTER UPDATE ON Student
FOR EACH ROW
BEGIN
INSERT INTO Student
SET action = 'update',
Name = OLD.Name,
Age = OLD.age,
Score = OLD.score;
END;
END //
DELIMITER ;以下请求显示当前数据库中的现有触发器 −
SHOW TRIGGERS \G;Output
触发器列表将按以下形式显示 −
*************************** 1. row ***************************
Trigger: sample_trigger
Event: INSERT
Table: student
Statement: BEGIN
IF NEW.score < 0 THEN SET NEW.score = 0;
END IF;
END
Timing: BEFORE
Created: 2021-05-12 19:08:04.50
sql_mode: STRICT_TRANS_TABLES,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: testTrigger
Event: UPDATE
Table: student
Statement: INSERT INTO Student
SET Name = OLD.Name,
Age = OLD.age,
Score = OLD.score
Timing: AFTER
Created: 2021-05-12 19:10:44.49
sql_mode: STRICT_TRANS_TABLES,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)With FROM or IN Clause
您可以使用 FROM 子句检索特定数据库中触发器的信息。
Example
假设当前数据库名为 demo 。以下请求显示数据库 demo 中存在的触发器 −
SHOW TRIGGERS FROM demo\G您也可以使用 IN 子句代替 FROM 来获取相同输出。
SHOW TRIGGERS IN demo\GOutput
demo 数据库中存在的触发器 −
*************************** 1. row ***************************
Trigger: sample_trigger
Event: INSERT
Table: student
Statement: BEGIN
IF NEW.score < 0 THEN SET NEW.score = 0;
END IF;
END
Timing: BEFORE
Created: 2023-09-29 11:42:33.58
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: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
Trigger: testTrigger
Event: UPDATE
Table: student
Statement: BEGIN
INSERT INTO Student
SET action = 'update',
Name = OLD.Name,
Age = OLD.age,
Score = OLD.score;
END
Timing: AFTER
Created: 2023-09-29 11:43:10.27
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: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)With WHERE Clause
您可以使用 SHOW TRIGGERS 语句的 WHERE 子句来检索与指定条件相匹配的触发器信息。
Output
所需触发器列表按以下形式显示 −
*************************** 1. row ***************************
Trigger: testTrigger
Event: UPDATE
Table: student
Statement: BEGIN
INSERT INTO Student
SET action = 'update',
Name = OLD.Name,
Age = OLD.age,
Score = OLD.score;
END
Timing: AFTER
Created: 2023-09-29 11:43:10.27
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: cp850
collation_connection: cp850_general_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)