笙亿网络策划

MySQL 怎么查看触发器？show trigger 命令用法详解？

约 20 分钟读完 1 阅
文章导读
上一个 测验 下一个 MySQL 中的 Triggers 是类似于 procedure 的存储程序。这些程序可以创建在 table、schema、view 和 database 上，与某个 event 相关联，每当 event 发生时，相应的 trigger 就会被调用。
📋 目录
  1. 壹 MySQL 中显示 Triggers
  2. 贰 使用 FROM 或 IN 子句
  3. 叁 使用 WHERE 子句
  4. 肆 使用客户端程序显示触发器
A A

MySQL - SHOW TRIGGERS

目录
  • MySQL 中显示 Triggers
  • 使用 FROM 或 IN 子句
  • 使用 WHERE 子句
  • 使用客户端程序显示 Trigger
上一个
测验
下一个

MySQL 中的 Triggers 是类似于 procedure 的存储程序。这些程序可以创建在 table、schema、view 和 database 上，与某个 event 相关联，每当 event 发生时，相应的 trigger 就会被调用。

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

MySQL 中显示 Triggers

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

语法

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

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

示例

在本示例中，我们使用下面的查询创建一个名为 STUDENT 的 table −

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

下面的查询创建一个名为 sample_trigger 的 trigger，如果向 table 中插入负数 score 值，它会将 score 值设置为 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 子句创建了另一个 trigger −

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 ;

下面的查询显示当前数据库中现有的 triggers −

SHOW TRIGGERS \G;

输出

triggers 列表将显示如下 −

*************************** 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.
');

// 创建一个触发器
$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);
}

// 显示创建的触发器详细信息
$sql = "SHOW TRIGGERS"; 
$res = $mysqli->query($sql);
if ($res) {
    while ($row = $res->fetch_assoc()) {
        // 打印触发器详细信息
        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"
});

 //连接到 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
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'Student'
trigger_name = 'sample_trigger'
# 创建游标对象
cursorObj = connection.cursor()
# 显示触发器
show_triggers_query = "SHOW TRIGGERS"
cursorObj.execute(show_triggers_query)
result = cursorObj.fetchall()
print("Triggers in the database:")
for row in result:
    print(row)
# 关闭游标和连接
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')