mysql的觸發器是什麼等級的-mysql教程-PHP中文網

mysql的觸發器是行級的。依照SQL標準，觸發器可以分為兩種：1、行級觸發器，對於修改的每一行資料都會啟動一次，如果一個語句插入了100行數據，將會呼叫觸發器100次；2、語句級觸發器，針對每個語句啟動一次，一個插入100行資料的語句只會呼叫一次觸發器。而MySQL中只支援行級觸發器，不支援預語句級觸發器。

mysql的觸發器是什麼等級的

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

觸發器概述

MySQL 觸發器（trigger）是一種儲存程序，它和一個指定的表相關聯，當該表中的資料發生變化（增加、更新、刪除）時自動執行。這些修改資料行的操作稱為觸發器事件，例如 INSERT 或 LOAD DATA 等插入資料的語句可以啟動插入觸發器。

依照 SQL 標準，觸發器可以分為行級觸發器（row-level trigger）和語句級觸發器（ statement-level trigger）。

行級觸發器對於修改的每一行資料都會啟動一次，如果一個語句插入了100 行數據，將會呼叫觸發器100 次；
#語句級觸發器會針對每個語句啟動一次，一個插入100 行資料的語句只會呼叫一次觸發器。
MySQL 只支援行級觸發器，不支援預語句級觸發器。

mysql的觸發器是什麼等級的

不同事件可以啟動不同類型的觸發器。 INSERT 事件觸發器用於插入資料的操作，包括INSERT、LOAD DATA、REPLACE 語句等；UPDATE 事件觸發器用於更新操作，例如UPDATE 語句；DELETE 事件觸發器用於刪除操作，例如DELETE 和REPLACE 語句等，DROP TABLE 和TRUNCATE TABLE 語句不會啟動刪除觸發器。

另外，MySQL 觸發器可以在觸發事件之前或之後執行，分別稱為 BEFORE 觸發器和 AFTER 觸發器。這兩種觸發時機可以和不同的觸發事件組合，例如 BEFORE INSERT 觸發器或 AFTER UPDATE 觸發器。

MySQL 觸發器的優點包括：

#記錄並審核使用者對資料表中資料的修改操作，實作稽核功能；
實現比檢查約束更複雜的完整性約束，例如禁止非業務時間的資料操作；
實現某種業務邏輯，例如增加或刪除員工時自動更新部門中的人數；
同步即時複製表中的資料。

雖然觸發器功能強大，但它也存在一些缺點：

觸發器會增加資料庫結構的複雜度，而且觸發器對應用程式不可見，難以調試；
觸發器需要佔用更多的資料庫伺服器資源，盡量使用資料庫提供的非空、唯一、檢查約束等等；
觸發器不能接收參數，只能基於目前的觸發物件進行操作。

針對特殊場景使用觸發器可以帶來一定的便利性；但不要過渡依賴觸發器，避免造成資料庫的效能下降和維護困難。接下來我們介紹觸發器的管理操作。

MySQL 支援的三個觸發器

#在實際使用中，MySQL 支援的觸發器有三種：INSERT 觸發器、UPDATE觸發器和DELETE 觸發器。

1) INSERT 觸發器

在 INSERT 語句執行之前或之後回應的觸發器。

使用INSERT 觸發器需要注意以下幾點：

在INSERT 觸發器程式碼內，可引用一個名為NEW（不區分大小寫）的虛擬表來存取被插入的行。
在 BEFORE INSERT 觸發器中，NEW 中的值也可以更新，也就是允許變更插入的值（只要有對應的操作權限）。
對於 AUTO_INCREMENT 列，NEW 在 INSERT 執行之前包含的值是 0，在 INSERT 執行之後會包含新的自動產生值。

2) UPDATE 觸發器

#在 UPDATE 語句執行之前或之後回應的觸發器。

使用UPDATE 觸發器需要注意以下幾點：

在UPDATE 觸發器程式碼內，可引用一個名為NEW（不區分大小寫）的虛擬表來存取更新的值。
在 UPDATE 觸發器程式碼內，可引用一個名為 OLD（不區分大小寫）的虛擬表來存取 UPDATE 語句執行前的值。
在 BEFORE UPDATE 觸發器中，NEW 中的值可能也會更新，也就是允許變更將要用於 UPDATE 語句中的值（只要有對應的操作權限）。
OLD 中的值全部都是唯讀的，不能更新。

注意：當觸發器設計對觸發表本身的更新操作時，只能使用 BEFORE 類型的觸發器，AFTER 類型的觸發器將不被允許。

3) DELETE 觸發器

在 DELETE 語句執行之前或之後回應的觸發器。

使用DELETE 觸發器需要注意以下幾點：

在DELETE 觸發器程式碼內，可以引用一個名為OLD（不區分大小寫）的虛擬表來存取被刪除的行。
OLD 中的值全部都是唯讀的，不能更新。

整體來說，觸發器使用的過程中，MySQL 會以以下方式處理錯誤。

對於事務性表，如果觸發程序失敗，以及由此導致的整個語句失敗，那麼該語句所執行的所有更改將回滾；對於非事務性表，則不能執行此類回滾，即使語句失敗，失敗之前所做的任何更改仍然有效。

