MySQL之觸發器

在 MySQL 中，「觸發器」(Trigger) 是一種特殊的程序類型，它會自動執行以響應資料庫表中的特定事件，例如插入 (INSERT)、更新 (UPDATE) 和刪除 (DELETE) 等操作。觸發器的功能是在這些事件發生時，對資料進行額外的處理或檢測，確保資料的完整性與一致性。以下將詳細介紹 MySQL 的觸發器及其使用方法。

什麼是 MySQL 觸發器？

MySQL 觸發器是一個隱含命名的預存程序（Stored Procedure），它在指定的資料表中執行特定的 SQL 語句，作為回應對該資料表進行的 DML 操作（Insert, Update, Delete）。觸發器可以基於時間條件和事件而執行，並且可以在資料庫層面實現強制性的商業規則和數據完整性約束。

觸發器的特性

• 自動運行：觸發器在相應的事件發生後自動運行，無需手動執行。

• 事務安全：觸發器參與當前的事務管理，如果運行失敗，整個事務將會滾回。

• 內聯接：觸發器可以與其所在的資料表有內部連結，這使得它可以訪問被修改的記錄。

• 可配置：你可以定義觸發器的行為，包括何時運行以及運行的條件。

建立及使用觸發器

要建立一個觸發器，你必須先擁有 CREATE TRIGGER 權限。以下是建立觸發器的基本步驟：

1. 定義觸發器名稱、引發事件、觸發事件所屬的資料表和觸發點（BEFORE 或 AFTER）。

2. 指定觸發器所要執行的 SQL 語句。

3. 使用 `CREATE TRIGGER` 命令來創建觸發器。

舉例來說，假設我們有一個 `Employees` 表格，其中包含了員工的相關資訊。現在我們想要建立一個觸發器，用來檢查每次新增或更新員工紀錄時，薪資欄位的數值是否合理。若不合理，則觸發器拒絕該次操作並返回錯誤訊息。

-- 首先，我們需要為 Employees 表格新增一列 Salary 欄位，且限制 Salary 不能小於 0
ALTER TABLE Employees ADD COLUMN Salary INTEGER NOT NULL DEFAULT 0;

-- 接著，我們建立一個 BEFORE INSERT 觸發器，用以檢查新增紀錄的 Salary 值
CREATE TRIGGER CheckSalaryBeforeInsert
BEFORE INSERT ON Employees FOR EACH ROW
BEGIN
IF NEW.Salary < 0 THEN -- 如果新紀錄的 Salary 小於零
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative'; -- 產生錯誤訊息
END IF;
END;

-- 然後，我們再建立一個 BEFORE UPDATE 觸發器，用來檢查更新的 Salary 值
CREATE TRIGGER CheckSalaryBeforeUpdate
BEFORE UPDATE OF Salary ON Employees FOR EACH ROW
BEGIN
IF OLD.Salary > NEW.Salary THEN -- 如果舊紀錄的 Salary 大於新紀錄的 Salary
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'New salary cannot be less than old one'; -- 產生錯誤訊息
END IF;
END;

在上述範例中，我們使用了兩個不同的觸發器：`CheckSalaryBeforeInsert` 和 `CheckSalaryBeforeUpdate`，分別在 INSERT 和 UPDATE 事件之前執行。每個觸發器都包含了一段程式碼，用來檢查即將被新增或更新的員工薪資是否有合理的數值。如果不合規定，則觸發器會透過 SIGNAL 語句傳送一個錯誤訊息給調用的應用程式，從而阻止不合法的操作。

總結

MySQL 觸發器提供了一種強大的方式來保護和管理資料庫中的資料，它們可以幫助開發人員減少重複的程式碼，同時也簡化了資料庫的管理工作。然而，在使用觸發器的時候，也需要注意性能的問題，因為觸發器可能會增加系統負載，特別是在大規模資料處理的時候。因此，在設計資料庫架構和使用觸發器的時候，應該充分考慮到效能的因素。