MySQL - SHOW TRIGGERS 语句
MySQL 中的触发器是类似于存储过程的存储程序。它们可以在与事件关联的表、模式、视图和数据库上创建，并且每当事件发生时都会调用相应的触发器。
MySQL 提供了一个语句来列出数据库中现有的所有触发器。了解触发器信息在创建新触发器时非常有用，这样用户就不会对多个触发器使用相同的名称。
在 MySQL 中显示触发器
在 MySQL 中，SHOW TRIGGERS 语句用于显示当前数据库中定义的所有触发器的信息。
语法
以下是 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 子句来检索符合指定条件的触发器信息。
示例
以下查询检索当前数据库中事件为更新的触发器 -
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/Python 的 execute() 函数执行 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"
});
//连接到 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 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)
#关闭游标和连接
cursorObj.close()
connection.close()
输出
以下是上述代码的输出 -
Triggers in the database:
('sample_trigger', 'INSERT', 'student', b'BEGIN
IF NEW.Score < 0 THEN
SET NEW.Score = 0;
END IF;
END', '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')
