MySQL  TRIGGER （觸發器）

資料庫觸發器是資料庫中所維護的命名資料庫物件，將在修改表中資料時被啟用。使用觸發器可以提高表中資料的功能和安全性級別。可以使用觸發器控制對特定資料的訪問許可權、執行特定日誌記錄或對資料本身進行審計。

觸發器是在修改表資料時啟用的命名資料庫物件。它們可以：

Ø   在插入或更新資料之前對資料進行檢查並驗證刪除和更新

Ø   充當資料過濾器，在插入或更新之前修改超出範圍的資料

Ø   修改 INSERT 、 UPDATE 和 DELETE 的行為方式

Ø   對於不支援外來鍵的儲存引擎，模仿外來鍵的行為

Ø   提供日誌記錄功能

Ø   自動建立彙總表

1.1.         建立觸發器

CREATE TRIGGER 語句：

CREATE TRIGGER trigger_name

{ BEFORE | AFTER }

{ INSERT | UPDATE | DELETE }

ON table_name FOR EACH ROW

triggered_statement

示例：

CREATE TRIGGER City_AD AFTER DELETE

ON City FOR EACH ROW

INSERT INTO DeletedCity (ID, Name) VALUES (OLD.ID, OLD.Name);

trigger_name 是為觸發器指定的名稱，而 table_name 是要與觸發器關聯的表的名稱。 BEFORE 和 AFTER 指示啟用觸發器的時間（是在觸發事件之前或是之後），而 INSERT 、 UPDATE 或 DELETE 指示具體的事件。

注：表名 OLD 和 NEW 是指觸發器可檢視的虛擬表。這些表分別包含 UPDATE 或 DELETE 語句所修改的資料的舊版本，或 INSERT 或 UPDATE 語句所新增的資料的新版本。

 

1.2.         觸發器事件

BEFORE 和 AFTER

BEFORE 和 AFTER 關鍵字是指觸發器的啟用時間，相對於資料修改語句（ INSERT 、 UPDATE 或 DELETE ）將更改寫入底層資料庫的時間。

BEFORE 關鍵字可使觸發器在涉及的資料修改之前執行。可使用 BEFORE 觸發器捕獲無效資料條目並在寫入表之前對其進行更正或拒絕。

– BEFORE INSERT ：在新增新資料之前觸發

– BEFORE UPDATE ：在使用新資料更新（或覆蓋）現有資料之前觸發

– BEFORE DELETE ：在刪除資料之前觸發

AFTER 關鍵字定義在資料修改成功後執行的觸發器。可使用 AFTER 觸發器記錄或審計資料庫中的資料修改。

– AFTER INSERT ：在新增新資料之後觸發

– AFTER UPDATE ：在使用新資料更新（或覆蓋）現有資料之後觸發

– AFTER DELETE ：在刪除資料之後觸發

 

1.3.         觸發器錯誤處理

MySQL 按如下方式處理觸發器執行期間發生的錯誤：

Ø   BEFORE 觸發器失敗，則包含相應行操作的事務將回滾。

Ø   AFTER 觸發器執行， AFTER 觸發器事件和行操作必須成功執行。

Ø   對於非事務表，事務不可用。只有觸發了觸發器的語句會回滾。

觸發器失敗後， MySQL 會回滾包含導致觸發器觸發的語句的事務。

對於非事務表，無法完成此類回滾。因此，雖然語句失敗，但在發生錯誤之前執行的所有更改仍然會生效。

 

1.4.         檢查觸發器

SHOW CREATE TRIGGER trigger_name

此語句返回可用於重新建立指定觸發器的具體字串。您必須知道觸發器的名稱才能執行此語句；對於 SHOW CREATE TRIGGER 語句，不存在 LIKE 或 WHERE 語法。

SHOW TRIGGERS

此語句為 MySQL 擴充套件。它可返回觸發器的特徵，如資料庫、名稱、型別、建立者以及建立和修改日期。此語句有一個優點：可基於 LIKE 模式或 WHERE 子句中提供的條件來顯示特定觸發器。如果未指定條件，則此語句會顯示所有觸發器的資訊。

INFORMATION_SCHEMA.TRIGGERS ：

此語句包含 SHOW 命令所顯示的所有資料，能夠完整地呈現在所有資料庫中可用的觸發器。

 

1.5.         刪除觸發器

使用如下語法可顯式刪除觸發器：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

使用 DROP TRIGGER trigger_name 時，伺服器會在當前模式中查詢該觸發器的名稱。如果要刪除其他模式中的觸發器，請包含模式名稱。使用 IF EXISTS 可防止因嘗試刪除不存在的觸發器而出現的錯誤。透過刪除針對其定義觸發器的表或者包含觸發器的資料庫，可隱式刪除觸發器；

 

1.6.         對觸發器的限制

Ø   不允許使用的語句包括：

l   SQL 預處理語句

l   顯示或隱式 COMMIT 和 ROLLBACK

l   返回結果集的語句，例如，不包含 INTO var_list 子句的 SELECT 語句

l   FLUSH 語句

l   用於修改要應用觸發器的表的語句

l   SHOW 語句

Ø   以下更改不會觸發觸發器：

l   級聯外來鍵所導致的更改

l   在基於行的複製過程中導致的更改

請透過使用 SELECT ...INTO var_list ，或者透過使用遊標和 FETCH 語句的方法在觸發器中處理結果集。

 

1.7.         觸發器許可權

Ø   要執行 CREATE TRIGGER 和 DROP TRIGGER 命令，需要有 TRIGGER 許可權。

Ø   在觸發器中使用 OLD 和 NEW 需要的許可權：要使用 SET NEW.col_name = value 為列賦值，需要對該列擁有 UPDATE 許可權；要在表示式中使用 NEW.col_name 以引用新的列值，需要對該列擁有 SELECT 許可權。