MySQL - SHOW TRIGGERS

Table of content

在 MySQL 中显示触发器
使用 FROM 或 IN 子句
使用 WHERE 子句
使用客户端程序显示触发器

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)

使用 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 NodeJS Java Python

要通过 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/Python 的 execute() 函数来执行 SHOW TRIGGERS 语句，如下所示：

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

示例

以下是程序：

PHP NodeJS Java Python

$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://127.0.0.1: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')

打印页面