教你如何使用MySQL 觸發器

MySQL 觸發器在添加、更新或删除表行時對表應用限制。

MySQL 中的列應用了少量的值限制。例如，将列資料類型設定為tiny int而不是 null需要一個小的數值輸入。盡管如此，仍需要更多的限制來保持資料的完整性。

本教程向您展示如何使用 MySQL 觸發器并為每種類型的觸發器提供示例。

IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

先決條件

• 在資料庫伺服器上運作 MySQL 的系統
• 具有 root 權限的MySQL 使用者帳戶
• 基本 MySQL 指令的知識（請參閱我們可下載下傳的MySQL 指令備忘單）

MySQL 中的觸發器是什麼？

觸發器是一個命名的 MySQL 對象，它在表中發生事件時激活。觸發器是與特定表關聯的特定類型的存儲過程。

NEW觸發器允許使用和通路表中的值以進行比較OLD。修飾符的可用性取決于您使用的觸發事件：

在嘗試插入資料時檢查或修改值使NEW.<column name>修飾符可用。這是因為表格已更新為新内容。相反，OLD.<column name>插入語句的值不存在，因為事先沒有資訊存在于其位置。

更新表格行時，兩個修飾符都可用。有OLD.<colum name>我們想更新為資料的NEW.<column name>資料。

最後，當移除一行資料時，OLD.<column name>修飾符通路移除的值。NEW.<column name>不存在，因為删除時沒有任何東西可以替換舊值。

注意：通過學習如何使用某些指令來重命名 MySQL 中的列，可以輕松管

理資料庫。

IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠

MySQL 觸發器示例

作為應用觸發器的示例，将新值插入到person表中會産生與原始輸入不同的結果：

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

請注意，插入的名稱最初是小寫的。選擇表格時，第一個字母顯示為大寫。盡管沒有任何與正常插入語句不同的迹象，但觸發器在插入語句之前觸發以大寫名稱的第一個字母。

使用 MySQL 觸發器

與表關聯的每個觸發器都具有基于兩個因素的唯一名稱和功能：

1.時間。BEFORE或AFTER特定的行事件。

2.事件。INSERT,UPDATE或DELETE.

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

MySQL 觸發器根據激活時間和事件觸發，總共有六個獨特的觸發器組合。before 語句有助于在送出之前檢查資料并進行更改，而 after 語句首先送出資料然後執行語句。

一組動作的執行自動發生，影響語句中所有插入、删除或更新的行。

建立觸發器

使用CREATE TRIGGER語句文法建立新觸發器：

CREATE TRIGGER <trigger name> <trigger time > <trigger event>           

ON <table name>           

FOR EACH ROW           

<trigger body>;           

最佳做法是使用以下資訊命名觸發器：

<trigger time>_<table name>_<trigger event>           

例如，如果觸發器在名為employee的表上插入之前觸發，則最好的約定是調用觸發器：

before_employee_insert           

或者，一種常見的做法是使用以下格式：

<table name>_<first letter of trigger time><first letter of trigger name>           

表employee的前插入觸發器名稱如下所示：

employee_bi           

對于受函數影響的每一行，觸發器在由<table name>定義的表上發生事件的特定時間執行。

IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠

删除觸發器

要删除觸發器，請使用以下DROP TRIGGER語句：

DROP TRIGGER <trigger name>;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

或者，使用：

DROP TRIGGER IF EXISTS <trigger name>;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

由于沒有觸發器，是以不會顯示錯誤消息，是以不會列印警告。

建立示例資料庫

為具有以下結構的觸發器示例代碼建立一個資料庫：

1.建立一個名為person的表，其中列有姓名和年齡。

CREATE TABLE person (name varchar(45), age int);           

将樣本資料插入表中：

INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);           

選擇表格以檢視結果：

SELECT * FROM person;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

2. 建立一個名為average_age的表，其中有一列名為average：

CREATE TABLE average_age (average double);           

将平均年齡值插入表中：

INSERT INTO average_age SELECT AVG(age) FROM person;           

