主頁 » SQL 觸發器

SQL 觸發器

摘要：在本教程中，您將學習 SQL 觸發器以及如何建立觸發器，以響應表中的事件自動呼叫一段程式碼。

SQL 觸發器簡介 #

觸發器是一種資料庫物件，它會響應表中的特定事件，執行一段程式碼、一個使用者定義的函式或一個儲存過程。

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

觸發器在以下事件之前或之後被呼叫：

  • INSERT – 當插入新行時
  • UPDATE – 當更新現有行時
  • DELETE – 當刪除一行時。
  • TRUNCATE – 當表被截斷時。(PostgreSQL)。

當您對錶執行 INSERT、UPDATE 或 DELETE 語句時，如果存在相應的觸發器，關係資料庫管理系統 (RDBMS) 就會觸發它。

在某些 RDBMS 中，執行間接執行 INSERT、UPDATE 或 DELETE 語句的語句也會呼叫觸發器。

例如，MySQL 有 LOAD DATA INFILE 語句，它從文字檔案中讀取資料並以非常高的速度將其插入表中，這會呼叫 BEFORE INSERT 和 AFTER INSERT 觸發器。

另一方面，有些語句可能會刪除表中的行，但不會呼叫相關的觸發器。

例如，TRUNCATE TABLE 語句會刪除表中的所有行，但不會呼叫 BEFORE DELETE 和 AFTER DELETE 觸發器。在 PostgreSQL 中，TRUNCATE TABLE 語句會觸發一個 TRUNCATE 觸發器。

建立觸發器 #

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

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. 語句級觸發器 #

觸發器有兩種型別：

  • 行級觸發器
  • 語句級觸發器

行級觸發器在每一行受到 DML 語句（如 INSERT、UPDATE 和 DELETE）影響時都會執行。

如果語句影響了 10 行，行級觸發器將執行 10 次。如果語句沒有影響任何行，行級觸發器將不會執行。

與行級觸發器不同，無論受影響的行數是多少，語句級觸發器都只被呼叫一次。

請注意，如果 SQL 語句沒有影響任何行，語句級觸發器仍然會執行。

在建立觸發器時，您可以使用 FOR EACH ROW 或 FOR EACH STATEMENT 子句分別指定觸發器是行級還是語句級。

為何使用 SQL 觸發器 #

您通常在以下場景中使用觸發器：

  • 日誌記錄。有些表包含敏感資料，如客戶電子郵件、員工資訊和薪水。您希望記錄所有更改。在這種情況下，您可以建立一個 UPDATE 觸發器，將更改記錄到一個單獨的表中。
  • 強制實施複雜的資料完整性。您可能需要建立觸發器，使用複雜的邏輯來驗證資料，而這些邏輯無法透過簡單的約束（如 NOT NULL、UNIQUE 和 CHECK 約束）來實現。

建立 SQL 觸發器 #

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

employees_table

假設您想記錄 employees 表中 salary 列值的更改。為此，您需要建立一個單獨的表來儲存這些更改，並使用觸發器將更改插入到這個表中。

首先，建立 salary_changes 表以儲存薪水更改：

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at TIMESTAMP 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 列使用當前時間作為預設值，以記錄更改發生的時間。

其次，建立一個函式，將更改記錄到 salary_changes 表中：

CREATE OR REPLACE FUNCTION log_salary_changes () 
RETURNS TRIGGER 
AS 
$$
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;
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

試一試

請注意，在觸發器主體內，您可以訪問 OLD 和 NEW 變數，以分別訪問更新前 (OLD) 和更新後 (NEW) 的行。

第三，建立一個名為 before_update_salary 的觸發器，它執行 log_salary_changes 函式，將薪水更改記錄到 salary_changes 表中：

CREATE TRIGGER salary_changes
AFTER UPDATE OF salary ON employees 
FOR EACH ROW
EXECUTE FUNCTION log_salary_changes ();Code language: SQL (Structured Query Language) (sql)

試一試

第四，檢索員工 ID 為 102 的當前薪水：

SELECT
  employee_id,
  first_name,
  last_name,
  salary
FROM
  employees
WHERE
  employee_id = 110;Code language: SQL (Structured Query Language) (sql)
 employee_id | first_name | last_name | salary
-------------+------------+-----------+---------
         110 | John       | Chen      | 8200.00

第四，使用以下 UPDATE 語句將員工 110 的薪水增加 5%：

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

最後，檢查 salary_changes 表，看觸發器是否已被呼叫：

SELECT
  employee_id,
  old_salary,
  new_salary
FROM
  salary_changes;Code language: SQL (Structured Query Language) (sql)

輸出

 employee_id | old_salary | new_salary
-------------+------------+------------
         110 |    8200.00 |    8610.00

輸出表明 salary_changes 表中有了一條新記錄。這意味著觸發器已正確呼叫。

修改 SQL 觸發器 #

要更改觸發器的定義，您可以使用 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)

移除 SQL 觸發器 #

要從資料庫中刪除一個觸發器，您可以使用 DROP TRIGGER 語句，其語法如下：

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

在此語法中：

  • 首先，在 DROP TRIGGER 關鍵字後指定觸發器的名稱 (trigger_name)。
  • 其次，使用 IF EXISTS 選項僅在觸發器存在時才刪除它。如果觸發器不存在，該語句將不執行任何操作。但是，如果您不使用 IF EXISTS 選項，當您嘗試刪除一個不存在的觸發器時，資料庫系統可能會報錯。

例如，以下語句使用 DROP TRIGGER 語句移除 salary_changes 觸發器：

DROP TRIGGER salary_changes;Code language: SQL (Structured Query Language) (sql)

摘要 #

  • 觸發器是與表關聯的資料庫物件，它會自動執行一段程式碼、一個使用者定義的函式或一個儲存過程，以響應表中發生的事件。
  • 使用 CREATE TRIGGER 語句建立新觸發器。
  • 使用 CREATE OR REPLACE TRIGGER 語句替換觸發器。
  • 使用 DROP TRIGGER 語句移除觸發器。

測驗 #

資料庫 #

SQL Views
上一篇
SQL 檢視
下一篇
SQL CASE 表示式
SQL CASE Expression
© .