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:特性限制。
關於觸發程式的二進制日誌功能,請參見20.4節,「儲存子程式和觸發程式的二進制日誌功能」。
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復合語句」。
在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權限。
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版升級」 。
DROP TRIGGER語句需要SUPER權限。
在本節中,介紹了在MySQL 5.1中使用觸發程式的方法,並介紹了在使用觸發程式方面的限制。
觸發程式是與資料表有關的命名資料庫對象,當資料表上出現特定事件時,將激活該對象。在某些觸發程式的用法中,可用於檢查插入到資料表中的值,或對更新涉及的值進行計算。
觸發程式與資料表相關,當對資料表執行INSERT、DELETE或UPDATE語句時,將激活觸發程式。可以將觸發程式設置為在執行語句之前或之後激活。例如,可以在從資料表中刪除每一行之前,或在更新了每一行後激活觸發程式。
要想建立觸發程式或捨棄觸發程式,可使用CREATE TRIGGER或DROP TRIGGER語句。關於這些語句的語法,請參見21.1節,「CREATE TRIGGER語法」和21.2節,「DROP TRIGGER語法」。
下面給出了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。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。