選擇表格以檢視結果：

SELECT * FROM average_age;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

3. 建立一個名為person_archive的表，其中包含name、age和time列：

CREATE TABLE person_archive (           

name varchar(45),           

age int,           

time timestamp DEFAULT NOW());           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

注意：該函數NOW()記錄目前時間。從我們的的MySQL日期函數指南和示例中了解有關日期和時間函數的更多資訊。

IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複

建立一個 BEFORE INSERT 觸發器

要建立BEFORE INSERT觸發器，請使用：

CREATE TRIGGER <trigger name> BEFORE INSERT           

ON <table name>           

FOR EACH ROW           

<trigger body>;           

觸發器在BEFORE INSERT送出到資料庫表之前控制資料修改。BEFORE INSERT将名稱大寫以保持一緻性、檢查輸入的長度或使用觸發器捕獲錯誤輸入會在輸入新資料之前進一步提供值限制。

BEFORE INSERT 觸發器示例

BEFORE INSERT在将資料插入人員表之前建立一個觸發器來檢查年齡值：

delimiter //           

CREATE TRIGGER person_bi BEFORE INSERT           

ON person           

FOR EACH ROW           

IF NEW.age < 18 THEN           

SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';           

END IF; //           

delimiter ;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

插入資料會激活觸發器并在送出資訊之前檢查age的值：

INSERT INTO person VALUES ('John', 14);           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

控制台顯示描述性錯誤消息。由于觸發器檢查失敗，資料未插入表中。

建立一個 AFTER INSERT 觸發器

使用以下指令建立AFTER INSERT觸發器：

CREATE TRIGGER <trigger name> AFTER INSERT           

ON <table name>           

FOR EACH ROW           

<trigger body>;           

當AFTER INSERT輸入的行生成更新另一個表所需的值時，觸發器很有用。

IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複

AFTER INSERT 觸發器示例

在person表中插入新行不會自動更新average_age表中的平均值。在person表上建立AFTER INSERT觸發器以在插入後更新average_age表：

delimiter //           

CREATE TRIGGER person_ai AFTER INSERT           

ON person           

FOR EACH ROW           

UPDATE average_age SET average = (SELECT AVG(age) FROM person); //           

delimiter ;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網www.xiaolin.cc

在person表中插入一個新行會激活觸發器：

INSERT INTO person VALUES ('John', 19);           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網www.xiaolin.cc

資料成功送出到person表并使用正确的平均值更新average_age表。

建立更新前觸發器

使用以下方法BEFORE UPDATE觸發：

CREATE TRIGGER <trigger name> BEFORE UPDATE           

ON <table name>           

FOR EACH ROW           

<trigger body>;           

觸發器與觸發器BEFORE UPDATE一起使用BEFORE INSERT。如果在插入資料之前存在任何限制，那麼在更新之前也應該存在限制。

BEFORE UPDATE 觸發器示例

如果在插入資料之前對person表有年齡限制，那麼在更新資訊之前也應該有年齡限制。沒有BEFORE UPDATE觸發器，年齡檢查觸發器很容易避免。沒有什麼可以将編輯限制為錯誤的值。

IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠

BEFORE UPDATE将觸發器添加到與觸發器具有相同主體的personBEFORE INSERT表中：

delimiter //           

CREATE TRIGGER person_bu BEFORE UPDATE           

ON person           

FOR EACH ROW           

IF NEW.age < 18 THEN           

SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';           

END IF; //           

delimiter ;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

更新現有值會激活觸發器檢查：

UPDATE person SET age = 17 WHERE name = 'John';           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

将年齡更新為小于 18 的值會顯示錯誤消息，并且資訊不會更新。

建立 AFTER UPDATE 觸發器

使用以下代碼塊建立AFTER UPDATE觸發器：

CREATE TRIGGER <trigger name> AFTER UPDATE           

ON <table name>           

FOR EACH ROW           

<trigger body>;           

AFTER UPDATE觸發器有助于跟蹤已送出的資料更改。大多數情況下，插入資訊後的任何更改也會在更新資料後發生。

