MySQL - 显示触发器

MySQL - 列出触发器

触发器是 MySQL 中一种特殊类型的存储程序，它在响应特定表上的某些事件时自动执行或“触发”。这些事件是 INSERT、UPDATE 或 DELETE 操作。随着数据库模式的增长，拥有列出和检查您已创建的触发器的方法变得至关重要。

MySQL 提供了两种主要方法来查看现有触发器：SHOW TRIGGERS 命令和查询 INFORMATION_SCHEMA.TRIGGERS 表。了解这两种方法将使您在管理数据库时拥有更大的灵活性。

方法 1：使用 SHOW TRIGGERS 语句

SHOW TRIGGERS 语句是一个简单直接的命令，用于显示数据库中定义的触发器信息。

语法

SHOW TRIGGERS [{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

示例

首先，让我们创建一张表和几个触发器来使用。我们将创建一个 AuditLog 表来记录 Employees 表的更改。

CREATE TABLE Employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

CREATE TABLE AuditLog (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    action VARCHAR(20),
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 用于记录薪资更新的触发器
DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO AuditLog(employee_id, action, old_salary, new_salary)
        VALUES(OLD.id, 'SALARY_UPDATE', OLD.salary, NEW.salary);
    END IF;
END;//
DELIMITER ;

-- 用于记录新员工入职的触发器
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog(employee_id, action, new_salary)
    VALUES(NEW.id, 'NEW_HIRE', NEW.salary);
END;//
DELIMITER ;

现在，要查看我们在当前数据库中刚刚创建的触发器，请使用 SHOW TRIGGERS 命令。\G 分隔符将输出垂直格式化，以提高可读性。

SHOW TRIGGERS\G

输出解释

输出将列出每个触发器的详细信息：

*************************** 1. row ***************************
             Trigger: after_employee_insert
               Event: INSERT
               Table: Employees
           Statement: BEGIN
    INSERT INTO AuditLog(employee_id, action, new_salary)
    VALUES(NEW.id, 'NEW_HIRE', NEW.salary);
END
              Timing: AFTER
             Created: ...
            sql_mode: ...
             Definer: 'user'@'host'
character_set_client: ...
collation_connection: ...
  Database Collation: ...
*************************** 2. row ***************************
             Trigger: before_employee_update
               Event: UPDATE
               Table: Employees
           Statement: BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO AuditLog(employee_id, action, old_salary, new_salary)
        VALUES(OLD.id, 'SALARY_UPDATE', OLD.salary, NEW.salary);
    END IF;
END
              Timing: BEFORE
             Created: ...
            sql_mode: ...
             Definer: 'user'@'host'
character_set_client: ...
collation_connection: ...
  Database Collation: ...
2 rows in set (0.00 sec)

过滤 SHOW TRIGGERS 输出

您可以使用 FROM/IN 来过滤特定数据库的结果，或使用 LIKE/WHERE 来过滤更复杂的条件。

使用 FROM 或 IN

要显示特定数据库（例如 company_db）中的触发器，无论您当前使用哪个数据库：

SHOW TRIGGERS FROM company_db;

使用 LIKE 或 WHERE

LIKE 子句根据触发器名称进行过滤，而 WHERE 子句允许对任何输出列应用更复杂的条件。WHERE 通常更灵活。

-- 使用 LIKE 查找名称中包含 'before' 的触发器
SHOW TRIGGERS LIKE '%before%';

-- 使用 WHERE 查找 'Employees' 表上的所有触发器
SHOW TRIGGERS WHERE `Table` = 'Employees';

-- 使用 WHERE 查找在 UPDATE 事件上触发的所有触发器
SHOW TRIGGERS WHERE Event = 'UPDATE'\G

方法 2：查询 INFORMATION_SCHEMA

INFORMATION_SCHEMA 是一组只读表，提供有关数据库本身的元数据。查询其中的 TRIGGERS 表是获取触发器信息的标准 SQL 方法，通常对于脚本编写和报告更灵活。

示例查询

此查询检索特定数据库中触发器的关键信息，类似于 SHOW TRIGGERS。

SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION AS `Event`,
    EVENT_OBJECT_TABLE AS `Table`,
    ACTION_TIMING AS `Timing`,
    ACTION_STATEMENT AS `Statement`
FROM
    INFORMATION_SCHEMA.TRIGGERS
WHERE
    TRIGGER_SCHEMA = 'your_database_name';

这种方法允许您使用 SELECT 的全部功能，包括联接 (joins)、复杂过滤和排序，这在 SHOW TRIGGERS 中是不可能实现的。

比较 SHOW TRIGGERS 和 INFORMATION_SCHEMA

方面	SHOW TRIGGERS	INFORMATION_SCHEMA.TRIGGERS
类型	MySQL 特有命令。	标准 SQL 方法，更具可移植性。
灵活性	使用 LIKE 和 WHERE 进行有限过滤。	完整的 SELECT 功能（联接、子查询、排序等）。
用例	快速、交互式检查触发器。	自动化脚本、报告、复杂元数据查询。
权限	需要 TRIGGER 权限。	对拥有 INFORMATION_SCHEMA 适当访问权限的用户可访问。

使用现代客户端应用程序列出触发器

以下是您如何以编程方式列出触发器，使用更灵活的 INFORMATION_SCHEMA 方法。

Node.js (mysql2)
Python (mysql-connector-python)
Java (JDBC)
PHP (mysqli)


在 Node.js 中使用 `mysql2/promise`，我们可以获取并显示触发器详细信息。

```javascript
// 必需：npm install mysql2
const mysql = require('mysql2/promise');

async function listTriggers(dbName) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_password',
      database: dbName
    });

    const sql = `
      SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
      FROM INFORMATION_SCHEMA.TRIGGERS
      WHERE TRIGGER_SCHEMA = ?
    `;

    const [rows] = await connection.execute(sql, [dbName]);

    if (rows.length === 0) {
        console.log(`No triggers found in database '${dbName}'.`);
        return;
    }

    console.log(`Triggers in database '${dbName}':`);
    rows.forEach(trigger => {
        console.log(
          `- ${trigger.TRIGGER_NAME} (${trigger.ACTION_TIMING} ${trigger.EVENT_MANIPULATION} on ${trigger.EVENT_OBJECT_TABLE})`
        );
    });

  } catch (error) {
    console.error(`An error occurred: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

listTriggers('your_database_name');

在 Python 中，您可以将结果作为字典列表获取，以便于处理。

// 必需：pip install mysql-connector-python
import mysql.connector

def list_triggers(db_name):
    config = {
        'user': 'root',
        'password': 'your_password',
        'host': 'localhost',
        'database': db_name
    }
    sql = """
        SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
        FROM INFORMATION_SCHEMA.TRIGGERS
        WHERE TRIGGER_SCHEMA = %s
    """
    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql, (db_name,))
                triggers = cursor.fetchall()

                if not triggers:
                    print(f"No triggers found in database '{db_name}'.")
                    return

                print(f"Triggers in database '{db_name}':")
                for trigger in triggers:
                    print(f"- {trigger['TRIGGER_NAME']} ({trigger['ACTION_TIMING']} {trigger['EVENT_MANIPULATION']} on {trigger['EVENT_OBJECT_TABLE']})")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")

list_triggers('your_database_name')

Java 的 JDBC 可用于查询 INFORMATION_SCHEMA 并打印结果。

import java.sql.*;

public class ListTriggersExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String dbName = "your_database_name";
        String sql = "SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING " +
                       "FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL + dbName, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, dbName);
            ResultSet rs = pstmt.executeQuery();

            System.out.println("Triggers in database '" + dbName + "':");
            boolean found = false;
            while (rs.next()) {
                found = true;
                System.out.printf("- %s (%s %s on %s)%n",
                    rs.getString("TRIGGER_NAME"),
                    rs.getString("ACTION_TIMING"),
                    rs.getString("EVENT_MANIPULATION"),
                    rs.getString("EVENT_OBJECT_TABLE"));
            }
            if (!found) {
                System.out.println("No triggers found.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在 PHP 中，建议使用 mysqli 的预处理语句，以防止 SQL 注入。

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'your_password';
$dbname = 'your_database_name';

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING ".
       "FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = ?";

$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $dbname);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    echo "Triggers in database '$dbname':\n";
    while($row = $result->fetch_assoc()) {
        printf("- %s (%s %s on %s)\n",
            $row['TRIGGER_NAME'],
            $row['ACTION_TIMING'],
            $row['EVENT_MANIPULATION'],
            $row['EVENT_OBJECT_TABLE']);
    }
} else {
    echo "No triggers found in database '$dbname'.";
}

$stmt->close();
$mysqli->close();
?>