MySQL - 建立觸發器

Table of content
上一個
下一個

觸發器通常定義為對事件的響應。例如，當我們將滑鼠指標懸停在網站的下拉選單上時，就會顯示一組用於瀏覽該網站的選項。這裡，滑鼠指標的懸停是一個事件，而下拉選單中選項的顯示是觸發器執行的結果。這個概念也引入了 MySQL。

MySQL 中的觸發器是類似於過程的儲存程式。這些可以建立在與事件關聯的表、模式、檢視和資料庫上，並且每當事件發生時，相應的觸發器就會被呼叫。

觸發器實際上是編寫為響應以下任何事件而執行的 -

  • 資料庫操作 (DML) 語句 (DELETE、INSERT 或 UPDATE)
  • 資料庫定義 (DDL) 語句 (CREATE、ALTER 或 DROP)。
  • 資料庫操作 (SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN)。

在 MySQL 中建立觸發器

您可以使用 CREATE TRIGGER 語句建立觸發器。

語法

以下是 MySQL CREATE TRIGGER 語句的語法。

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
...
END;

其中，

  • trigger_name 是您需要建立的觸發器的名稱
  • trigger_time 是觸發器啟用的時間
  • trigger_event 可以是 INSERT、UPDATE 或 DELETE。此事件會導致觸發器被呼叫。
  • table_name 是與觸發器關聯的表的名稱。

示例

假設我們建立了一個名為 student 的表，如下所示 -

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

以下查詢建立一個觸發器，如果您輸入的值小於 0 作為分數，它將分數值設定為 0。

DELIMITER //
CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW
BEGIN
IF NEW.Score < 0 THEN SET NEW.Score = 0;
END IF;
END //
DELIMITER ;

驗證

如果您嘗試在 student 表中插入記錄，並且如果您使用小於 0 的值作為年齡，它將自動設定為 0。

INSERT INTO STUDENT VALUES
('Jeevan', 22, 8),
('Raghav', 26, -3),
('Pooja', 21, -9),
('Devi', 30, 9);

建立的 STUDENT 表將包含以下記錄 -

姓名 年齡 分數
Jeevan 22 8
Raghav 26 0
Pooja 21 0
Devi 30 9

正如我們所看到的，表中沒有插入負值，因為它們都被替換為零。

使用客戶端程式建立觸發器

我們還可以使用客戶端程式建立觸發器。

語法

PHP NodeJS Java Python

要透過 PHP 程式建立觸發器，我們需要使用名為 query() 的 mysqli 函式執行 CREATE 語句，如下所示 -

$sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score";
$mysqli->query($sql);

要透過 JavaScript 程式建立觸發器，我們需要使用 mysql2 庫的 query() 函式執行 CREATE 語句，如下所示 -

sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score"
con.query(sql);

要透過 Java 程式建立觸發器，我們需要使用 JDBC 函式 execute() 執行 CREATE 語句，如下所示 -

String sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score";
statement.execute(sql);

要透過 python 程式建立觸發器，我們需要使用 MySQL Connector/Python 的 execute() 函式執行 CREATE 語句，如下所示 -

create_trigger_query = 'CREATE TRIGGER sample_trigger
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
   IF NEW.Score < 0 THEN
      SET NEW.Score = 0;
   END IF;
END'
cursorObj.execute(create_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 testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score";
if($mysqli->query($sql)){
   printf("Trigger created successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

輸出

獲得的輸出如下 -

Trigger created successfully...!

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 testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score"
   con.query(sql);
   console.log("Trigger created successfully....!");
   sql = "SHOW TRIGGERS";
   con.query(sql, function(err, result){
      if (err) throw err;
      console.log(result);
   });
});

輸出

生成的輸出如下 -

Trigger created successfully....!
[
  {
    Trigger: 'testTrigger',
    Event: 'UPDATE',
    Table: 'student',
    Statement: "INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score",
    Timing: 'AFTER',
    Created: 2023-08-01T05:21:18.540Z,
    sql_mode: 'IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION',
    Definer: 'root@localhost',
    character_set_client: 'utf8mb4',
    collation_connection: 'utf8mb4_unicode_ci',
    'Database Collation': 'utf8mb4_0900_ai_ci'
  }
] 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CreateTrigger {
   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...!");
         String sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score";
         st.execute(sql);
         System.out.print("Trigger created successfully....!");
            
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

輸出

獲得的輸出如下所示 -

Trigger created successfully....!

import mysql.connector
#Establishing the connection
connection = mysql.connector.connect(
   host='localhost',
   user='root',
   password='password',
   database='tut'
)
table_name = 'Student'
trigger_name = 'sample_trigger'
# Creating a cursor object
cursorObj = connection.cursor()
# Create table
create_table_query = '''
CREATE TABLE Student(
   Name VARCHAR(35),
   age INT,
   Score INT
)
'''
cursorObj.execute(create_table_query)
print("The table is created successfully!")
# Creating a trigger
create_trigger_query = f'''CREATE TRIGGER {trigger_name}
BEFORE INSERT ON {table_name}
FOR EACH ROW
BEGIN
   IF NEW.Score < 0 THEN
      SET NEW.Score = 0;
   END IF;
END'''
cursorObj.execute(create_trigger_query)
print(f"Trigger '{trigger_name}' is created successfully.")
# Commit the changes and close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

輸出

以下是上述程式碼的輸出 -

The table is created successfully!
Trigger 'sample_trigger' is created successfully.
列印頁面
上一個
下一個
廣告