若 BEFORE 觸發程式失敗，則 MySQL 將不執行對應行上的動作。

若在 BEFORE 或 AFTER 觸發程式的執行過程中出現錯誤，則會導致呼叫觸發程式的整個語句失敗。

僅當 BEFORE 觸發程序和行操作都已成功執行，MySQL 才會執行 AFTER 觸發程序。

建立觸發器

MySQL 使用CREATE TRIGGRT語句建立觸發器，基本語法如下：

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    trigger_body;

登入後複製

其中，trigger_name是觸發器的名稱；BEFORE 和AFTER 用於指定觸發器的觸發時機；INSERT、UPDATE 和DELETE 用於定義觸發事件的類型；table_name 是觸發器關聯的表名，不能是臨時表或視圖；FOR EACH ROW表示這是行級觸發器；trigger_body 是觸發器執行的具體語句。

舉例來說，由於員工的薪水屬於重要訊息，所以需要記錄薪水的修改歷史。首先，我們建立一個稽核表：

CREATE TABLE emp_salary_audit (
    audit_id    INTEGER NOT NULL AUTO_INCREMENT
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    change_by   VARCHAR(50) NOT NULL,
    CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);

登入後複製

其中，audit_id 是自增主鍵；emp_id 是員工編號；old_salary 和new_salary 分別用於儲存修改前和修改後的月薪；change_date 記錄了修改時間；change_by記錄了執行修改操作的使用者。

然後建立一個觸發器tri_audit_salary，用於記錄員工月薪的修改記錄：

DELIMITER $$
CREATE TRIGGER tri_audit_salary
  AFTER UPDATE ON employee
  FOR EACH ROW
BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END$$
DELIMITER ;

登入後複製

其中，DELIMITER 用來修改SQL 語句的結束符，我們在前文介紹預存程序時已經有所了解；AFTER 表示在修改資料之後執行觸發器；UPDATE 表示只針對更新操作記錄資料變更；觸發器主體中的NEW 和OLD 是MySQL 觸發器中的特殊變量，包含了修改後和修改前的記錄，對於INSERT 觸發器而言沒有OLD 變量，對於DELETE 觸發器而言沒有NEW 變數；CURRENT_TIMESTAMP 和USER() 都是MySQL 系統函數，傳回目前時間和登入的使用者。

在建立觸發器之後，我們執行一些資料修改的操作，驗證該觸發器的效果：

UPDATE employee
SET email = &#39;sunqian@shuguo.net&#39;
WHERE emp_name = &#39;孙乾&#39;;

UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = &#39;孙乾&#39;;

SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date        |change_by|
--------|------|----------|----------|-------------------|---------|
       1|    25|      4700|      5170|2019-10-18 10:16:36|TONY     |

登入後複製

第一個UPDATE 語句只修改了「孫幹」的電子郵箱，所以不會觸發tri_audit_salary；第二個UPDATE 語句修改了他的月薪，觸發了tri_audit_salary。因此審計表 salary_audit 中包含一筆數據，記錄了月薪變更前後的情況。

如果想要同時稽核新增員工和刪除員工的操作，可以再建立一個 INSERT 觸發器和 DELETE 觸發器。

除此之外，MySQL 支援針對相同的觸發時機和相同的事件定義多個觸發器，同時指定它們的執行順序：

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    { FOLLOWS | PRECEDES } other_trigger
    trigger_body;

登入後複製

其中，FOLLOWS 表示該觸發器在觸發器other_trigger 之後執行；PRECEDES 表示該觸發器在other_trigger 之前執行；如果沒有指定任何選項，則預設會依照觸發器的建立順序執行。

檢視觸發器

使用SHOW TRIGGERS語句可以查看資料庫中的觸發器清單：

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE &#39;pattern&#39; | WHERE expr]

登入後複製

其中，db_name用於查看指定資料庫中的觸發器，預設為目前資料庫；LIKE 用於匹配預存程序的名稱，WHERE 可以指定更多的過濾條件。例如，以下語句傳回了目前資料庫中的觸發器：

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tri_audit_salary
               Event: UPDATE
               Table: employee
           Statement: BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
              Timing: AFTER
             Created: 2020-10-06 21:50:02.47
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

登入後複製

另外，MySQL 系統表 INFORMATION_SCHEMA.TRIGGERS 中包含了更詳細的觸發器資訊。

如果想要取得建立某個觸發器的 DDL 語句，可以SHOW CREATE TRIGGER語句。例如：

mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
               Trigger: tri_audit_salary
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)

登入後複製

刪除觸發器

#MySQL 沒有提供修改觸發器的語句，只能透過DROP TRIGGER語句刪除並再次建立觸發器。例如，以下語句可以用來刪除觸發器 tri_audit_salary：

DROP TRIGGER IF EXISTS tri_audit_salary;

登入後複製

IF EXISTS 可以避免觸發器 tri_audit_salary 不存在時產生錯誤。

【相關推薦：mysql影片教學】

以上是mysql的觸發器是什麼等級的的詳細內容。更多資訊請關注PHP中文網其他相關文章！