觸發器 (Trigger) 是一種自動執行的程式，它會在表格上執行指定的INSERT、UPDATE或DELETE操作時被觸發。觸發器可以用來自動執行一些維護數據一致性的任務，例如驗證數據、記錄變更日誌等。
範例 :
如何在XAMPP中使用MySQL Trigger
(範例) MySQL Stored Procedure/Stored Function/Trigger
實作 : 工讀生薪資管理系統
Trigger的語法
在MySQL中創建Trigger的語法如下：
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
-- 觸發器執行的SQL語句
statement1;
statement2;
statement3;
END;
trigger_name：觸發器的名稱。
BEFORE | AFTER：指定觸發器是在操作前還是操作後執行。
INSERT | UPDATE | DELETE：指定觸發器的觸發事件類型。
table_name：觸發器所關聯的表名。
FOR EACH ROW：指定觸發器對每一行數據操作都執行一次。
BEGIN ... END：在這之間寫入觸發器的執行語句。
作用範圍：
觸發器只能在特定的表上生效，無法跨表生效。每個表格的同一操作只能有一個BEFORE和一個AFTER觸發器。
上下文變數：
在觸發器中，可以使用NEW和OLD兩個虛擬表來存取被操作的資料。
NEW代表插入或更新後的新資料。
OLD代表刪除或更新前的舊資料。
限制：
(1) 觸發器不能再觸發另一個觸發器。
(2) 被觸發器觸發的表單，盡量不再去進行insert/update/delete。
在觸發器中對觸發表進行 INSERT、UPDATE 或 DELETE 操作可能會導致錯誤。
以下是 MySQL 文檔中關於這個限制的說明:
"A trigger is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the trigger."
翻譯過來就是: "觸發器不允許修改已經被調用觸發器的語句所使用(用於讀取或寫入)的表。"
會出現如下的錯誤 :
#1442 - Can't update table 'salary' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
這個限制的目的是為了避免出現無限遞迴調用或其他意外的數據完整性問題。當觸發器嘗試修改正在被使用的表時，它可能會干擾原始的 SQL 語句，導致不可預期的行為和錯誤。
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2),
last_modified TIMESTAMP
);
例如以下觸發器範例是會產生#1442 錯誤的 :
DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
-- 嘗試再次更新同一張表
UPDATE employees SET last_modified = NOW() WHERE id = NEW.id;
END//
DELIMITER ;
但是以下觸發器範例是允許的
DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
-- 檢查salary是否發生變化
IF NEW.salary != OLD.salary THEN
-- 更新last_modified欄位
SET NEW.last_modified = NOW();
END IF;
END //
DELIMITER ;
(3) 觸發器中不能執行動態SQL（即使用PREPARE和EXECUTE語句）。
動態SQL是什麼? 就是SQL指令中是由變數組成的，例如 :
假設有個表單是 employee
SET @table_name='employee';
SET @query = CONCAT('SELECT * ', ' FROM ', @table_name);
PREPARE stmt FROM @query;
EXECUTE stmt;
性能考量：
由於觸發器會在每次指定操作時執行，因此要避免在觸發器中執行耗時的操作，以免影響系統性能。應謹慎設計觸發器，以防止無限遞歸觸發（即觸發器觸發自己）。
除錯與維護：
要經常檢查觸發器的執行效果，確保其行為符合預期。記錄觸發器的變更歷史，以便於維護和除錯。
範例
假設我們有兩個表：orders（訂單）和inventory（庫存）。每當有新訂單插入orders表時，我們希望自動減少inventory表中對應產品的庫存數量。
CREATE TABLE inventory (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
quantity INT
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_id INT,
order_quantity INT,
order_date DATE
);
我們插入一些初始資料到inventory表單：
INSERT INTO inventory (product_id, product_name, quantity) VALUES (1, 'Laptop', 50), (2, 'Smartphone', 100), (3, 'Tablet', 75);
我們創建一個觸發器，當有新訂單插入orders表時，會自動更新inventory表中的庫存數量：
DELIMITER //
CREATE TRIGGER reduce_inventory
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE inventory
SET quantity = quantity - NEW.order_quantity
WHERE product_id = NEW.product_id;
END;
//
DELIMITER ;
如果要更精準的先檢查庫存，觸發器如下 :
0 留言