Mysql 简明教程

MySQL - SHOW TRIGGERS

MySQL 中的触发器类似于过程，是一些存储程序。它们可以在与事件关联的表、架构、视图和数据库上创建，并且每当事件发生时便会调用相应的触发器。

Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked.

MySQL 提供语句来列出数据库中所有现有的触发器。了解触发器信息在创建新触发器时非常有用，这样用户就不会在多个触发器中使用相同名称。

MySQL provides a statement to list out all the existing triggers present in a database. Knowing the trigger information can be useful while creating new triggers, so that a user wouldn’t use the same name for multiple triggers.

Show Triggers in MySQL

SHOW TRIGGERS 语句用于在 MySQL 中显示当前数据库中定义的所有触发器的信息。

The SHOW TRIGGERS Statement is used in MySQL to display information about all the triggers defined in the current database.

Syntax

以下是 MySQL SHOW TRIGGERS 语句的语法 −

Following is the syntax of the MySQL SHOW TRIGGERS Statement −

SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

Example

在本示例中，我们使用以下请求创建名为 STUDENT 的表 −

In this example, we are creating a table named STUDENT using the query below −

CREATE TABLE STUDENT(
   Name varchar(35),
   Age INT,
   Score INT
);

以下请求创建触发器 sample_trigger，如果将负分值输入到表中，它会将得分值设置为 0。

Following query creates a trigger sample_trigger which will set the score value to 0 if you enter a negative score value into the table.

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 子句创建了另一个触发器 −

Assume we have created another trigger using the AFTER clause −

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 ;

以下请求显示当前数据库中的现有触发器 −

Following query shows the existing triggers in the current database −

SHOW TRIGGERS \G;

Output

触发器列表将按以下形式显示 −

The list of triggers will be displayed as follows −

*************************** 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 子句检索特定数据库中触发器的信息。

You can retrieve the information of triggers from a specific database using the FROM clause.

Example

假设当前数据库名为 demo 。以下请求显示数据库 demo 中存在的触发器 −

Assume that the current database is named demo. Following query shows the triggers present in the database demo −

SHOW TRIGGERS FROM demo\G

您也可以使用 IN 子句代替 FROM 来获取相同输出。

You can also use the IN clause instead of FROM, to get the same output.

SHOW TRIGGERS IN demo\G

Output

demo 数据库中存在的触发器 −

The existing triggers present in the demo database −

*************************** 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 子句来检索与指定条件相匹配的触发器信息。

You can use the WHERE clause of the SHOW TRIGGERS statements to retrieve info about the triggers which match the specified condition.

Example

以下请求检索当前数据库中事件为更新的触发器 −

Following query retrieves the triggers in the current database whose event is update −

SHOW TRIGGERS FROM demo WHERE Event = 'UPDATE' \G;

Output

所需触发器列表按以下形式显示 −

The required list of triggers is displayed as follows −

*************************** 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)

Showing Trigger Using Client Program

我们还可使用客户端程序显示触发器。

We can also Show a trigger using a client program.

Syntax

Example

以下是这些程序 −

Following are the programs −