SQL觸發器：型別、應用、工作原理等

SQL 觸發器就像資料庫中的自動化例程，當表中發生特定事件（例如 INSERT、UPDATE 或 DELETE）時，它會執行預定義的操作。這有助於自動執行資料更新並設定一些規則。它可以保持資料的整潔和一致性，而無需您每次都編寫額外的程式碼。在本文中，我們將深入探討 SQL 觸發器的確切含義及其工作原理。我們還將透過一些示例探索不同型別的 SQL 觸發器，並瞭解它們在 MySQL、PostgreSQL 和 SQL Server 中的不同用法。最後，您將對在資料庫設定中如何以及何時實際使用觸發器有一個很好的瞭解。

什麼是SQL觸發器？

觸發器就像一個與資料庫表繫結的自動程式，當特定事件（例如插入、更新或刪除行）發生時，它會自動執行 SQL 程式碼。例如，您可以使用觸發器自動設定建立、新增或刪除新行的時間戳，或者在應用程式中無需額外程式碼即可應用新的資料規則。簡單來說，我們可以說觸發器是一組儲存的 SQL 語句，它們會響應表事件而“觸發”。

觸發器在SQL中的工作原理

在 MySQL 中，觸發器使用 CREATE TRIGGER 語句定義，並附加到特定的表和事件。每個觸發器都是行級的，這意味著它會對受事件影響的每一行執行一次。建立觸發器時，您需要指定：

• 時間：BEFORE 或 AFTER – 觸發器是在事件之前還是之後觸發。
  事件：INSERT、UPDATE 或 DELETE – 啟用觸發器的操作。
  表：它所附加到的表的名稱。
  觸發器主體：要執行的 SQL 語句，包含在 BEGIN … END 中。

例如，BEFORE INSERT 觸發器在新行新增到表之前執行，而 AFTER UPDATE 觸發器在現有行更改後立即執行。MySQL 要求觸發器中使用關鍵字 FOR EACH ROW，這使得它會對受操作影響的每一行執行觸發器主體。

在觸發器內部，使用 NEW 和 OLD 別名引用行資料。在 INSERT 觸發器中，只有 NEW.column 可用（傳入資料）。同樣，在 DELETE 觸發器中，只有 OLD.column 可用（關於要刪除的行的資料）。但是，在 UPDATE 觸發器中，您可以同時使用兩者：OLD.column 引用更新前的行值，NEW.column 引用更新後的值。

讓我們看看觸發器的 SQL 語法：

CREATE TRIGGER trigger_name
BEFORE|AFTER {INSERT|UPDATE|DELETE} ON table_name
FOR EACH ROW
BEGIN
-- SQL statements here --
END;

這是標準的 SQL 格式。需要注意的是，觸發器主體通常包含多個帶分號的語句；通常應該先更改 SQL 分隔符，例如更改為 //，這樣才能正確解析整個 CREATE TRIGGER 語句塊。

建立觸發器的分步示例

現在讓我們看看如何在 SQL 中建立觸發器。

步驟 1：準備表

為此，我們先建立一個簡單的使用者表：

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
created_at DATETIME,
updated_at DATETIME
);

步驟 2：更改分隔符

在 SQL 中，您可以更改語句分隔符，以便編寫多語句觸發器。例如：

DELIMITER //

步驟 3：編寫CREATE TRIGGER語句

例如，我們可以建立一個觸發器，將 created_at 列設定為插入時的當前時間：

CREATE TRIGGER before_users_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END;
//

因此，在上面的程式碼中，BEFORE INSERT ON users 表示觸發器在插入每行新資料之前觸發。觸發器主體會檢查 NEW.created_at 是否為空，如果是，則使用 NOW() 填充。這會自動設定時間戳。

編寫觸發器後，您可以根據需要恢復分隔符，以便其他程式碼可以順利執行。

DELIMITER ;

步驟 4：測試觸發器

現在，當您插入資料時未指定 created_at，觸發器將自動設定。

INSERT INTO users (username) VALUES ('Alice');
SELECT * FROM users;

並且 created_at 將自動填充當前日期/時間。觸發器可以透過設定預設值來自動執行任務。

不同型別的觸發器

每個表有六種型別的 SQL 觸發器：

1. BEFORE INSERT trigger（插入前觸發器）
2. BEFORE UPDATE Trigger（更新前觸發器）
3. BEFORE DELETE Trigger（刪除前觸發器）
4. AFTER INSERT Trigger（插入後觸發器）
5. AFTER UPDATE Trigger（更新後觸發器）
6. AFTER DELETE Trigger（刪除後觸發器）

讓我們透過示例來了解每種觸發器。

1. 插入前觸發器

此觸發器在新行插入表之前啟用。它通常用於在儲存資料之前驗證或修改資料。

BEFORE INSERT 觸發器的 SQL 語法示例：

DELIMITER //
CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;
// 
DELIMITER ;

在插入新使用者記錄之前，此觸發器會自動將 created_at 時間戳設定為當前時間。

2. 更新前觸發器

此觸發器在現有行更新之前執行。這允許在更新發生之前驗證或修改資料。

BEFORE UPDATE 觸發器的 SQL 語法示例：

DELIMITER //
CREATE TRIGGER before_update_user
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.email NOT LIKE '%@%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email address';
  END IF;
