本文首發：https://www.kalacloud.com/blog/how-to-manage-and-use-mysql-database-triggers
觸發器（Trigger）是 MySQL 中非常實用的一個功能，它可以在操作者對錶進行「增刪改」 之前（或之後）被觸發，自動執行一段事先寫好的 SQL 程式碼。
本教程帶領大家在實踐中學習，你將學到觸發器在實際應用場景中的重要應用。
在這個教程中，你是「卡拉雲銀行」的程式設計師，你正在搭建一套銀行客戶管理系統。在這套系統中，你需要設定在INSERT 表之前檢測操作者是否輸入錯誤資料、在 UPDATE 時，記錄操作者的行為 log ，以及在DELETE 時，判斷刪除的資訊是否符合刪除規則。 這三類操作都可以使用 MySQL 觸發器來實現。
如果你正在資料庫的基礎上搭建一套資料庫管理工具或企業內部工具，推薦你試試我開發的卡拉雲，詳情見後文。
本教程將帶你一起實踐的案例
BEFORE INSERT： 在插入資料前，檢測插入資料是否符合業務邏輯，如不符合返回錯誤資訊。AFTER INSERT： 在表 A 建立新賬戶後，將建立成功資訊自動寫入表 B 中。BEFORE UPDATE：在更新資料前，檢測更新資料是否符合業務邏輯，如不符合返回錯誤資訊。AFTER INSERT：在更新資料後，將操作行為記錄在 log 中BEFORE DELETE：在刪除資料前，檢查是否有關聯資料，如有，停止刪除操作。AFTER DELETE：刪除表 A 資訊後，自動刪除表 B 中與表 A 相關聯的資訊。
先決條件
在開始之前，請確保您具備以下條件：
建立示例資料庫
我們先建立一個乾淨的示例資料庫，方便大家可以跟隨本教程一起實踐。我們會在這個資料庫中演示 MySQL 觸發器的多種工作方式。
首先，以 root 身份登入到你的 MySQL 伺服器：
mysql -u root -p出現提示時，請輸入你 MySQL root 賬號的密碼，然後點選 ENTER 繼續。看到 mysql> 提示後，執行以下命令，建立 demo_kalacloud 資料庫：
CREATE database demo_kalacloud;Output
Query OK, 1 row affected (0.00 sec)接下來，切換到新建的 demo_kalacloud 資料庫：
USE demo_kalacloud;Output
Database changed接著建立一個 customers 表。我們使用這個表記錄銀行客戶的資訊。這個表包括 customer_id，customer_name，和level。我們們先把客戶分為兩個級別：BASIC和VIP。
create table customers(
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(50),
level VARCHAR(50)
) ENGINE=INNODB;Output
Query OK, 0 rows affected (0.01 sec)接著，我們向 customers 表中新增一些客戶記錄。
Insert into customers (customer_id, customer_name, level )values('1','Jack Ma','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','Robin Li','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','Pony Ma','VIP');分別執行三個 INSERT 命令後，命令列輸出成功資訊。
Output
Query OK, 1 row affected (0.01 sec)我們使用 SELECT 檢查一下三條資訊是否已經寫入表中：
Select * from customers;
下面我們建立另一個表customer_status，用於儲存 customers 表中客戶的備註資訊。
這個表包含 customer_id 和 status_notes 欄位：
Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;然後，我們再建立一個 sales 表，這個表與 customer_id 關聯。儲存與客戶有關的銷售資料。
Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;Output
Query OK, 0 rows affected (0.01 sec)最後一步，我們再建一個 audit_log 表，用來記錄操作員操作「卡拉雲銀行」客戶管理系統時的操作行為。方便管理員在發生問題時，有 log 可查。
Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;Output
Query OK, 0 rows affected (0.02 sec)至此，你作為「卡拉雲銀行」的程式設計師，已經把客戶管理系統的demo_kalacloud 資料庫和四張表建立完成。接下來，我們將對這個管理系統的關鍵節點增加對應的觸發器。
擴充套件閱讀：《如何使用 MySQL 慢查詢日誌進行效能優化 - Profiling、mysqldumpslow 例項詳解》
1.BEFORE INSERT觸發器使用方法
作為嚴謹的銀行客戶管理系統，對任何寫入系統的資料都應該提前檢測，以防止錯誤的資訊被寫進去。
在寫入前檢測資料這個功能，我們可以使用BEFORE INSERT 觸發器來實現。
在操作者對 sales 表中的sales_amount 欄位進行寫操作時，系統將在寫入（INSERT）前檢查資料是否符合規範。
我們先來看一下，建立觸發器的基本語法。
DELIMITER //
CREATE TRIGGER [觸發器的名字]
[觸發器執行時機] [觸發器監測的物件]
ON [表名]
FOR EACH ROW [觸發器主體程式碼]//
DELIMITER ;觸發器的結構包括：
DELIMITER //：MySQL 預設分隔符是;但在觸發器中，我們使用//表示觸發器的開始與結束。[觸發器的名字]：這裡填寫觸發器的名字[觸發器執行時機]：這裡設定觸發器是在關鍵動作執行之前觸發，還是執行之後觸發。[觸發器監測的物件]：觸發器可以監測INSERT、UPDATE、DELETE的操作，當監測的命令對觸發器關聯的表進行操作時，觸發器就被啟用了。[表名]：將這個觸發器與資料庫中的表進行關聯，觸發器定義在表上，也附著在表上，如果這個表被刪除了，那麼這個觸發器也隨之被刪除。FOR EACH ROW：這句表示只要滿足觸發器觸發條件，觸發器都會被執行，也就是說帶上這個引數後，觸發器將監測每一行對關聯表操作的程式碼，一旦符合條件，觸發器就會被觸發。[觸發器主體程式碼]：這裡是當滿足觸發條件後，被觸發執行的程式碼主體。這裡可以是一句 SQL 語句，也可以是多行命令。如果是多行命令，那麼這些命令要寫在BEGIN...END之間。
注：在建立觸發器主體時，還可以使用OLD和NEW 來獲取 SQL 執行INSERT，UPDATE和DELETE 操作前後的寫入資料。這裡沒看明白沒關係，我們將會在接下來的實踐中，展開講解。
講到這裡，大家看了一大堆雲裡霧裡的概念，如果沒看懂，也別擔心。接下來進入實踐環節，只要跟著貼程式碼看返回結果，很快你就能夠通透理解觸發器了。
現在，我們來建立第一個觸發器，BEFORE INSERT （在執行 insert 之前，執行觸發器）。這個觸發器用於監測操作者在寫入 sales 表中的 sales_amount 值時，這個值是否大於 10000 ，如果大於，那麼返回錯誤資訊進行報錯。
登入 MySQL Server 後，我們建立一個觸發器：
DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "你輸入的銷售總額超過 10000 元。";
END IF//
DELIMITER ;上面這段程式碼中，我們使用IF...THEN...END IF 來建立一個監測 INSERT 語句寫入的值是否在限定的範圍內的觸發器。
這個觸發器的功能時監測 INSERT 在寫入sales_amount 值時，這個新增的（NEW）值是否符合條件（ > 10000）。
當操作員錄入一個超過 10000 的數字，會返回如下錯誤資訊：
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '你輸入的銷售總額超過 10000 元。';我們來試試看，看看觸發器是否已啟用。
我們向 sales_amount 中插入一條 11000 的值。
Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');
命令列返回錯誤資訊，這就是我們剛剛建立觸發器時，填入的錯誤資訊。與我們的設定一致。
下面我們 insert 一個值小於 10000 的數字：
Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7700');輸入值為 7700 小於設定的 10000 ，insert 命令執行成功。
Output
Query OK, 1 row affected (0.01 sec)我們調出 sales 表，看看是否插入成功：
Select * from sales;輸出確認資料在表中：
![確認資料在表中]](kalacloud.com/static/6a4d3093e730b...)
通過這張表，我們可以看到，7700 已經插入到表中。
剛剛我們演示了在執行 insert 命令前，檢測某個值是否符合設定，接著我們來看在執行 insert 之後，使用觸發器將不同的值儲存到不同的表中。
擴充套件閱讀：《如何在兩臺伺服器之間遷移 MySQL / MariaDB 資料庫 阿里雲騰訊雲遷移案例》
2.AFTER INSERT觸發器使用方法
接著我們講解 AFTER INSERT ，觸發器在監測到我們成功執行了 INSERT 命令後，再執行觸發器中設定好的程式碼。
例如：在銀行賬戶系統中，當我們新建一個賬戶後，我們將建立成功資訊寫入對應的 customer_status 表中。
在這個案例中，你作為「卡拉雲銀行」的程式設計師，現在要建立一個AFTER INSERT觸發器，在建立新客戶賬戶後，將成功資訊寫入customer_status 表中
要建立AFTER INSERT觸發器，請輸入以下命令：
DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, '賬戶建立成功')//
DELIMITER ;Output
Query OK, 0 rows affected (0.00 sec)這個觸發器在操作者向 customers 表中 INSERT 新客戶資訊後，再向 customer_status 表對應的行中寫入成功資訊。
現在我們 INSERT 一條資訊，看看觸發器是否已啟用：
Insert into customers (customer_id, customer_name, level )values('4','Xing Wang','VIP');Output
Query OK, 1 row affected (0.01 sec)記錄 INSERT 成功，接著我們來檢查customer_status表中是否寫入了對應的成功資料。
Select * from customer_status;
這裡可以看到，我們向 customers 表插入了一個customer_id 為 4 的新使用者 ，隨後，觸發器根據程式碼自動向customer_status 表中也插入了一個 customer_id 為 4 的開戶成功資訊。
AFTER INSERT 特別適合這種狀態變更的關聯寫入操作。比如開戶、暫停、登出等各類狀態變更。
到這裡，觸發器在INSERT執行前、後的應用，我們已經講完了，接著我們來講 UPDATE 觸發器。
擴充套件閱讀：《MySQL 配置檔案 my.cnf / my.ini 逐行詳解》
3.BEFORE UPDATE觸發器使用方法
BEFORE UPDATE觸發器與BEFORE INSERT 觸發器非常類似，我們可以使用BEFORE UPDATE 觸發器在更新資料之前，先做一次業務邏輯檢測，避免發生誤操作。
剛剛我們建立示例資料庫時，建立了兩個級別的客戶，VIP 和 BASIC 級別。卡拉雲銀行的客戶一旦升級至 VIP，就不能再降級至 BASIC 級別了。
我們使用 BEFORE UPDATE 來貫徹這一規則，這個觸發器將在 UPDATE 語句執行之前，先判斷是否為降級行為，如果是，則輸出報錯資訊。
我們來建立這個觸發器：
DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'VIP 級別客戶不能降級為普通級別客戶';
END IF //
DELIMITER ;我們可以使用 OLD 來獲取執行 UPDATE 命令前，客戶的 level 值。同樣，我們使用該IF...THEN...END IF語句來對 level 值是否符合規則進行判斷。
我們先來檢視一下 customers 表中的資料。
select * from customers;
好，我們選一個已經是 VIP 級別的客戶，對他進行降級操作，看看我們的觸發器是否能夠正確執行。
接下來，執行以下 SQL 命令，試試能不能將 customer_id 為 3 的 VIP 客戶降級成 BASIC 客戶：
Update customers set level='BASIC' where customer_id='3';執行程式碼後，命令列返回錯誤資訊：
這說明我們剛剛設定的觸發器已經起作用了。
接著我們來試試，對一個BASIC級別的客戶執行相同的命令，看看能不能把他升級到VIP級別：
Update customers set level='VIP' where customer_id='2';執行成功：
Output
Rows matched: 1 Changed: 1 Warnings: 0我們再來看一下 customers 表中的資料情況：
select * from customers;
可以看到剛才 customer_id 為 2 的 BASIC 客戶已經升級為 VIP 客戶。
BEFORE UPDATE 觸發器用於在更新資料前進行確認，很好的守護了系統的業務規則。接著我們來看看 AFTER UPDATE 在客戶管理系統中的應用。
擴充套件閱讀：《MySQL Workbench 操作 MySQL / MariaDB 資料庫中文指南》
4.AFTER INSERT觸發器使用方法
本節我們來演示 AFTER UPDATE 在實際中的應用。AFTER UPDATE 多用於 log 記錄，在管理系統多操作者使用的環境中，管理員需要設定操作 log 記錄，以便在出問題時，可以檢視操作者對錶編輯的操作，可追根溯源。
我們先來建立一個對 sales 表操作的 log 記錄觸發器。
當操作者對 sales 表進行修改後，操作記錄會被寫入 audit_log 表中。
觸發器將監測使用者 ID 、更新前的銷售總額、更新後的銷售總額、操作者 ID、修改時間等資訊，作為 log 存入 audit_log 表中。
使用以下命令建立這個 log 記錄觸發器：
DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;當操作者對 sales 表中的一條客戶資訊進行 UPDATE 操作時，觸發器會在UPDATE操作之後，將操作行為記錄在 audit_log 中。包括 sales_id ，修改 sales_amount 值的前後變化。
銷售總額的變化是審計的關鍵資料，所以要把它記錄在 audit_log 中。使用OLD 來獲取更新前的 sales_amount 值，使用 NEW 來獲取更新後的值。
另外我們還要記錄修改 sales 表的操作者資訊及操作時間。
你可以使用 SELECT USER() 來檢測當前操作使用者的賬號，用 NOW() 語句抓去當前伺服器日期和時間。
為了測試這個觸發器，我們先在 sales 表中建立一條資訊記錄：
Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');Output
Query OK, 1 row affected (0.00 sec)接下來，我們來更新這條記錄：
Update sales set sales_amount='9000' where sales_id='5';您將看到以下輸出：
Output
Rows matched: 1 Changed: 1 Warnings: 0理論上，我們更新了 sales 表後，觸發器應該觸發了操作，將我們剛剛的修改記錄到了audit_log 表中。我們用以下命令，看看audit_log 表中是否已經有記錄了。
Select * from audit_log;如下表，觸發器更新了audit_log 表，表中包含了sales_amount 更新前的舊值和更新後的新值。
至此，使用 AFTER UPDATE 製作的 log 自動記錄觸發器就完成了。
下一節，我們來學習 DELETE 相關的觸發器。
擴充套件閱讀：《如何檢視 MySQL 資料庫、表、索引容量大小？找到佔用空間最大的表》
5.BEFORE DELETE觸發器使用方法
BEFORE DELETE觸發器會在DELETE語句執行之前呼叫。
這些型別的觸發器通常用於在不同的相關表上強制執行參照完整性。
BEFORE DELETE 的應用場景通常是確保有關聯的資料不被錯誤的誤刪除掉。
例如：sales 表通過customer_id 與customers表相關聯。如果操作者刪除了customers 表中的一條資料，那麼 sales 表中某些資料就失去了關聯線索。
為了避免這種情況的發生，我們需要建立一個 BEFORE DELETE觸發器，防止記錄被誤刪除。
DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '這位客戶有相關聯的銷售記錄，不能刪除。';
END IF//
DELIMITER ;現在，我們試著刪除有銷售關聯資訊的客戶：
Delete from customers where customer_id='2';所以，你會看到以下輸出：
這個觸發器做到了先檢測 sales 是否與正要被刪除的 customers 表中的資料有關聯，防止有關聯資訊的資料被誤刪除。
不過有時候，我們需要刪除主資料後，再讓系統自動幫我們刪除與之相關聯的其他所有資料。這時，我們就要用到 AFTER DELETE 這個觸發器了。
擴充套件閱讀：《在 MySQL 中 DATETIME 和 TIMESTAMP 時間型別的區別及使用場景 - 實戰案例講解》
6.AFTER DELETE觸發器使用方法
接著說說 AFTER DELETE ，一旦記錄被成功刪除，這個觸發器就會被啟用。
這個觸發器在實際場景用的應用也比較廣泛。比如銀行系統中的升級降級操作，當客戶花掉自己的賬戶積分後，啟用觸發器，觸發器可以判斷剩餘積分是否滿足客戶當前等級，如果不滿足，自動做降級操作。
AFTER DELETE觸發器的另一個用途是在刪除主表中的資料後，與這個主表關聯的資料，一起自動刪除。
我們來看一下這個觸發器如何建立：
DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;接下來，我們來試試這個觸發器。刪除銷售記錄中 customer_id 為 2 的銷售記錄：
Delete from sales where customer_id='2';Output
Query OK, 1 row affected (0.00 sec)接著我們檢查以下 customers 表中的關聯資訊是否一起自動刪除：
Select * from customers where customer_id='2';命令列會返回 Empty Set 的結果，我們剛剛刪除了 sales 表中的資訊後，customers 表中的關聯資訊也被一起刪除了。
以上就是 MySQL 觸發器的六種使用方式和對應的場景。
擴充套件閱讀：《最好用的 10 款 MySQL / MariaDB 管理工具橫向測評 - 免費和付費到底怎麼選?》
7.檢視觸發器
（1）直接檢視觸發器
當我們想檢視資料庫中的觸發器有哪些時，可用以下命令：
SHOW TRIGGERS;後面加上 \G 是觸發器列表豎排列：
SHOW TRIGGERS \G
剛剛我們建立的觸發器都羅列在這個列表當中了。
（2）在 triggers 表中檢視觸發器資訊
在 MySQL Server 中，資料庫 information_schema 的 triggers 表中存著所有觸發器的資訊。所有我們可以通過 SELECT 來檢視。
SELECT * FROM information_schema.triggers WHERE trigger_name= '觸發器名稱';當然，也可以不指定觸發器名稱，來檢視所有。
SELECT * FROM information_schema.triggers \G擴充套件閱讀：《如何在 MySQL / MariaDB 中查詢資料庫中帶有某個欄位/列名的所有表名》
8.刪除觸發器
最後，我們們來說說如何刪除觸發器。刪除命令也很簡單，Drop trigger 觸發器名字 即可。
Drop trigger [觸發器名稱];例如，我們們把剛剛建立的最後一個觸發器刪掉：
Drop trigger delete_related_info;Output
Query OK, 0 rows affected (0.00 sec)特別提示：我們不能對已經建立好的觸發器進行修改。如果你想修改，只能先刪除，再重新建立。
擴充套件閱讀：《MySQL / MariaDB 中如何儲存圖片 BLOB 資料型別詳解》
9.總結
在本教程中，我們展示了觸發器的六種形式，即在INSERT、DELETE、UPDATE 執行前或後執行觸發器，以及對應的六個實戰案例。
BEFORE INSERT： 在插入資料前，檢測插入資料是否符合業務邏輯，如不符合返回錯誤資訊。AFTER INSERT： 在表 A 建立新賬戶後，將建立成功資訊自動寫入表 B 中。BEFORE UPDATE：在更新資料前，檢測更新資料是否符合業務邏輯，如不符合返回錯誤資訊。AFTER INSERT：在更新資料後，將操作行為記錄在 log 中BEFORE DELETE：在刪除資料前，檢查是否有關聯資料，如有，停止刪除操作。AFTER DELETE：刪除表 A 資訊後，自動刪除表 B 中與表 A 相關聯的資訊。
接著推薦一下卡拉雲，只要你會寫 MySQL ，就能使用卡拉雲搭建自己的資料工具，比如，資料看板，企業 CRM、ERP，許可權管理後臺，對賬系統等。
卡拉雲是新一代低程式碼開發工具，免安裝部署，可一鍵接入包括 MySQL 在內的常見資料庫及 API。可根據自己的工作流，定製開發。無需繁瑣的前端開發，只需要簡單拖拽，即可快速搭建企業內部工具。數月的開發工作量，使用卡拉雲後可縮減至數天，歡迎免費試用卡拉雲。
卡拉雲可一鍵接入常見的資料庫及 API
卡拉雲可根據公司工作流需求，輕鬆搭建資料看板或其他內部工具，並且可一鍵分享給組內的小夥伴。
希望本教程對你有所幫助。更多有關 MySQL 教程，歡迎訪問卡拉雲檢視更多。
有關 MySQL 教程，可繼續擴充學習：
- 如何遠端連線 MySQL 資料庫，阿里雲騰訊雲外網連線教程
- 如何在 MySQL / MariaDB 中匯入匯出資料，匯入匯出資料庫檔案、Excel、CSV
- 如何在兩臺伺服器之間遷移 MySQL 資料庫 阿里雲騰訊雲遷移案例
- MySQL 重置自增 ID (AUTO_INCREMENT)教程 - 完美保留表資料的終極解決方案
本作品採用《CC 協議》，轉載必須註明作者和本文連結