### 第21章:觸發程序
MySQL 5.1包含對觸發程序的支持。觸發程序是與表有關的命名數據庫對象,當表上出現特定事件時,將激活該對象。例如,下述語句將創建1個表和1個INSERT觸發程序。觸發程序將插入表中某一列的值加在一起:
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
??? -> FOR EACH ROW SET @sum = @sum + NEW.amount;
在本章中,介紹了創建和撤銷觸發程序的語法,并給出了一些使用它們的示例。關于對觸發程序使用的限制,請參見[附錄I:](#)[_特性限制_](# "Appendix?I.?Feature Restrictions")。
關于觸發程序的二進制日志功能,請參見[20.4節,“存儲子程序和觸發程序的二進制日志功能”](# "20.4.?Binary Logging of Stored Routines and Triggers")。
### 21.1.?CREATE TRIGGER語法
CREATE TRIGGER trigger_name trigger_time trigger_event
???ON tbl_name FOR EACH ROW trigger_stmt
觸發程序是與表有關的命名數據庫對象,當表上出現特定事件時,將激活該對象。
觸發程序與命名為_tbl_name_的表相關。_tbl_name_必須引用永久性表。不能將觸發程序與TEMPORARY表或視圖關聯起來。
_trigger_time_是觸發程序的動作時間。它可以是BEFORE或AFTER,以指明觸發程序是在激活它的語句之前或之后觸發。
_trigger_event_指明了激活觸發程序的語句的類型。_trigger_event_可以是下述值之一:
·???????? INSERT:將新行插入表時激活觸發程序,例如,通過INSERT、LOAD DATA和REPLACE語句。
·???????? UPDATE:更改某一行時激活觸發程序,例如,通過UPDATE語句。
·???????? DELETE:從表中刪除某一行時激活觸發程序,例如,通過DELETE和REPLACE語句。
請注意,_trigger_event_與以表操作方式激活觸發程序的SQL語句并不很類似,這點很重要。例如,關于INSERT的BEFORE觸發程序不僅能被INSERT語句激活,也能被LOAD DATA語句激活。
可能會造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...語法:BEFORE INSERT觸發程序對于每一行將激活,后跟AFTER INSERT觸發程序,或BEFORE UPDATE和AFTER UPDATE觸發程序,具體情況取決于行上是否有重復鍵。
對于具有相同觸發程序動作時間和事件的給定表,不能有兩個觸發程序。例如,對于某一表,不能有兩個BEFORE UPDATE觸發程序。但可以有1個BEFORE UPDATE觸發程序和1個BEFORE INSERT觸發程序,或1個BEFORE UPDATE觸發程序和1個AFTER UPDATE觸發程序。
_trigger_stmt_是當觸發程序激活時執行的語句。如果你打算執行多個語句,可使用BEGIN ... END復合語句結構。這樣,就能使用存儲子程序中允許的相同語句。請參見[20.2.7節,“BEGIN ... END復合語句”](# "20.2.7.?BEGIN ... END Compound Statement")。
在MySQL 5.1中,可以編寫包含按名稱對表進行直接引用的觸發程序,如下例中所示的名為testref的觸發程序:
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
? a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
??b4 INT DEFAULT 0
);
?
DELIMITER |
?
CREATE TRIGGER testref BEFORE INSERT ON test1
? FOR EACH ROW BEGIN
??? INSERT INTO test2 SET a2 = NEW.a1;
??? DELETE FROM test3 WHERE a3 = NEW.a1;?
????UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
? END
|
?
DELIMITER ;
?
INSERT INTO test3 (a3) VALUES
??(NULL), (NULL), (NULL), (NULL), (NULL),
??(NULL), (NULL), (NULL), (NULL), (NULL);
?
INSERT INTO test4 (a4) VALUES
??(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
如果將下述值插入表test1,如下所示:
mysql> INSERT INTO test1 VALUES
????-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8? Duplicates: 0? Warnings: 0
那么4個表中的數據如下:
mysql> SELECT * FROM test1;
+------+
| a1?? |
+------+
|??? 1 |
|??? 3 |
|??? 1 |
|??? 7 |
|??? 1 |
|??? 8 |
|??? 4 |
|??? 4 |
+------+
8 rows in set (0.00 sec)
?
mysql> SELECT * FROM test2;
+------+
| a2?? |
+------+
|??? 1 |
|??? 3 |
|??? 1 |
|??? 7 |
|??? 1 |
|??? 8 |
|??? 4 |
|??? 4 |
+------+
8 rows in set (0.00 sec)
?
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|? 2 |
|? 5 |
|? 6 |
|? 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
?
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4?? |
+----+------+
|? 1 |??? 3 |
|? 2 |??? 0 |
|? 3 |??? 1 |
|? 4 |??? 2 |
|? 5 |??? 0 |
|? 6 |??? 0 |
|? 7 |??? 1 |
|? 8 |??? 1 |
|? 9 |??? 0 |
| 10 |??? 0 |
+----+------+
10 rows in set (0.00 sec)
使用別名OLD和NEW,能夠引用與觸發程序相關的表中的列。OLD._col_name_在更新或刪除它之前,引用已有行中的1列。NEW._col_name_在更新它之后引用將要插入的新行的1列或已有行的1列。
激活觸發程序時,對于觸發程序引用的所有OLD和NEW列,需要具有SELECT權限,對于作為SET賦值目標的所有NEW列,需要具有UPDATE權限。
**注釋:**目前,觸發程序不會被級聯的 外鍵動作激活。該限制將會被盡早放寬。
CREATE TRIGGER語句需要SUPER權限。
### 21.2.?DROP TRIGGER語法
DROP TRIGGER [schema_name.]trigger_name
舍棄觸發程序。方案名稱(schema_name)是可選的。如果省略了schema(方案),將從當前方案中舍棄觸發程序。
_注釋:_從MySQL 5.0.10之前的MySQL版本升級到5.0.10或更高版本時(包括所有的MySQL 5.1版本),必須在升級之前舍棄所有的觸發程序,并在隨后重新創建它們,否則,在升級之后DROP TRIGGER不工作。關于推薦的升級步驟,請參見[2.10.1節,“從5.0版升級”](# "2.10.1.?Upgrading from Version 5.0") 。
DROP TRIGGER語句需要SUPER權限。
### 21.3.?使用觸發程序
在本節中,介紹了在MySQL 5.1中使用觸發程序的方法,并介紹了在使用觸發程序方面的限制。
觸發程序是與表有關的命名數據庫對象,當表上出現特定事件時,將激活該對象。在某些觸發程序的用法中,可用于檢查插入到表中的值,或對更新涉及的值進行計算。
觸發程序與表相關,當對表執行INSERT、DELETE或UPDATE語句時,將激活觸發程序。可以將觸發程序設置為在執行語句之前或之后激活。例如,可以在從表中刪除每一行之前,或在更新了每一行后激活觸發程序。
要想創建觸發程序或舍棄觸發程序,可使用CREATE TRIGGER或DROP TRIGGER語句。關于這些語句的語法,請參見[21.1節,“CREATE TRIGGER語法”](# "21.1.?CREATE TRIGGER Syntax")和[21.2節,“DROP TRIGGER語法”](# "21.2.?DROP TRIGGER Syntax")。
下面給出了1個簡單的示例,在該示例中,針對INSERT語句,將觸發程序和表關聯了起來。其作用相當于累加器,能夠將插入表中某一列的值加起來。
在下面的語句中,創建了1個表,并為表創建了1個觸發程序:
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
??? -> FOR EACH ROW SET @sum = @sum + NEW.amount;
CREATE TRIGGER語句創建了與賬戶表相關的、名為ins_sum的觸發程序。它還包括一些子句,這些子句指定了觸發程序激活時間、觸發程序事件、以及激活觸發程序時作些什么:
·???????? 關鍵字BEFORE指明了觸發程序的動作時間。在本例中,應在將每一行插入表之前激活觸發程序。這類允許的其他關鍵字是AFTER。
·???????? 關鍵字INSERT指明了激活觸發程序的事件。在本例中,INSERT語句將導致觸發程序的激活。你也可以為DELETE和UPDATE語句創建觸發程序。
·???????? 跟在FOR EACH ROW后面的語句定義了每次激活觸發程序時將執行的程序,對于受觸發語句影響的每一行執行一次。在本例中,觸發的語句是簡單的SET語句,負責將插入amount列的值加起來。該語句將列引用為NEW.amount,意思是“將要插入到新行的amount列的值”。
要想使用觸發程序,將累加器變量設置為0,執行INSERT語句,然后查看變量的值:
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48?????????????? |
+-----------------------+
在本例中,執行了INSERT語句后,@sum的值是14.98 + 1937.50 – 100,或1852.48。
要想銷毀觸發程序,可使用DROP TRIGGER語句。如果觸發程序不在默認的方案中,必須指定方案名稱:
mysql> DROP TRIGGER test.ins_sum;
觸發程序名稱存在于方案的名稱空間內,這意味著,在1個方案中,所有的觸發程序必須具有唯一的名稱。位于不同方案中的觸發程序可以具有相同的名稱。
在1個方案中,所有的觸發程序名稱必須是唯一的,除了該要求外,對于能夠創建的觸發程序的類型還存在其他限制。尤其是,對于具有相同觸發時間和觸發事件的表,不能有2個觸發程序。例如,不能為某一表定義2個BEFORE INSERT觸發程序或2個AFTER UPDATE觸發程序。這幾乎不是有意義的限制,這是因為,通過在FOR EACH ROW之后使用BEGIN ... END復合語句結構,能夠定義執行多條語句的觸發程序。請參見本節后面給出的示例。
此外,激活觸發程序時,對觸發程序執行的語句也存在一些限制:
·???????? 觸發程序不能調用將數據返回客戶端的存儲程序,也不能使用采用CALL語句的動態SQL(允許存儲程序通過參數將數據返回觸發程序)。
·???????? 觸發程序不能使用以顯式或隱式方式開始或結束事務的語句,如START TRANSACTION、COMMIT或ROLLBACK。
使用OLD和NEW關鍵字,能夠訪問受觸發程序影響的行中的列(OLD和NEW不區分大小寫)。在INSERT觸發程序中,僅能使用NEW._col_name_,沒有舊行。在DELETE觸發程序中,僅能使用OLD._col_name_,沒有新行。在UPDATE觸發程序中,可以使用OLD._col_name_來引用更新前的某一行的列,也能使用NEW._col_name_來引用更新后的行中的列。
用OLD命名的列是只讀的。你可以引用它,但不能更改它。對于用NEW命名的列,如果具有SELECT權限,可引用它。在BEFORE觸發程序中,如果你具有UPDATE權限,可使用“SET NEW._col_name_ = _value_”更改它的值。這意味著,你可以使用觸發程序來更改將要插入到新行中的值,或用于更新行的值。
在BEFORE觸發程序中,AUTO_INCREMENT列的NEW值為0,不是實際插入新記錄時將自動生成的序列號。
OLD和NEW是對觸發程序的MySQL擴展。
通過使用BEGIN ... END結構,能夠定義執行多條語句的觸發程序。在BEGIN塊中,還能使用存儲子程序中允許的其他語法,如條件和循環等。但是,正如存儲子程序那樣,定義執行多條語句的觸發程序時,如果使用mysql程序來輸入觸發程序,需要重新定義語句分隔符,以便能夠在觸發程序定義中使用字符“;”。在下面的示例中,演示了這些要點。在該示例中,定義了1個UPDATE觸發程序,用于檢查更新每一行時將使用的新值,并更改值,使之位于0~100的范圍內。它必須是BEFORE觸發程序,這是因為,需要在將值用于更新行之前對其進行檢查:
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
??? -> FOR EACH ROW
??? -> BEGIN
??? ->? ???IF NEW.amount < 0 THEN
??? ->?????? ??SET NEW.amount = 0;
??? ->???? ELSEIF NEW.amount > 100 THEN
??? ->?????? ??SET NEW.amount = 100;
??? ->???? END IF;
??? -> END;//
mysql> delimiter ;
較為簡單的方法是,單獨定義存儲程序,然后使用簡單的CALL語句從觸發程序調用存儲程序。如果你打算從數個觸發程序內部調用相同的子程序,該方法也很有幫助。
在觸發程序的執行過程中,MySQL處理錯誤的方式如下:
·???????? 如果BEFORE觸發程序失敗,不執行相應行上的操作。
·???????? 僅當BEFORE觸發程序(如果有的話)和行操作均已成功執行,才執行AFTER觸發程序。
·???????? 如果在BEFORE或AFTER觸發程序的執行過程中出現錯誤,將導致調用觸發程序的整個語句的失敗。
·???????? 對于事務性表,如果觸發程序失敗(以及由此導致的整個語句的失敗),該語句所執行的所有更改將回滾。對于非事務性表,不能執行這類回滾,因而,即使語句失敗,失敗之前所作的任何更改依然有效。
這是MySQL參考手冊的翻譯版本,關于MySQL參考手冊,請訪問[dev.mysql.com](http://dev.mysql.com/doc/mysql/en)。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。
- 前言
- 1. 一般信息
- 2. 安裝MySQL
- 3. 教程
- 4. MySQL程序概述
- 5. 數據庫管理
- 6. MySQL中的復制
- 7. 優化
- 8. 客戶端和實用工具程序
- 9. 語言結構
- 10. 字符集支持
- 11. 列類型
- 12. 函數和操作符
- 13. SQL語句語法
- 14. 插件式存儲引擎體系結構
- 15. 存儲引擎和表類型
- 16. 編寫自定義存儲引擎
- 17. MySQL簇
- 18. 分區
- 19. MySQL中的空間擴展
- 20. 存儲程序和函數
- 21. 觸發程序
- 22. 視圖
- 23. INFORMATION_SCHEMA信息數據庫
- 24. 精度數學
- 25. API和庫
- 26. 連接器
- 27. 擴展MySQL
- A. 問題和常見錯誤
- B. 錯誤代碼和消息
- C. 感謝
- D. MySQL變更史
- E. 移植到其他系統
- F. 環境變量
- G. MySQL正則表達式
- H. MySQL中的限制
- I. 特性限制
- J. GNU通用公共許可
- K. MySQL FLOSS許可例外
- 索引