MySQL - BEFORE INSERT Trigger 插入前触发器
正如我们已经了解的，触发器被定义为对所执行事件的响应。在 MySQL 中，触发器被称为特殊的存储过程，因为它不需要像其他存储过程那样显式调用。触发器会在所需事件触发时自动执行。这些事件包括执行 SQL 语句，例如 INSERT、UPDATE 和 DELETE 等。
MySQL 插入前触发器
插入前触发器是 MySQL 数据库支持的行级触发器。顾名思义，此触发器会在将值插入数据库表之前执行。
行级触发器是一种在每次修改行时触发的触发器。简单来说，对于表中的每一个事务(例如插入、删除、更新)，都会自动触发一个触发器。
每当在数据库中查询 INSERT 语句时，都会首先自动执行此触发器，然后才将值插入表中。
语法
以下是在 MySQL 中创建 BEFORE INSERT 触发器的语法 -
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN -- trigger body END;
示例
让我们看一个演示 BEFORE INSERT 触发器的示例。在这里，我们将创建一个新表 STUDENT，其中包含某个机构的学生详细信息，并使用以下查询 -
CREATE TABLE STUDENT( Name varchar(35), Age INT, Score INT, Grade CHAR(10) );
使用以下 CREATE TRIGGER 语句，在 STUDENT 表上创建一个新的触发器 sample_trigger。在这里，我们将检查每个学生的分数并为其分配合适的等级。
DELIMITER // CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL'; ELSE SET NEW.Grade = 'PASS'; END IF; END // DELIMITER ;
使用常规 INSERT 语句将值插入 STUDENT 表，如下所示 -
INSERT INTO STUDENT VALUES
('John', 21, 76, NULL),
('Jane', 20, 24, NULL),
('Rob', 21, 57, NULL),
('Albert', 19, 87, NULL);
验证
要验证触发器是否已执行，请使用 SELECT 语句显示 STUDENT 表 -
| Name | Age | Score | Grade |
|---|---|---|---|
| John | 21 | 76 | PASS |
| Jane | 20 | 24 | FAIL |
| Rob | 21 | 57 | PASS |
| Albert | 19 | 87 | PASS |
使用客户端程序执行插入前触发器
除了创建或显示触发器之外，我们还可以使用客户端程序执行"插入前触发器"语句。
语法
要通过 PHP 程序执行插入前触发器，我们需要使用 mysqli 函数 query() 执行 CREATE TRIGGER 语句，如下所示 -
$sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT"." FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL'; ELSE SET NEW.Grade = 'PASS'; END IF; END"; $mysqli->query($sql);
要通过 JavaScript 程序执行插入前触发器，我们需要使用 mysql2 库的 query() 函数执行 CREATE TRIGGER 语句，如下所示 -
sql = `Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL'; ELSE SET NEW.Grade = 'PASS'; END IF; END`; con.query(sql);
要通过 Java 程序执行插入前触发器，我们需要使用 JDBC 函数 execute() 执行 CREATE TRIGGER 语句，如下所示 -
String sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL'; ELSE SET NEW.Grade = 'PASS'; END IF; END"; statement.execute(sql);
要通过 Python 程序执行插入前触发器，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 CREATE TRIGGER 语句，如下所示 -
beforeInsert_trigger_query = 'CREATE TRIGGER sample_trigger BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL'; ELSE SET NEW.Grade = 'PASS'; END IF; END' cursorObj.execute(drop_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.
');
$sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT"."
FOR EACH ROW
BEGIN
IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END";
if($mysqli->query($sql)){
printf("Trigger created successfully...!
");
}
$q = "INSERT INTO STUDENT VALUES ('John', 21, 76, NULL)";
$result = $mysqli->query($q);
if ($result == true) {
printf("Record inserted successfully...!
");
}
$q1 = "SELECT * FROM STUDENT";
if($r = $mysqli->query($q1)){
printf("Select query executed successfully...!");
printf("Table records(Verification):
");
while($row = $r->fetch_assoc()){
printf("Name: %s, Age: %d, Score %d, Grade %s",
$row["Name"],
$row["Age"],
$row["Score"],
$row["Grade"]);
printf("
");
}
}
if($mysqli->error){
printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
输出
获得的输出如下 -
Trigger created successfully...! Record inserted successfully...! Select query executed successfully...!Table records(Verification): Name: Jane, Age: 20, Score 24, Grade FAIL Name: John, Age: 21, Score 76, Grade PASS
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 = `Create Trigger sample_trigger BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END`;
con.query(sql);
console.log("Before Insert query executed successfully..!");
sql = "INSERT INTO STUDENT VALUES ('Aman', 22, 86, NULL)";
con.query(sql);
console.log("Record inserted successfully...!");
console.log("Table records: ")
sql = "SELECT * FROM STUDENT";
con.query(sql, function(err, result){
if (err) throw err;
console.log(result);
});
});
输出
生成的输出如下 -
Before Insert query executed successfully..!
Record inserted successfully...!
Table records:
[
{ Name: 'Jane', Age: 20, Score: 24, Grade: 'FAIL' },
{ Name: 'John', Age: 21, Score: 76, Grade: 'PASS' },
{ Name: 'John', Age: 21, Score: 76, Grade: 'PASS' },
{ Name: 'Aman', Age: 22, Score: 86, Grade: 'PASS' },
{ Name: 'Aman', Age: 22, Score: 86, Grade: 'PASS' }
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BeforeInsertTrigger {
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...!");
//lets create trigger on student table
String sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END";
st.execute(sql);
System.out.println("Triggerd Created successfully...!");
//lets insert some records into student table
String sql1 = "INSERT INTO STUDENT VALUES ('John', 21, 76, NULL), ('Jane', 20, 24, NULL), ('Rob', 21, 57, NULL), ('Albert', 19, 87, NULL)";
st.execute(sql1);
//let print table records
String sql2 = "SELECT * FROM STUDENT";
rs = st.executeQuery(sql2);
while(rs.next()) {
String name = rs.getString("name");
String age = rs.getString("age");
String score = rs.getString("score");
String grade = rs.getString("grade");
System.out.println("Name: " + name + ", Age: " + age + ", Score: " + score + ", Grade: " + grade);
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
输出
获得的输出如下所示 -
Triggerd Created successfully...! Name: John, Age: 21, Score: 76, Grade: PASS Name: Jane, Age: 20, Score: 24, Grade: FAIL Name: Rob, Age: 21, Score: 57, Grade: PASS Name: Albert, Age: 19, Score: 87, Grade: PASS
import mysql.connector
# 建立连接
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
trigger_name = 'sample_trigger'
table_name = 'Student'
beforeInsert_trigger_query = f'''CREATE TRIGGER {trigger_name}
BEFORE INSERT ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.Score < 35
THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END'''
cursorObj.execute(beforeInsert_trigger_query)
print(f"BEFORE INSERT Trigger '{trigger_name}' is created successfully.")
# 提交更改并关闭游标和连接
connection.commit()
cursorObj.close()
connection.close()
输出
以下是上述代码的输出 -
BEFORE INSERT Trigger 'sample_trigger' is created successfully.
