?
本文檔使用 php中文網(wǎng)手冊(cè) 發(fā)布
目錄
- 21.1. CREATE TRIGGER語(yǔ)法
- 21.2. DROP TRIGGER語(yǔ)法
- 21.3. 使用觸發(fā)程序
MySQL 5.1包含對(duì)觸發(fā)程序的支持。觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫(kù)對(duì)象，當(dāng)表上出現(xiàn)特定事件時(shí)，將激活該對(duì)象。例如，下述語(yǔ)句將創(chuàng)建1個(gè)表和1個(gè)INSERT觸發(fā)程序。觸發(fā)程序?qū)⒉迦氡碇心骋涣械闹导釉谝黄穑?/p>
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;
在本章中，介紹了創(chuàng)建和撤銷(xiāo)觸發(fā)程序的語(yǔ)法，并給出了一些使用它們的示例。關(guān)于對(duì)觸發(fā)程序使用的限制，請(qǐng)參見(jiàn)附錄I：特性限制。
關(guān)于觸發(fā)程序的二進(jìn)制日志功能，請(qǐng)參見(jiàn)20.4節(jié)，“存儲(chǔ)子程序和觸發(fā)程序的二進(jìn)制日志功能”。
CREATE TRIGGER trigger_name trigger_time trigger_event
???ON tbl_name FOR EACH ROW trigger_stmt
觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫(kù)對(duì)象，當(dāng)表上出現(xiàn)特定事件時(shí)，將激活該對(duì)象。
觸發(fā)程序與命名為tbl_name的表相關(guān)。tbl_name必須引用永久性表。不能將觸發(fā)程序與TEMPORARY表或視圖關(guān)聯(lián)起來(lái)。
trigger_time是觸發(fā)程序的動(dòng)作時(shí)間。它可以是BEFORE或AFTER，以指明觸發(fā)程序是在激活它的語(yǔ)句之前或之后觸發(fā)。
trigger_event指明了激活觸發(fā)程序的語(yǔ)句的類(lèi)型。trigger_event可以是下述值之一：
·???????? INSERT：將新行插入表時(shí)激活觸發(fā)程序，例如，通過(guò)INSERT、LOAD DATA和REPLACE語(yǔ)句。
·???????? UPDATE：更改某一行時(shí)激活觸發(fā)程序，例如，通過(guò)UPDATE語(yǔ)句。
·???????? DELETE：從表中刪除某一行時(shí)激活觸發(fā)程序，例如，通過(guò)DELETE和REPLACE語(yǔ)句。
請(qǐng)注意，trigger_event與以表操作方式激活觸發(fā)程序的SQL語(yǔ)句并不很類(lèi)似，這點(diǎn)很重要。例如，關(guān)于INSERT的BEFORE觸發(fā)程序不僅能被INSERT語(yǔ)句激活，也能被LOAD DATA語(yǔ)句激活。
可能會(huì)造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...語(yǔ)法：BEFORE INSERT觸發(fā)程序?qū)τ诿恳恍袑⒓せ睿?/span>后跟AFTER INSERT觸發(fā)程序，或BEFORE UPDATE和AFTER UPDATE觸發(fā)程序，具體情況取決于行上是否有重復(fù)鍵。
對(duì)于具有相同觸發(fā)程序動(dòng)作時(shí)間和事件的給定表，不能有兩個(gè)觸發(fā)程序。例如，對(duì)于某一表，不能有兩個(gè)BEFORE UPDATE觸發(fā)程序。但可以有1個(gè)BEFORE UPDATE觸發(fā)程序和1個(gè)BEFORE INSERT觸發(fā)程序，或1個(gè)BEFORE UPDATE觸發(fā)程序和1個(gè)AFTER UPDATE觸發(fā)程序。
trigger_stmt是當(dāng)觸發(fā)程序激活時(shí)執(zhí)行的語(yǔ)句。如果你打算執(zhí)行多個(gè)語(yǔ)句，可使用BEGIN ... END復(fù)合語(yǔ)句結(jié)構(gòu)。這樣，就能使用存儲(chǔ)子程序中允許的相同語(yǔ)句。請(qǐng)參見(jiàn)20.2.7節(jié)，“BEGIN ... END復(fù)合語(yǔ)句”。
在MySQL 5.1中，可以編寫(xiě)包含按名稱(chēng)對(duì)表進(jìn)行直接引用的觸發(fā)程序，如下例中所示的名為testref的觸發(fā)程序：
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個(gè)表中的數(shù)據(jù)如下：
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，能夠引用與觸發(fā)程序相關(guān)的表中的列。OLD.col_name在更新或刪除它之前，引用已有行中的1列。NEW.col_name在更新它之后引用將要插入的新行的1列或已有行的1列。
激活觸發(fā)程序時(shí)，對(duì)于觸發(fā)程序引用的所有OLD和NEW列，需要具有SELECT權(quán)限，對(duì)于作為SET賦值目標(biāo)的所有NEW列，需要具有UPDATE權(quán)限。
注釋?zhuān)?/span>目前，觸發(fā)程序不會(huì)被級(jí)聯(lián)的 外鍵動(dòng)作激活。該限制將會(huì)被盡早放寬。
CREATE TRIGGER語(yǔ)句需要SUPER權(quán)限。
DROP TRIGGER [schema_name.]trigger_name
舍棄觸發(fā)程序。方案名稱(chēng)（schema_name）是可選的。如果省略了schema（方案），將從當(dāng)前方案中舍棄觸發(fā)程序。
注釋?zhuān)?/span>從MySQL 5.0.10之前的MySQL版本升級(jí)到5.0.10或更高版本時(shí)（包括所有的MySQL 5.1版本），必須在升級(jí)之前舍棄所有的觸發(fā)程序，并在隨后重新創(chuàng)建它們，否則，在升級(jí)之后DROP TRIGGER不工作。關(guān)于推薦的升級(jí)步驟，請(qǐng)參見(jiàn)2.10.1節(jié)，“從5.0版升級(jí)” 。
DROP TRIGGER語(yǔ)句需要SUPER權(quán)限。
在本節(jié)中，介紹了在MySQL 5.1中使用觸發(fā)程序的方法，并介紹了在使用觸發(fā)程序方面的限制。
觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫(kù)對(duì)象，當(dāng)表上出現(xiàn)特定事件時(shí)，將激活該對(duì)象。在某些觸發(fā)程序的用法中，可用于檢查插入到表中的值，或?qū)Ω律婕暗闹颠M(jìn)行計(jì)算。
觸發(fā)程序與表相關(guān)，當(dāng)對(duì)表執(zhí)行INSERT、DELETE或UPDATE語(yǔ)句時(shí)，將激活觸發(fā)程序。可以將觸發(fā)程序設(shè)置為在執(zhí)行語(yǔ)句之前或之后激活。例如，可以在從表中刪除每一行之前，或在更新了每一行后激活觸發(fā)程序。
要想創(chuàng)建觸發(fā)程序或舍棄觸發(fā)程序，可使用CREATE TRIGGER或DROP TRIGGER語(yǔ)句。關(guān)于這些語(yǔ)句的語(yǔ)法，請(qǐng)參見(jiàn)21.1節(jié)，“CREATE TRIGGER語(yǔ)法”和21.2節(jié)，“DROP TRIGGER語(yǔ)法”。
下面給出了1個(gè)簡(jiǎn)單的示例，在該示例中，針對(duì)INSERT語(yǔ)句，將觸發(fā)程序和表關(guān)聯(lián)了起來(lái)。其作用相當(dāng)于累加器，能夠?qū)⒉迦氡碇心骋涣械闹导悠饋?lái)。
在下面的語(yǔ)句中，創(chuàng)建了1個(gè)表，并為表創(chuàng)建了1個(gè)觸發(fā)程序：
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語(yǔ)句創(chuàng)建了與賬戶表相關(guān)的、名為ins_sum的觸發(fā)程序。它還包括一些子句，這些子句指定了觸發(fā)程序激活時(shí)間、觸發(fā)程序事件、以及激活觸發(fā)程序時(shí)作些什么：
·???????? 關(guān)鍵字BEFORE指明了觸發(fā)程序的動(dòng)作時(shí)間。在本例中，應(yīng)在將每一行插入表之前激活觸發(fā)程序。這類(lèi)允許的其他關(guān)鍵字是AFTER。
·???????? 關(guān)鍵字INSERT指明了激活觸發(fā)程序的事件。在本例中，INSERT語(yǔ)句將導(dǎo)致觸發(fā)程序的激活。你也可以為DELETE和UPDATE語(yǔ)句創(chuàng)建觸發(fā)程序。
·???????? 跟在FOR EACH ROW后面的語(yǔ)句定義了每次激活觸發(fā)程序時(shí)將執(zhí)行的程序，對(duì)于受觸發(fā)語(yǔ)句影響的每一行執(zhí)行一次。在本例中，觸發(fā)的語(yǔ)句是簡(jiǎn)單的SET語(yǔ)句，負(fù)責(zé)將插入amount列的值加起來(lái)。該語(yǔ)句將列引用為NEW.amount，意思是“將要插入到新行的amount列的值”。
要想使用觸發(fā)程序，將累加器變量設(shè)置為0，執(zhí)行INSERT語(yǔ)句，然后查看變量的值：
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?????????????? |
+-----------------------+
在本例中，執(zhí)行了INSERT語(yǔ)句后，@sum的值是14.98 + 1937.50 – 100，或1852.48。
要想銷(xiāo)毀觸發(fā)程序，可使用DROP TRIGGER語(yǔ)句。如果觸發(fā)程序不在默認(rèn)的方案中，必須指定方案名稱(chēng)：
mysql> DROP TRIGGER test.ins_sum;
觸發(fā)程序名稱(chēng)存在于方案的名稱(chēng)空間內(nèi)，這意味著，在1個(gè)方案中，所有的觸發(fā)程序必須具有唯一的名稱(chēng)。位于不同方案中的觸發(fā)程序可以具有相同的名稱(chēng)。
在1個(gè)方案中，所有的觸發(fā)程序名稱(chēng)必須是唯一的，除了該要求外，對(duì)于能夠創(chuàng)建的觸發(fā)程序的類(lèi)型還存在其他限制。尤其是，對(duì)于具有相同觸發(fā)時(shí)間和觸發(fā)事件的表，不能有2個(gè)觸發(fā)程序。例如，不能為某一表定義2個(gè)BEFORE INSERT觸發(fā)程序或2個(gè)AFTER UPDATE觸發(fā)程序。這幾乎不是有意義的限制，這是因?yàn)?，通過(guò)在FOR EACH ROW之后使用BEGIN ... END復(fù)合語(yǔ)句結(jié)構(gòu)，能夠定義執(zhí)行多條語(yǔ)句的觸發(fā)程序。請(qǐng)參見(jiàn)本節(jié)后面給出的示例。
此外，激活觸發(fā)程序時(shí)，對(duì)觸發(fā)程序執(zhí)行的語(yǔ)句也存在一些限制：
·???????? 觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲(chǔ)程序，也不能使用采用CALL語(yǔ)句的動(dòng)態(tài)SQL（允許存儲(chǔ)程序通過(guò)參數(shù)將數(shù)據(jù)返回觸發(fā)程序）。
·???????? 觸發(fā)程序不能使用以顯式或隱式方式開(kāi)始或結(jié)束事務(wù)的語(yǔ)句，如START TRANSACTION、COMMIT或ROLLBACK。
使用OLD和NEW關(guān)鍵字，能夠訪問(wèn)受觸發(fā)程序影響的行中的列（OLD和NEW不區(qū)分大小寫(xiě)）。在INSERT觸發(fā)程序中，僅能使用NEW.col_name，沒(méi)有舊行。在DELETE觸發(fā)程序中，僅能使用OLD.col_name，沒(méi)有新行。在UPDATE觸發(fā)程序中，可以使用OLD.col_name來(lái)引用更新前的某一行的列，也能使用NEW.col_name來(lái)引用更新后的行中的列。
用OLD命名的列是只讀的。你可以引用它，但不能更改它。對(duì)于用NEW命名的列，如果具有SELECT權(quán)限，可引用它。在BEFORE觸發(fā)程序中，如果你具有UPDATE權(quán)限，可使用“SET NEW.col_name = value”更改它的值。這意味著，你可以使用觸發(fā)程序來(lái)更改將要插入到新行中的值，或用于更新行的值。
在BEFORE觸發(fā)程序中，AUTO_INCREMENT列的NEW值為0，不是實(shí)際插入新記錄時(shí)將自動(dòng)生成的序列號(hào)。
OLD和NEW是對(duì)觸發(fā)程序的MySQL擴(kuò)展。
通過(guò)使用BEGIN ... END結(jié)構(gòu)，能夠定義執(zhí)行多條語(yǔ)句的觸發(fā)程序。在BEGIN塊中，還能使用存儲(chǔ)子程序中允許的其他語(yǔ)法，如條件和循環(huán)等。但是，正如存儲(chǔ)子程序那樣，定義執(zhí)行多條語(yǔ)句的觸發(fā)程序時(shí)，如果使用mysql程序來(lái)輸入觸發(fā)程序，需要重新定義語(yǔ)句分隔符，以便能夠在觸發(fā)程序定義中使用字符“;”。在下面的示例中，演示了這些要點(diǎn)。在該示例中，定義了1個(gè)UPDATE觸發(fā)程序，用于檢查更新每一行時(shí)將使用的新值，并更改值，使之位于0～100的范圍內(nèi)。它必須是BEFORE觸發(fā)程序，這是因?yàn)?，需要在將值用于更新行之前?duì)其進(jìn)行檢查：
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 ;
較為簡(jiǎn)單的方法是，單獨(dú)定義存儲(chǔ)程序，然后使用簡(jiǎn)單的CALL語(yǔ)句從觸發(fā)程序調(diào)用存儲(chǔ)程序。如果你打算從數(shù)個(gè)觸發(fā)程序內(nèi)部調(diào)用相同的子程序，該方法也很有幫助。
在觸發(fā)程序的執(zhí)行過(guò)程中，MySQL處理錯(cuò)誤的方式如下：
·???????? 如果BEFORE觸發(fā)程序失敗，不執(zhí)行相應(yīng)行上的操作。
·???????? 僅當(dāng)BEFORE觸發(fā)程序（如果有的話）和行操作均已成功執(zhí)行，才執(zhí)行AFTER觸發(fā)程序。
·???????? 如果在BEFORE或AFTER觸發(fā)程序的執(zhí)行過(guò)程中出現(xiàn)錯(cuò)誤，將導(dǎo)致調(diào)用觸發(fā)程序的整個(gè)語(yǔ)句的失敗。
·???????? 對(duì)于事務(wù)性表，如果觸發(fā)程序失?。ㄒ约坝纱藢?dǎo)致的整個(gè)語(yǔ)句的失?。?，該語(yǔ)句所執(zhí)行的所有更改將回滾。對(duì)于非事務(wù)性表，不能執(zhí)行這類(lèi)回滾，因而，即使語(yǔ)句失敗，失敗之前所作的任何更改依然有效。
這是MySQL參考手冊(cè)的翻譯版本，關(guān)于MySQL參考手冊(cè)，請(qǐng)?jiān)L問(wèn)dev.mysql.com。原始參考手冊(cè)為英文版，與英文版參考手冊(cè)相比，本翻譯版可能不是最新的。