SQL Trigger詳解

一、什麼是SQL Trigger

SQL Trigger，即SQL觸發器，是一種特殊的數據庫對象，與表有關聯，可以在特定的操作（例如修改數據、插入新數據或刪除數據）發生時自動執行。SQL Trigger可以用來保證數據的完整性，實現業務邏輯的自動化，以及記錄數據的變化歷史等。

二、SQL Trigger的語法

SQL Trigger有以下基本的語法：

CREATE TRIGGER trigger_name [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
FOR EACH ROW 
BEGIN
-- sql statement
END;

其中：

• trigger_name：觸發器的名稱。
• BEFORE|AFTER：指定在操作之前還是之後觸發該觸發器。
• INSERT|UPDATE|DELETE：指定在執行插入、更新或刪除操作時觸發該觸發器。
• table_name：指定綁定該觸發器的數據庫表名稱。
• FOR EACH ROW：指定該觸發器是針對每一行數據而觸發，而不是每個操作。
• BEGIN...END：包括要執行的SQL語句。

三、SQL Trigger的應用場景

1. 數據完整性的維護

SQL Trigger可以用來在某些操作發生時自動校驗數據的完整性，從而避免數據不一致的情況出現。

例如，一個在線商店的商品庫存不能為負數，可以創建一個觸發器，規定在商品售出之前，先判斷庫存數量是否滿足要求，在不滿足的情況下阻止交易事件的發生。

2. 業務邏輯的自動化

SQL Trigger可以用來自動化處理某些業務邏輯，避免人工操作帶來的錯誤和延遲。

例如，一個銀行的客戶在轉賬時，需要記錄交易的發起時間和完成時間，可以創建一個觸發器，在轉賬開始時插入一條記錄，並在轉賬結束時更新該記錄的完成時間。

3. 數據變化歷史的記錄

SQL Trigger可以用來記錄數據的變化歷史，以便在數據出現問題時進行回溯和排查。

例如，一個人事管理系統需要記錄員工的調職記錄，可以創建一個觸發器，在員工信息表中的職位字段發生變化時，自動在調職記錄表中新增一條記錄，記錄調職時間、調職前的職位和調職後的職位等信息。

四、SQL Trigger的示例

1. 數據完整性的維護

以下示例通過創建一個觸發器，防止商品庫存數量為負數。

CREATE TRIGGER check_inventory BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.inventory < 0 THEN
    SET NEW.inventory = OLD.inventory;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Inventory cannot be negative.';
  END IF;
END;

以上SQL語句創建了一個名為check_inventory的觸發器，該觸發器針對products表的每個更新操作，在更新之前檢查inventory字段的值是否小於0，如果是則阻止該更新行為。

2. 業務邏輯的自動化

以下示例通過創建一個觸發器，在銀行轉賬時自動處理交易時間的記錄。

CREATE TRIGGER update_transaction_time AFTER UPDATE ON bank_accounts
FOR EACH ROW
BEGIN
  IF NEW.balance  OLD.balance THEN
    UPDATE transaction_history SET completion_time = NOW()
    WHERE account_number = NEW.account_number AND completion_time IS NULL;
  END IF;
END;

以上SQL語句創建了一個名為update_transaction_time的觸發器，該觸發器針對bank_accounts表的每個更新操作，在balance字段發生變化時，自動更新對應的交易記錄的完成時間。

3. 數據變化歷史的記錄

以下示例通過創建一個觸發器，在員工調職時自動記錄調職記錄。

CREATE TRIGGER insert_transfer_record AFTER UPDATE ON employee_info
FOR EACH ROW
BEGIN
  IF NEW.position  OLD.position THEN
    INSERT INTO transfer_history (employee_id, transfered_time, from_position, to_position)
    VALUES (NEW.id, NOW(), OLD.position, NEW.position);
  END IF;
END;

以上SQL語句創建了一個名為insert_transfer_record的觸發器，該觸發器針對employee_info表的每個更新操作，在position字段發生變化時，自動在transfer_history表中創建一條記錄，記錄轉移員工的ID、時間、原職位和現職位。