END;
//
 
DELIMITER ;

此觸發器在更新使用者記錄之前檢查新的電子郵件地址是否有效。如果無效，則會引發錯誤。

3. 刪除前觸發器

此觸發器在刪除行之前執行。也可用於強制引用完整性或在特定條件下阻止刪除。

BEFORE DELETE 觸發器的 SQL 語法示例：

DELIMITER //
CREATE TRIGGER before_delete_order
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status = 'Shipped' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders';
  END IF;
END;
//
 
DELIMITER ;

此觸發器可防止刪除已發貨的訂單。

4. 插入後觸發器

此觸發器在插入新行後執行，通常用於記錄日誌或更新相關表。

AFTER INSERT 觸發器 SQL 語法示例：

DELIMITER //
CREATE TRIGGER after_insert_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time
  VALUES (NEW.id, 'User created', NOW());
END;
//
 
DELIMITER ;

此觸發器會在 user_logs 表中記錄新使用者的建立。

5. 更新後觸發器

此觸發器在行更新後執行。它可用於審計更改或更新相關資料。

AFTER UPDATE 觸發器的 SQL 語法示例：

DELIMITER //
CREATE TRIGGER after_update_user
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time)
  VALUES (NEW.id, CONCAT('User updated: ', OLD.name, ' to ', NEW.name), NOW());
END;
//
 
DELIMITER ;

此觸發器記錄更新後使用者名稱的更改。

6. 刪除後觸發器

此觸發器在刪除行後執行。通常用於記錄刪除操作或清理相關資料。

AFTER DELETE 觸發器的 SQL 語法示例：

DELIMITER //
CREATE TRIGGER after_delete_user
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time)
  VALUES (OLD.id, 'User deleted', NOW());
END;
//
 
DELIMITER ;

此觸發器將使用者的刪除操作記錄在 user_log 表中。

何時以及為何使用觸發器

當您想要自動執行資料更改時發生的操作時，觸發器非常有用。以下是一些用例和優勢，重點介紹了何時以及為何應該使用 SQL 觸發器。

• 日常任務自動化：您可以自動填充或更新時間戳、計數器或某些計算值等列，而無需在應用中編寫任何額外程式碼。如上例所示，我們自動使用了 created_at 和 updated_at 欄位。
• 強制執行資料完整性和規則：觸發器可以幫助您檢查條件，甚至防止無效操作。例如，如果某行違反某些規則，BEFORE_INSERT 觸發器會引發錯誤並停止該行。這確保即使發生錯誤，資料也能保持乾淨。
• 審計日誌和跟蹤：它們還可以幫助您自動記錄更改。AFTER DELETE 觸發器可以在刪除某行時將記錄插入日誌表。這樣無需編寫單獨的指令碼即可提供審計線索。
• 保持多表一致性：有時，您必須遇到這樣的情況：當一個表發生更改時，您希望另一個表也自動更新。觸發器可以在後臺處理這些關聯的更新。

效能考慮和限制

您必須謹慎執行觸發器。由於觸發器每次資料更改時都會靜默執行，因此如果觸發器過多，它們有時可能會降低速度或使除錯變得棘手。不過，對於設定時間戳、檢查輸入或同步其他資料等操作，觸發器非常有用。它們可以節省時間，並減少因重複編寫相同程式碼而犯下的愚蠢錯誤。

在決定使用 SQL 觸發器之前，需要考慮以下幾點：

• 隱藏邏輯：觸發器程式碼儲存在資料庫中並自動執行，這可能會降低系統行為的透明度。因此，開發人員可能會忘記觸發器在後臺更改資料。因此，應該對其進行良好的文件記錄。
• 無事務控制：您無法在 SQL 觸發器中啟動、提交或回滾事務。所有觸發器操作都發生在原始語句的事務上下文中。換句話說，您無法在觸發器中提交部分更改並繼續執行主語句。
• 非事務性表：如果您使用非事務性引擎，則可能會發生觸發器錯誤。SQL 無法完全回滾。因此，某些資料部分可能會更改，而某些部分可能不會更改，這可能會導致資料不一致。
• 受限資料操作：SQL 限制觸發器執行某些語句。例如，您無法執行 DDL 或呼叫返回結果集的儲存例程。此外，SQL 中沒有檢視觸發器。
  無遞迴：SQL 不允許遞迴；它不能以會導致自身立即再次觸發的方式不斷修改定義它的同一張表。因此，建議避免設計透過不斷更新相同行而迴圈的觸發器。

MySQL、PostgreSQL和SQL Server觸發器對比表

現在讓我們來看看 MySQL、PostgreSQL 和 SQL Server 等不同資料庫上的觸發器有何不同。

小結

雖然 SQL 觸發器一開始可能感覺有點複雜，但一旦上手，你就會完全理解它們，並體會到它們的實用性。當表中發生更改時，它們會自行執行，從而節省時間並確保資料持續遵循你設定的規則。無論是記錄更改、阻止不必要的更新，還是跨表同步資訊，觸發器在 SQL 中都非常有用。但請確保不要過度使用觸發器，也不要建立太多觸發器，因為這會使事情變得混亂，並且以後難以除錯。保持簡單，進行適當的測試，這樣你就可以開始了。