笙亿网络策划

MySQL Before Insert Trigger怎么创建和使用？

约 17 分钟读完 0 阅
文章导读
上一个 测验 下一个 正如我们已经了解的，Trigger 被定义为对执行事件的响应。在 MySQL 中，trigger 被称作特殊的存储过程，因为它不像其他存储过程那样需要显式调用。只要期望的事件触发，trigger 就会自动执行。这些事件包括执行 INSERT、UPDAT
📋 目录
  1. 一 MySQL Before Insert Trigger
  2. 二 使用客户端程序创建 Before Insert Trigger
A A

MySQL - Before Insert Trigger

目录
  • MySQL Before Insert Trigger
  • 使用客户端程序的 Before Insert Trigger
上一个
测验
下一个

正如我们已经了解的，Trigger 被定义为对执行事件的响应。在 MySQL 中，trigger 被称作特殊的存储过程，因为它不像其他存储过程那样需要显式调用。只要期望的事件触发，trigger 就会自动执行。这些事件包括执行 INSERT、UPDATE 和 DELETE 等 SQL 语句。

MySQL Before Insert Trigger

Before Insert Trigger 是 MySQL 数据库支持的行级 trigger。正如其名称所示，该 trigger 在将值插入数据库表之前立即执行。

行级 trigger 是一种每次修改一行时都会触发的 trigger。简单来说，对于表中进行的每笔事务（例如插入、删除、更新），都会自动执行一个 trigger。

每当在数据库中查询 INSERT 语句时，该 Trigger 会首先自动执行，然后才会将值插入表中。

语法

以下是在 MySQL 中创建 BEFORE INSERT trigger 的语法 −

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

示例

让我们看一个演示 BEFORE INSERT trigger 的示例。在此，我们使用以下查询创建一个名为 STUDENT 的新表，其中包含机构中学生的信息 −

CREATE TABLE STUDENT(
   Name varchar(35),
   Age INT,
   Score INT,
   Grade CHAR(10)
);

使用以下 CREATE TRIGGER 语句，在 STUDENT 表上创建一个名为 sample_trigger 的新 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);

验证

要验证 trigger 是否已执行，请使用 SELECT 语句显示 STUDENT 表 −

姓名 年龄 分数 等级
John 21 76 PASS
Jane 20 24 FAIL
Rob 21 57 PASS
Albert 19 87 PASS

使用客户端程序创建 Before Insert Trigger

除了创建或显示 trigger 外，我们还可以使用客户端程序执行 “Before Insert trigger” 语句。

语法

PHP NodeJS Java Python

要通过 PHP 程序执行 Before Insert Trigger，我们需要使用 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 程序执行 Before Insert Trigger，我们需要使用 mysql2 library 的 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 程序执行 Before Insert Trigger，我们需要使用 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 程序执行 Before Insert Trigger，我们需要使用 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...!\n");
   }
   $q = "INSERT INTO STUDENT VALUES ('John', 21, 76, NULL)";
   $result = $mysqli->query($q);
   if ($result == true) {
      printf("Record inserted successfully...!\n");
   }
   $q1 = "SELECT * FROM STUDENT";
   if($r = $mysqli->query($q1)){
      printf("Select query executed successfully...!");
      printf("Table records(Verification): \n");
      while($row = $r->fetch_assoc()){
          printf("Name: %s, Age: %d, Score %d, Grade %s",   
         $row["Name"],
         $row["Age"],
         $row["Score"],
         $row["Grade"]);
         printf("\n");  
      }
  }
   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"
});

 //Connecting to 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
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
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.")
# commit the changes and close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

输出

上述代码的输出如下 −

BEFORE INSERT Trigger 'sample_trigger' is created successfully.