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://: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.
列印頁面
上一頁
下一頁
廣告