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

列印頁面