MySQL深入淺出精講觸發器用法
- WBOY轉載
- 2022-09-01 14:41:312010瀏覽
推薦學習：mysql影片教學
在實際開發中，我們常常會遇到這樣的情況：有2 個或多個相互關聯的表，如商品資訊和庫存資訊分別存放在2 個不同的資料表中，我們在新增一筆新商品記錄的時候，為了確保資料的完整性，必須同時在庫存表中新增一條庫存記錄。這樣一來，我們就必須把這兩個關聯的操作步驟寫到程式裡面，而且要用 事務 包裹起來，確保這兩個操 作成為一個 原子操作 ，要么全部執行，要么全部不執行。
要是遇到特殊情況，可能還需要對資料進行手動維護，這樣就很 容易忘記其中的一步 ，導致資料缺失。這時候，咱們可以使用觸發器。你可以建立一個觸發器，讓商品資訊資料的插入操作自動觸發庫存數 據的插入操作。這樣一來，就不用擔心因為忘記新增庫存資料而導致的資料缺失了。
觸發器概述
MySQL從 5 . 0 . 2 版本開始支援觸發器。 MySQL的觸發器和預存程序一樣，都是嵌入到MySQL伺服器的一 段程式。觸發器是由 事件觸發 某個操作，這些事件包括 INSERT 、 UPDATE 、 DELETE 事件。
所謂事件就是指 使用者的動作或觸發某項行為。如果定義了觸發程序，當資料庫執行這些語句時候，就相當於事件發生 了，就會 自動 激發觸發器執行對應的操作。當對資料表中的資料執行插入、更新和刪除操作，需要自動執行一些資料庫邏輯時，可以使用觸發器來 實作。
觸發器的建立
建立觸發器語法
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;說明：
表名 ：表示觸發器監控的物件。
BEFORE | AFTER ：表示觸發的時間。 BEFORE 表示在事件之前觸發； AFTER 表示在事件之後觸發。
INSERT | UPDATE | DELETE ：表示觸發的事件。
INSERT 表示插入記錄時觸發；
UPDATE 表示更新記錄時觸發；
DELETE 表示刪除記錄時觸發。
程式碼範例1
建立兩個張表
CREATE TABLE test_trigge r ( id INT PRIMARY KEY AUTO_INCREMENT , t_note VARCHAR ( 30 ) ) ; CREATE TABLE test_trigger_log ( id INT PRIMARY KEY AUTO_INCREMENT , t_log VARCHAR ( 30 ) ) ;
建立觸發器
DELIMITER / / CREATE TRIGGER befo_re_insert BEFORE INSERT ON test_trigger FOR EACH ROW BEGIN INSERT INTO test_trigger_log ( t_log ) VALUES ( ' befo re_inse rt ' ) ; END / / DELIMITER ;
在test_trigger資料表中插入資料
INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');
查看test_trigger_log資料表中的資料
SELECT * FROM test_trigger_log
#程式碼舉例2
建立觸發器
DELIMITER / / CREATE TRIGGER after_insert AFTER INSERT ON test_trigger FOR EACH ROW BEGIN INSERT INTO test_trigger_log ( t_log ) VALUES ( ' after_insert ' ) ; END / / DELIMITER ;
向test_trigger資料表中插入資料。
INSERT INTO test_trigger (t_note) VALUES ('测试 AFTER INSERT 触发器');
查看test_trigger_log資料表中的資料
SELECT * FROM test_trigger_log
程式碼舉例3
定義觸發器“salary_check_trigger”，基於員工表“ employees」的INSERT事件，在INSERT之前檢查將要新增的新員工薪資是否大於他領導的薪資，如果大於領導薪資，則報sqlstate_value為'HY000'的錯誤，從而使得新增失敗。
DELIMITER // CREATE TRIGGER salary_check_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN DECLARE mgrsalary DOUBLE; SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id; IF NEW.salary > mgrsalary THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; END IF; END // DELIMITER ;
上面觸發器宣告過程中的NEW關鍵字代表INSERT新增語句的新記錄。
查看刪除觸發器
方式1：查看目前資料庫的所有觸發器的定義
SHOW TRIGGERS
方式2：查看目前資料庫中某個觸發器的定義方式
SHOW CREATE TRIGGER 触发器名
方式3：從系統函式庫information_schema的TRIGGERS表中查詢「salary_check_trigger」觸發器的資訊。
SELECT * FROM information_schema.TRIGGERS;
刪除觸發器
DROP TRIGGER IF EXISTS 触发器名称
觸發器的優點
1、觸發器可以確保資料的完整性。
假設我們用 進貨單頭表 （demo.importhead）來保存進貨單的總體信息，包括進貨單編號、供貨商編號、倉庫編號、總計進貨數量、總計進貨金額和驗收日期。
用進貨單明細表（demo.importdetails）來儲存進貨商品的明細，包括進貨單編號、商品編號、進貨數
量、進貨價格和進貨金額額就不等於進貨單明細表中數量合計和金額合計了，這就是數據不一致。
為了解決這個問題，我們就可以使用觸發器，規定每當進貨單明細表有資料插入、修改和刪除的操作
#時，自動觸發2 步驟操作：
1）重新計算進貨單明細表中的數量合計和金額合計；
2）用第一步中計算出來的值更新進貨單頭表中的合計數量與合計金額。
這樣一來，進貨單頭表中的合計數量與合計金額的值，就始終與進貨單明細表中計算出來的合計數量與
合計金額的值相同，數據就是一致的，不會互相矛盾。
2、觸發器可以幫助我們記錄操作日誌。
利用觸發器，可以具體記錄什麼時間發生了什麼事。例如，記錄修改會員儲值金額的觸發器，就是一個很
好的例子。這對我們還原操作執行時的具體場景，更好地定位問題原因很有幫助。
3、觸發器還可以用在操作資料前，對資料進行合法性檢查。
例如，超市進貨的時候，需要庫管輸入進貨價格。但是，人為操作很容易犯錯，比如說在錄入數量的
時候，把條碼掃進去了；錄入金額的時候，看串了行，錄入的價格遠超售價，導致賬面上的巨虧......
這些都可以通過觸發器，在實際插入或更新操作之前，對相應的數據進行檢查，及時提示錯誤，防止
錯誤數據進入系統。
觸發器的缺點
1、觸發器最大的一個問題就是可讀性差。
因為觸發器儲存在資料庫中，並且由事件驅動，這意味著觸發器有可能 不受應用層的控制 。這對系統維護是非常有挑戰的。
例如，建立觸發器用於修改會員儲值操作。如果觸發器中的操作出了問題，會導致會員儲值金額更新失敗。我用下面的程式碼示範一下
結果顯示，系統提示錯誤，欄位「aa」不存在。
這是因為，觸發器中的資料插入操作多了一個字段，系統提示錯誤。可是，如果你不了解這個觸發器，很可能會認為是更新語句本身的問題，或是會員資訊表的結構出了問題。說不定你還會為會員資訊表添加一個叫「aa」的字段，試圖解決這個問題，結果只能是白費力。
2、相關資料的變更，可能會導致觸發器出錯。
特別是資料表結構的變更，都可能會導致觸發器出錯，進而影響資料操作的正常運作。這些都會因為觸發器本身的隱藏性，影響應用中錯誤原因排查的效率。
注意點
注意，如果在子表中定義了外鍵約束，並且外鍵指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此時修改父表被引用的鍵值或刪除父表被引用的記錄行時，也會造成子表的修改和刪除操作，此時基於子表的UPDATE和DELETE語句定義的觸發器並不會被啟動。
例如：基於子表員工表（t_employee）的DELETE語句定義了觸發器t1，而子表的部門編號（did）欄位定義了外鍵約束引用了父表部門表（t_department）的主鍵列部門編號（did），並且該外鍵加了“ONDELETE SET NULL”子句，那麼如果此時刪除父表部門表（t_department）在子表員工表（t_employee）
推薦學習：mysql影片教學
#以上是MySQL深入淺出精講觸發器用法的詳細內容。更多資訊請關注PHP中文網其他相關文章！