AFTER UPDATE 觸發器示例

對person表中年齡資料的任何成功更新也應該更新在average_age表中計算的中間平均值。

在更新person表中的一行後建立一個AFTER UPDATE觸發器來更新average_age表：

delimiter //           

CREATE TRIGGER person_au AFTER UPDATE           

ON person           

FOR EACH ROW           

UPDATE average_age SET average = (SELECT AVG(age) FROM person); //           

delimiter ;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

更新現有資料會更改person表中的值：

UPDATE person SET age = 21 WHERE name = 'John';           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

更新表person也會更新average_age表中的平均值。

IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複

建立一個 BEFORE DELETE 觸發器

要建立BEFORE DELETE觸發器，請使用：

CREATE TRIGGER <trigger name> BEFORE DELETE           

ON <table name>           

FOR EACH ROW           

<trigger body>;           

出于安全原因，BEFORE DELETE觸發器是必不可少的。如果父表附加了任何子表，則觸發器有助于阻止删除并防止孤立表。觸發器還允許在删除之前歸檔資料。

BEFORE DELETE 觸發器示例

通過在表person上建立BEFORE DELETE觸發器來歸檔已删除的資料并将值插入person_archive表：

delimiter //           

CREATE TRIGGER person_bd BEFORE DELETE           

ON person           

FOR EACH ROW           

INSERT INTO person_archive (name, age)           

VALUES (OLD.name, OLD.age); //           

delimiter ;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

從person表中删除資料将資料歸檔到person_archive表中，然後再删除：

DELETE FROM person WHERE name = 'John';           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

将值插入回person表中，将删除資料的日志保留在person_archive表中：

INSERT INTO person VALUES ('John', 21);           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

觸發器對于BEFORE DELETE記錄任何表更改嘗試很有用。

建立一個 AFTER DELETE 觸發器

使用以下方法AFTER DELETE觸發：

CREATE TRIGGER <trigger name> AFTER DELETE           

ON <table name>           

FOR EACH ROW           

<trigger body>;           

觸發器維護要求資料行在進行更新之前消失的AFTER DELETE資訊更新。

IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠IT澶栧寘 鏈嶅姟鍣ㄨ櫄鎷熷寲 鏁版嵁瀛樺偍 鏁版嵁澶囦喚 鏁版嵁鎭㈠

AFTER DELETE 觸發器示例

在person表上建立AFTER DELETE觸發器以使用新資訊更新average_age表：

delimiter //           

CREATE TRIGGER person_ad AFTER DELETE           

ON person           

FOR EACH ROW           

UPDATE average_age SET average = (SELECT AVG(person.age) FROM person); //           

delimiter ;           

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

從person表中删除一條記錄會使用新的平均值更新average_age表：

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

沒有AFTER DELETE觸發器，資訊不會自動更新。

建立多個觸發器

MySQL 不支援同時觸發多個觸發器。但是，可以向同一個觸發器添加多個邏輯操作。使用BEGIN和END定界符來訓示觸發器主體：

CREATE TRIGGER <trigger name> <trigger time > <trigger event>           

ON <table name>           

FOR EACH ROW           

BEGIN           

<trigger body>;           

END;           

確定在建立具有多個操作的觸發器之前更改預設分隔符。

顯示觸發器

列出資料庫中的所有觸發器：

SHOW triggers;           

輸出顯示所有觸發器的清單，包括名稱和語句内容：

IT外包|伺服器虛拟化|資料存儲|資料備份|網絡故障維修|資料恢複|異地組網 www.xiaolin.cc

還會顯示其他資訊，例如建立時間和建立觸發器的使用者。

結論

MySQL 觸發器在特定事件發生之前或之後提供對資料的進一步驗證和控制。無論您是試圖防止錯誤還是添加一緻性限制，觸發器都有助于控制資料輸入、更新和删除。

請記住，觸發器檢查是按行進行的，這會導緻性能因大量查詢而降低。

IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複IT外包 伺服器虛拟化 資料存儲 資料備份 資料恢複