MySQL 提供了數種進階功能來擴展資料庫的邏輯處理能力，其中 Trigger（觸發器）、Stored Function（儲存函式） 與 Stored Procedure（儲存程序） 是三種常用的工具，分別用於不同的情境，讓資料庫更具靈活性與自動化能力。
關於Trigger (觸發器)、Stored Function (預存函數)、Stored Procedure (預存程序)，以下是之前的文章，可以先參考一下 :
實作練習：MySQL Trigger 觸發器是什麼？如何使用？
實作 : 練習SELECT、INSERT INTO、UPDATE、DELETE FROM以及Trigger的使用
(範例) MySQL Stored Procedure/Stored Function/Trigger
實作練習 : Stored Procedure + Stored Function + Trigger 的錯誤處理
實作 : 工讀生薪資管理系統
實作練習 : PHP + Stored Procedure + Stored Function + Trigger
實作練習 : PHP + Stored Procedure + Stored Function + Trigger (二)
實作練習 : PHP + Stored Procedure + Stored Function + Trigger (三)
Trigger (觸發器) :
觸發器是在資料庫表格上的程式，在事件發生（對資料列做新增/修改/刪除）時，資料庫會依照觸發條件（事件前/事件後）幫你執行預先儲存好的程式。因此觸發器建立之後，不需要呼叫，而是靠事件來觸發。
關於觸發器，需要知道以下幾個重點 :
(1) 觸發器作用的資料表，不要於觸發器內對該資料表再使用UPDATE/INSERT/DELETE。
(2) 觸發器只能在特定的表上生效，無法跨表生效。
(3) 每個表格的同一操作只能有一個BEFORE或一個AFTER觸發器。
(4) 觸發器不能再觸發另一個觸發器。
(5) 觸發器中不能執行動態SQL（即使用PREPARE和EXECUTE語句）。
(6) 要避免在觸發器中執行耗時的操作，以免影響系統性能。
(7) InnoDB支援Transaction及行級鎖(Row-level Lock)，比其他儲存引擎能夠維持資料一致性。
(8) 觸發器執行時不會返回結果，只能執行 SQL 邏輯。
建立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：在這之間寫入觸發器的執行語句，如果觸發器中只有一條語句，則可以省略BEGIN和END。
範例 :
-- 建立資料表單
CREATE TABLE my_trigger_test (
id INT AUTO_INCREMENT PRIMARY KEY,
myfield VARCHAR(10) NOT NULL,
last_modified TIMESTAMP);
-- 建立觸發器
DELIMITER //
CREATE TRIGGER before_table_update
BEFORE UPDATE ON my_trigger_test
FOR EACH ROW
BEGIN
IF NEW.myfield != OLD.myfield
THEN
SET NEW.last_modified = NOW();
END IF;
END; //
DELIMITER ;
如果Trigger寫成如下 :
-- 以下範例是錯誤的
DELIMITER //
CREATE TRIGGER before_table_update2
BEFORE UPDATE ON my_trigger_test
FOR EACH ROW
UPDATE my_trigger_test SET last_modified = NOW() WHERE id = NEW.id;
END; //
DELIMITER ;
當以上before_table_update2觸發器被執行時，就會發生#1442 錯誤 (如下圖) : 因為表單已經被觸發器使用，不能在觸發器的程式內再執行UPDATE。
當然，要在資料表單更新時紀錄最後的更新時間，也可以不必使用Trigger。只要在欄位屬性上宣告即可，因為表單已經建立，我們使用語法來修正 :
ALTER TABLE my_trigger_test
MODIFY last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
當然要記得刪除Trigger :
DROP TRIGGER before_table_update;
DROP TRIGGER before_table_update2;
然後再執行更新看看結果。
UPDATE my_trigger_test SET myfield = '00000';
就可以看到 last_modified 會自動更新了。
範例 :
假設有產品資料庫如下 (資料表的設計說明請參考這篇)
CREATE TABLE category (
category_no INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL );
CREATE TABLE product (
prod_no INT AUTO_INCREMENT PRIMARY KEY,
category_no INT,
prod_name VARCHAR(100) NOT NULL,
prod_price DECIMAL(10, 2) NOT NULL,
prod_unit VARCHAR(50),
prod_stock INT,
FOREIGN KEY (category_no) REFERENCES category(category_no) );
history_id INT AUTO_INCREMENT PRIMARY KEY,
operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
category_no_old INT,
category_no_new INT,
prod_name_old VARCHAR(100),
prod_name_new VARCHAR(100),
prod_price_old DECIMAL(10, 2),
prod_price_new DECIMAL(10, 2),
prod_unit_old VARCHAR(50),
prod_unit_new VARCHAR(50),
prod_stock_old INT,
prod_stock_new INT );
AFTER UPDATE ON product
FOR EACH ROW
CREATE TRIGGER after_product_insert
AFTER INSERT ON product
FOR EACH ROW
BEGIN
INSERT INTO product_history ( operation_type, operation_time, prod_no, category_no_new, prod_name_new, prod_price_new, prod_unit_new, prod_stock_new ) VALUES ( 'INSERT', NOW(), NEW.prod_no, NEW.category_no, NEW.prod_name, NEW.prod_price, NEW.prod_unit, NEW.prod_stock );
END; //
DELIMITER ;
CREATE TRIGGER after_product_delete
AFTER DELETE ON product
FOR EACH ROW
BEGIN
INSERT INTO product_history ( operation_type, operation_time, prod_no, category_no_old, prod_name_old, prod_price_old, prod_unit_old, prod_stock_old ) VALUES ( 'DELETE', NOW(), OLD.prod_no, OLD.category_no, OLD.prod_name, OLD.prod_price, OLD.prod_unit, OLD.prod_stock );
END; //
DELIMITER ;
如何處理或防止觸發器執行時發生錯誤?
由於觸發器內沒有Error Handler (發生錯誤時的處理機制)，因此不要在觸發器內操作太複雜而不可預期的邏輯。如果真的需要複雜邏輯的話，可以由觸發器去呼叫預存程序，範例如下 :
-- 建立 error_log 資料表單
CREATE TABLE error_log (
error_id INT AUTO_INCREMENT PRIMARY KEY,
error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
error_message VARCHAR(255));
-- 建立stored procedure，包含異常處理邏輯
DELIMITER //
CREATE PROCEDURE log_product_update(
IN p_prod_no INT,
IN p_category_no_old INT, IN p_category_no_new INT,
IN p_prod_name_old VARCHAR(100), IN p_prod_name_new VARCHAR(100),
IN p_prod_price_old DECIMAL(10, 2), IN p_prod_price_new DECIMAL(10, 2),
IN p_prod_unit_old VARCHAR(50), IN p_prod_unit_new VARCHAR(50),
IN p_prod_stock_old INT, IN p_prod_stock_new INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 錯誤處理邏輯：記錄錯誤到日誌表
INSERT INTO error_log (error_time, error_message)
VALUES (NOW(), 'Error occurred in log_product_update procedure.');
-- 避免中止程序
RESIGNAL;
END;
-- 插入歷史記錄
INSERT INTO product_history (
operation_type, operation_time, prod_no,
category_no_old, category_no_new,
prod_name_old, prod_name_new,
prod_price_old, prod_price_new,
prod_unit_old, prod_unit_new,
prod_stock_old, prod_stock_new
)
VALUES (
'UPDATE', NOW(), p_prod_no,
p_category_no_old, p_category_no_new,
p_prod_name_old, p_prod_name_new,
p_prod_price_old, p_prod_price_new,
p_prod_unit_old, p_prod_unit_new,
p_prod_stock_old, p_prod_stock_new
);
END //
DELIMITER ;
DELIMITER //
Stored Procedure 預存程序 :
把一連串的SQL程序步驟儲存起來，最後透過 『call 預儲程序名稱;』來呼叫。
建立Stored Procedure語法 :
DELIMITER //
CREATE PROCEDURE procedure_name(
[IN|OUT|INOUT] param_name datatype, -- 傳入、傳出或雙向參數
...
)
BEGIN
-- 程式邏輯
[DECLARE ...]; -- 宣告區
[LOOP|IF|CASE|...]; -- 流程控制
[SQL statements]; -- 執行的 SQL 語句
END; //
DELIMITER ;
通常儲存程序（Stored Procedure）適合執行多步驟邏輯，用來簡化程式或是程式邏輯可以重複使用。
範例 : 新增一個程序，用來新增顧客並回傳該顧客的 ID。
DELIMITER //
CREATE PROCEDURE AddCustomer(
IN customerName VARCHAR(100),
IN customerEmail VARCHAR(100),
OUT customerID INT )
BEGIN
INSERT INTO customer (c_name, c_email) VALUES (customerName, customerEmail);
SET customerID = LAST_INSERT_ID();
END //
DELIMITER ;
CALL AddCustomer('John Doe', '[email protected]', @newCustomerID);
SELECT @newCustomerID;
Stored Function 預儲函數
跟預儲程序很像，不過最後會傳回值。 呼叫方式不是 call，而是Select，例如：『select 預儲函數名稱(引數值);』或『select 預儲函數名稱();』。
建立Stored Function語法 :
DELIMITER //
CREATE FUNCTION function_name(
param_name datatype, -- 輸入參數
...
)
RETURNS datatype -- 函數的返回型別
DETERMINISTIC | NOT DETERMINISTIC -- 決定性標誌（必須指定）
BEGIN
-- 函數邏輯
DECLARE ...; -- 宣告變數（可選）
...
RETURN value; -- 返回值
END; //
DELIMITER ;
通常儲存函數（Stored Function）適合處理簡單計算，或是檢查狀態並取得回傳值。
DELIMITER //
CREATE FUNCTION GetOrderTotal(orderID INT)
RETURNS DECIMAL(10, 2)
NOT DETERMINISTIC
BEGIN
RETURN ( SELECT SUM(prod_qty * prod_price) FROM order_items WHERE o_no = orderID ); END //
DELIMITER ;
SELECT GetOrderTotal(1);
以上的 DETERMINISTIC 是什麼意思? 是指相同輸入會有相同結果，GetOrderTotal(orderID INT) 的結果會因為表格內容變動而改變（例如中間有新的資料加入到 order_items ），因此這個函數應該是 NOT DETERMINISTIC。
但是在 MySQL 中，如果沒有明確宣告 DETERMINISTIC 或 NOT DETERMINISTIC，預設會是 NOT DETERMINISTIC。因此，如果你的函數應該是「非確定性」的，可以選擇不特別宣告 NOT DETERMINISTIC。
Stored procedure 與 Stored function參數類型差異
兩者都支援參數，但用法稍有不同：
儲存程序參數類型：IN：輸入參數（預設），傳遞給程序的值。OUT：輸出參數，程序執行後返回值。INOUT：同時為輸入與輸出參數。
CREATE PROCEDURE example_proc(IN param1 INT, OUT result INT)
BEGIN
SET result = param1 * 2;
END;
CREATE FUNCTION example_func(param1 INT) RETURNS INT
BEGIN
RETURN param1 * 2;
END;
DECLARE EXIT HANDLER：處理例外情況。
RESIGNAL：傳遞異常至外部程式。
(1) 當客戶下訂單時：
-- 更新商品庫存。
-- 若庫存不足則記錄錯誤訊息。
(2) 使用儲存函式 (stored function) 計算訂單總金額。
(3) 使用儲存程序 (stored procedure) 執行下訂單的整個過程。
0 留言