21. 觸發程序 · MySQL 5.1參考手冊

### 第21章：觸發程序 ** 目錄** [21.1. CREATE TRIGGER語法](#)[21.2. DROP TRIGGER語法](#)[ 21.3. 使用觸發程序](#) 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)。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。