MySQL - BEFORE INSERT Trigger 插入前触发器

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 NodeJS Java Python

要通过 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)

示例

以下是程序 -

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 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.

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - BEFORE INSERT Trigger 插入前触发器

MySQL 插入前触发器

语法

示例

验证

使用客户端程序执行插入前触发器

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！