MySQL - SHOW TRIGGERS



MySQL 中的触发器是类似于存储过程的存储程序。这些可以在与事件关联的表、模式、视图和数据库上创建,并且每当发生事件时,就会调用相应的触发器。

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

在 MySQL 中显示触发器

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

语法

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

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

示例

在这个例子中,我们使用下面的查询创建一个名为 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;

输出

触发器的列表将如下显示:

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

使用 FROM 或 IN 子句

您可以使用 FROM 子句从特定数据库中检索触发器的信息。

示例

假设当前数据库名为 demo。下面的查询显示数据库 demo 中存在的触发器:

SHOW TRIGGERS FROM demo\G

您也可以使用 IN 子句代替 FROM,以获得相同的输出。

SHOW TRIGGERS IN demo\G

输出

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)

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

使用 WHERE 子句

您可以使用 SHOW TRIGGERS 语句的 WHERE 子句来检索与指定条件匹配的触发器的信息。

示例

下面的查询检索当前数据库中事件为 update 的触发器:

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

输出

所需的触发器列表将如下显示:

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

使用客户端程序显示触发器

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

语法

要通过 PHP 程序显示触发器,我们需要使用 mysqli 函数 query() 来执行 SHOW TRIGGERS 语句,如下所示:

$sql = "Show TRIGGER"; $mysqli->query($sql);

要通过 JavaScript 程序显示触发器,我们需要使用 mysql2 库的 query() 函数来执行 SHOW TRIGGERS 语句,如下所示:

sql = "Show TRIGGER"; con.query(sql);

要通过 Java 程序显示触发器,我们需要使用 JDBC 函数 executeQuery() 来执行 SHOW TRIGGERS 语句,如下所示:

String sql = "Show TRIGGER"; statement.executeQuery(sql);

要通过 python 程序显示触发器,我们需要使用 MySQL Connector/Pythonexecute() 函数来执行 SHOW TRIGGERS 语句,如下所示:

Show_trigger_query = 'SHOW TRIGGER' cursorObj.execute(Show_trigger_query)

示例

以下是程序:

$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $db = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit(); } printf('Connected successfully.'); // Create a trigger $sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score"; if ($mysqli->query($sql)) { printf("Trigger created successfully...!"); } else { printf("Trigger creation failed: %s", $mysqli->error); } // Show created trigger details $sql = "SHOW TRIGGERS"; $res = $mysqli->query($sql); if ($res) { while ($row = $res->fetch_assoc()) { // Print trigger details foreach ($row as $key => $value) { printf("%s: %s", $key, $value); } printf(""); } $res->free(); } else { printf("Failed to retrieve triggers: %s", $mysqli->error); } $mysqli->close();

输出

获得的输出如下:

Connected successfully.
Trigger created successfully...!
Trigger: testTrigger
Event: UPDATE
Table: student
Statement: INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score
Timing: AFTER
Created: 2023-09-08 12:16:27.54
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
var mysql = require('mysql2'); var con = mysql.createConnection({ host:"localhost", user:"root", password:"password" }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; //console.log("Connected successfully...!"); //console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); sql = "SHOW TRIGGERS"; con.query(sql); console.log("show trigger query executed successfully..!"); console.log("Triggers: "); sql = "SHOW TRIGGERS"; con.query(sql, function(err, result){ if (err) throw err; console.log(result); }); });

输出

生成的输出如下:

show trigger query executed successfully..!
Triggers:
[
  {
    Trigger: 'testTrigger',
    Event: 'UPDATE',
    Table: 'student',
    Statement: "INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score",
    Timing: 'AFTER',
    Created: 2023-08-01T05:21:18.540Z,
    sql_mode: 'IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION',
    Definer: 'root@localhost',
    character_set_client: 'utf8mb4',
    collation_connection: 'utf8mb4_unicode_ci',
    'Database Collation': 'utf8mb4_0900_ai_ci'
  }
] 
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ShowTrigger { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "Create Trigger sample_trigger BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END"; st.execute(sql); System.out.println("Trigger created successfully...!"); String sql1 = "SHOW TRIGGERS"; rs = st.executeQuery(sql1); System.out.println("Triggers: "); while(rs.next()) { String triggers = rs.getNString(1); System.out.println(triggers); } }catch(Exception e) { e.printStackTrace(); } } }

输出

获得的输出如下所示:

Trigger created successfully...!
Triggers: 
sample_trigger
testTrigger   
import mysql.connector # Establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) table_name = 'Student' trigger_name = 'sample_trigger' # Creating a cursor object cursorObj = connection.cursor() # show trigger show_triggers_query = "SHOW TRIGGERS" cursorObj.execute(show_triggers_query) result = cursorObj.fetchall() print("Triggers in the database:") for row in result: print(row) # close the cursor and connection cursorObj.close() connection.close()

输出

以上代码的输出如下:

Triggers in the database:
('sample_trigger', 'INSERT', 'student', b'BEGIN\n    IF NEW.Score < 0 THEN\n        SET NEW.Score = 0;\n    END IF;\nEND', 'BEFORE', datetime.datetime(2023, 7, 31, 11, 38, 5, 880000), {'STRICT_TRANS_TABLES', 'NO_ENGINE_SUBSTITUTION'}, 'root@localhost', 'utf8mb4', 'utf8mb4_0900_ai_ci', 'utf8mb4_0900_ai_ci')
广告