MySQL - 更新前觸發器

Table of content
上一頁
下一頁

MySQL 觸發器分為兩種型別：更新前觸發器和更新後觸發器，用於各種 SQL 操作，例如插入、刪除和更新。正如我們在前面章節中已經學習的那樣，更新後觸發器在資料庫錶行中的值被更新後立即執行。在這裡，讓我們進一步瞭解更新前觸發器。

MySQL 更新前觸發器

更新前觸發器是 MySQL 資料庫支援的行級觸發器。它是一種特殊的儲存過程，在資料庫錶行中的值被更新之前自動執行。

行級觸發器是一種在每次修改行時都會觸發的觸發器。簡單來說，對於在表中進行的每一次事務（例如插入、刪除、更新），都會自動執行一個觸發器。

每當在資料庫中執行 UPDATE 語句時，觸發器都會首先被觸發，然後才是更新後的值。

語法

以下是建立 MySQL 中更新前觸發器的語法：

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

示例

讓我們首先建立一個名為 USERS 的表，其中包含應用程式使用者的詳細資訊。使用以下 CREATE TABLE 查詢來執行此操作：

CREATE TABLE USERS(
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(100) NOT NULL,
   AGE INT NOT NULL,
   birthDATE VARCHAR(100),
   PRIMARY KEY(ID)
);

使用常規 INSERT 語句將值插入 USERS 表中，如下所示：

INSERT INTO USERS (Name, Age, birthDATE) VALUES 
('Sasha', 23, '24/06/1999');
('Alex', 21, '12/01/2001');

USERS 表建立如下：

ID 姓名 年齡 出生日期
1 Sasha 23 24/06/1999
2 Alex 21 12/01/2001

建立觸發器

使用以下 CREATE TRIGGER 語句，在 USERS 表上建立一個名為 'before_update_trigger' 的新觸發器，使用 SQLSTATE 顯示自定義錯誤，如下所示：

DELIMITER //
CREATE TRIGGER before_update_trigger 
BEFORE UPDATE ON USERS FOR EACH ROW
BEGIN
   IF NEW.AGE < 0 
   THEN SIGNAL SQLSTATE '45000' 
   SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END //
DELIMITER ;

使用常規 UPDATE 語句更新 USERS 表的值：

UPDATE USERS SET AGE = -1 WHERE NAME = 'Sasha';

輸出

此查詢的輸出將顯示一個錯誤：

ERROR 1644 (45000): Age Cannot be Negative

使用客戶端程式的更新前觸發器

我們還可以使用客戶端程式而不是直接使用 SQL 查詢來執行更新前觸發器。

語法

PHP NodeJS Java Python

要在 PHP 程式中執行更新前觸發器，我們需要使用 **mysqli** 函式 **query()** 來執行 CREATE TRIGGER 語句，如下所示：

$sql = "CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END";
$mysqli->query($sql);

要在 JavaScript 程式中執行更新前觸發器，我們需要使用 **mysql2** 庫的 **query()** 函式來執行 CREATE TRIGGER 語句，如下所示：

sql = `CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END`;
con.query(sql);

要在 Java 程式中執行更新前觸發器，我們需要使用 **JDBC** 函式 **execute()** 來執行 CREATE TRIGGER 語句，如下所示：

String sql = "CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END";
statement.execute(sql);

要在 Python 程式中執行更新前觸發器，我們需要使用 **MySQL Connector/Python** 的 **execute()** 函式來執行 CREATE TRIGGER 語句，如下所示：

beforeUpdate_trigger_query = 'CREATE TRIGGER {trigger_name}
BEFORE UPDATE ON {sample}
FOR EACH ROW
BEGIN
IF NEW.AGE < 0
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age Cannot be Negative'
END IF
END'
cursorObj.execute(beforeUpdate_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 before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END";
if($mysqli->query($sql)){
   printf("Trigger created successfully...!\n");
}
$q = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
$result = $mysqli->query($q);
if ($result == true) {
   printf("Record updated successfully...!\n");
}
if($mysqli->error){
   printf("Error message: " , $mysqli->error);
}
$mysqli->close();

輸出

獲得的輸出如下：

Trigger created successfully...!
PHP Fatal error:  Uncaught mysqli_sql_exception: Age Cannot be Negative

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 before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW
   BEGIN
   IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
   END IF;
   END`;
   con.query(sql);
   console.log("Before Update query executed successfully..!");
   sql = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
   con.query(sql);
   console.log("Table records: ")
   sql = "SELECT * FROM Sample";
   con.query(sql, function(err, result){
      if (err) throw err;
      console.log(result);
   });
});

輸出

產生的輸出如下：

Error: Age Cannot be Negative

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BeforeUpdateTrigger {
    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 = "SELECT * FROM SAMPLE";
            rs = st.executeQuery(sql);
            System.out.println("Sample table records before update: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String birth_date = rs.getString("birthDATE");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Birth_date: " + birth_date);
            }
            //lets create trigger on student table
            String sql1 = "CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
            END IF;
            END";
            st.execute(sql1);
            System.out.println("Triggerd Created successfully...!");
            //lets update table records
            String sql3 = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
            st.execute(sql3);
            //let print SAMPLE table records
            String sql4 = "SELECT * FROM SAMPLE";
            rs = st.executeQuery(sql4);
            System.out.println("Sample table records after update: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String birth_date = rs.getString("birthDATE");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Birth_date: " + birth_date);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

輸出

獲得的輸出如下所示：

Sample table records before update: 
Id: 1, Name: Sasha, Age: 23, Birth_date: 24/06/1999
Id: 2, Name: Alex, Age: 21, Birth_date: 12/01/2001  

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()
table_name = 'Sample'
trigger_name = 'before_update_trigger'
beforeUpdate_trigger_query = f'''
CREATE TRIGGER {trigger_name}
BEFORE UPDATE ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.AGE < 0
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END
'''
cursorObj.execute(beforeUpdate_trigger_query)
print(f"BEFORE UPDATE Trigger '{trigger_name}' is created successfully.")
connection.commit()
# Update the "AGE" column
update_query = "UPDATE Sample SET AGE = -1 WHERE NAME = 'Sasha'"
cursorObj.execute(update_query)
print("Update query executed successfully.")
# close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

輸出

以上程式碼的輸出如下：

BEFORE UPDATE Trigger 'before_update_trigger' is created successfully.
Traceback (most recent call last):
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 633, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Age Cannot be Negative

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Lenovo\Desktop\untitled.py", line 29, in 
    cursorObj.execute(update_query)
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 330, in execute
    result = self._cnx.cmd_query(
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\opentelemetry\context_propagation.py", line 77, in wrapper
    return method(cnx, *args, **kwargs)
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 641, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 1644 (45000): Age Cannot be Negative
列印頁面
上一頁
下一頁
廣告