SQL 觸發器的綜合指南

摘要：在本教學中，您將學習 SQL 觸發器的概念以及如何在資料庫系統中開發簡單的觸發器。

SQL 觸發器簡介

觸發器是一段程式碼，會在資料庫中資料表上發生特定事件時自動執行。

觸發器總是與特定的資料表相關聯。如果資料表被刪除，所有相關聯的觸發器也會自動被刪除。

觸發器會在以下事件之前或之後被調用

INSERT - 當插入新的資料列時
UPDATE - 當現有的資料列被更新時
DELETE - 當資料列被刪除時。

當您發出 INSERT、UPDATE 或 DELETE 語句時，關聯式資料庫管理系統 (RDBMS) 會觸發相應的觸發器。

在某些 RDBMS 中，執行呼叫 INSERT、UPDATE 或 DELETE 語句的語句也會調用觸發器。例如，MySQL 有 LOAD DATA INFILE，它會從文字檔中讀取資料列並以非常快的速度插入到資料表中，這會調用 BEFORE INSERT 和 AFTER INSERT 觸發器。

另一方面，一條語句可能會刪除資料表中的資料列，但不會調用相關的觸發器。例如，TRUNCATE TABLE 語句會移除資料表中的所有資料列，但不會調用 BEFORE DELETE 和 AFTER DELETE 觸發器。

觸發器建立語句語法

要建立觸發器，您可以使用以下語句

CREATE TRIGGER trigger_name [BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

讓我們更詳細地檢視語法

首先，在 CREATE TRIGGER 子句之後指定觸發器的名稱。
接下來，使用 BEFORE 或 AFTER 關鍵字來確定觸發器應在何時觸發，以回應特定的事件，例如 INSERT、UPDATE 或 DELETE。
然後，指定觸發器綁定的資料表名稱。
之後，使用 FOR EACH ROW 或 FOR EACH STATEMENT 指定觸發器的類型。我們將在下一節中詳細討論此內容。
最後，將觸發器的邏輯放在 BEGIN ... END 區塊中。

除了在 BEGIN END 區塊中使用程式碼之外，您還可以如下執行預存程序

CREATE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
EXECUTE stored_procedure_name;Code language: SQL (Structured Query Language) (sql)

資料列層級觸發器 vs. 語句層級觸發器

觸發器有兩種類型：資料列層級和語句層級觸發器。

資料列層級觸發器會在每次資料列受到 UPDATE 語句影響時執行。如果 UPDATE 語句影響 10 個資料列，則資料列層級觸發器會執行 10 次，每次針對一個資料列。如果 UPDATE 語句未影響任何資料列，則根本不會執行資料列層級觸發器。

與資料列層級觸發器不同，語句層級觸發器只會被調用一次，無論 UPDATE 語句影響多少資料列。請注意，如果 UPDATE 語句未影響任何資料列，則仍會執行觸發器。

建立觸發器時，您可以使用 FOR EACH ROW 或 FOR EACH STATEMENT 分別指定觸發器是資料列層級還是語句層級。

SQL 觸發器用法

您通常會在以下情況下使用觸發器

記錄資料表修改。某些資料表包含敏感資料，例如客戶電子郵件、員工薪資等，您想要記錄所有變更。在這種情況下，您可以建立 UPDATE 觸發器以將變更插入到單獨的資料表中。
強制執行複雜的資料完整性。在這種情況下，您可以定義觸發器來驗證資料，並在必要時重新格式化資料。例如，您可以使用 BEFORE INSERT 或 BEFORE UPDATE 觸發器在插入或更新之前轉換資料。

SQL 觸發器範例

我們將在範例資料庫中使用 employees 資料表進行示範。

假設我們想要記錄 salary 欄位中的值變更。為此，我們建立一個單獨的資料表來儲存變更，並使用觸發器將變更插入到這個資料表中。

以下語句會建立 salary_changes 資料表。

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    old_salary DECIMAL(8 , 2 ),
    new_salary DECIMAL(8 , 2 ),
    PRIMARY KEY (employee_id , changed_at)
);Code language: SQL (Structured Query Language) (sql)

salary_changes 資料表會記錄員工 ID、舊薪資、新薪資和變更時間。請注意，change_at 欄位使用目前時間作為預設值，以記錄發生變更的時間。

以下 before_update_salary 觸發器會將薪資變更記錄到 salary_changes 資料表中。

CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary <> OLD.salary THEN
	INSERT INTO salary_changes(employee_id,old_salary,new_salary)
        VALUES(NEW.employee_id,OLD.salary,NEW.salary);
    END IF;
END;
Code language: SQL (Structured Query Language) (sql)

在觸發器的主體中，如果新薪資與舊薪資不同，我們會插入變更。

請注意，在觸發器主體內，我們使用 OLD 和 NEW 關鍵字來存取受觸發器影響的資料列中的欄位。

讓我們透過將 ID 為 102 的員工薪資提高 5% 來測試觸發器。

首先，檢查員工 102 的目前薪資

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    employee_id = 110;Code language: SQL (Structured Query Language) (sql)

其次，發出以下 UPDATE 語句，將薪資提高 5%。

UPDATE employees 
SET 
    salary = salary * 1.05
WHERE
    employee_id = 110;Code language: SQL (Structured Query Language) (sql)

第三，檢查 salary_changes 資料表，以查看觸發器是否已調用。

SELECT 
    *
FROM
    salary_changes;Code language: SQL (Structured Query Language) (sql)

如您所見，salary_changes 資料表有一個新的項目。這表示觸發器已正確調用。

修改觸發器

若要變更觸發器定義，您可以使用 CREATE OR REPLACE TRIGGER 語句。

基本上，CREATE OR REPLACE TRIGGER 會在觸發器不存在時建立新的觸發器，並在觸發器存在時變更觸發器。

CREATE OR REPLACE TRIGGER 語句類似於 CREATE TRIGGER 語句，如下所示

CREATE OR REPLACE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

刪除觸發器

若要刪除觸發器，您可以使用 DROP TRIGGER 語句，如下所示

DROP TRIGGER [IF EXISTS] trigger_name;Code language: SQL (Structured Query Language) (sql)

IF EXISTS 選項允許您在觸發器存在時刪除觸發器。如果觸發器不存在，則語句不會執行任何動作。但是，如果您沒有 IF EXISTS 選項，如果您嘗試刪除不存在的觸發器，資料庫系統可能會發出錯誤。

同樣地，如果您刪除資料表，則也會刪除與該資料表相關聯的所有觸發器。以下語句會刪除 before_update_salary 觸發器

DROP TRIGGER IF EXISTS before_update_salary;Code language: SQL (Structured Query Language) (sql)

現在，您應該對 SQL 觸發器有充分的了解，並且知道如何在資料庫系統中建立觸發器。