MySQL - 插入前触发器

Table of content
上一页
下一页

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

MySQL 插入前触发器

插入前触发器是 MySQL 数据库支持的行级触发器。顾名思义，此触发器在将值插入数据库表之前立即执行。

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

每当在数据库中查询 INSERT 语句时，此触发器都会首先自动执行，然后值才会插入表中。

语法

以下是创建 MySQL 中插入前触发器的语法：

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

示例

让我们来看一个演示插入前触发器的示例。在这里，我们使用以下查询创建一个名为 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 表：

姓名 年龄 分数 等级
John 21 76 及格
Jane 20 24 不及格
Rob 21 57 及格
Albert 19 87 及格

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

除了创建或显示触发器外，我们还可以使用客户端程序执行“插入前触发器”语句。

语法

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...!\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://127.0.0.1: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.
打印页面
上一页
下一页
广告