MySQL 5.0-觸發器

Conventions and Styles約定和編程風格

　　每次我想要示範實際代碼時，我會對mysql用戶端的螢幕就出現的代碼進行調整，將字型改成Courier，使他們看起來與普通文本不一樣（讓大家區別程式碼和本文）。在這裡舉個例子：

mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)

　　如果執行個體比較大，則需要在某些行和段落間加註釋，同時我會用將"<--"符號放在頁面的右邊以表示強調。例如：

mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//
Query OK, 0 rows affected (0.00 sec)

　　有時候我會將例子中的"mysql>"和"->"這些系統顯示去掉，你可以直接將代碼複製到mysql用戶端程式中（如果你現在所讀的不是電子版的，可以在mysql.com網站下載相關指令碼）

　　所以的例子都已經在Suse 9.2 Linux、Mysql 5.0.3公用版上測試通過。在您閱讀本書的時候，Mysql已經有更高的版本，同時能支援更多OS了，包括Windows，Sparc，HP-UX。因此這裡的例子將能正常的運行在您的電腦上。但如果運行仍然出現故障，可以諮詢你認識的資深Mysql使用者，這樣就能得到比較好的支援和協助。

Why Triggers 為什麼要用觸發器

　　我們在MySQL 5.0中包含對觸發器的支援是由於以下原因:

　　MySQL早期版本的使用者長期有需要觸發器的要求。
　　我們曾經確保支援所有ANSI標準的特性。
　　您可以使用它來檢查或預防壞的資料進入資料庫。
　　您可以改變或者取消INSERT, UPDATE以及DELETE語句。
　　您可以在一個會話中監視資料改變的動作。

　　在這裡我假定大家都讀過"MySQL新特性"叢書的第一集--"MySQL預存程序"，那麼大家都應該知道MySQL至此預存程序和函數，那是很重要的知識，因為在觸發器中你可以使用在函數中使用的語句。特別舉個例子：

　　複合陳述式(BEGIN / END)是合法的.

　　流量控制（Flow-of-control）語句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.

　　變數聲明(DECLARE)以及指派(SET)是合法的.

　　允許條件聲明.

　　異常處理聲明也是允許的.

　　但是在這裡要記住函數有受限條件:不能在函數中訪問表.因此在函數中使用以下語句是非法的。

ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE

DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL

LOCK OPTIMIZE REPAIR REPLACE REVOKE

ROLLBACK SAVEPOINT 'SELECT FROM table'

'SET system variable' 'SET TRANSACTION'

SHOW 'START TRANSACTION' TRUNCATE UPDATE

　　在觸發器中也有完全一樣的限制.

　　觸發器相對而言比較新，因此會有（bugs）缺陷.所以我在這裡給大家警告，就像我在預存程序書中所說那樣.不要在含有重要資料的資料庫中使用這個觸發器，如果需要的話在一些以測試為目的的資料庫上使用，同時在你對錶建立觸發器時確認這些資料庫是預設的。

Syntax 文法

　　1. Syntax: Name 文法：命名規則

CREATE TRIGGER <觸發器名稱> <--
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發器SQL語句>

　　觸發器必須有名字，最多64個字元，可能後面會附有分隔字元.它和MySQL中其他對象的命名方式基本相象.

　　這裡我有個習慣：就是用表的名字＋'_'＋觸發器類型的縮寫.因此如果是表t26，觸發器是在事件UPDATE（參考下面的點（2）和（3））之前（BEFORE）的，那麼它的名字就是t26_bu。

　　2. Syntax: Time 文法：觸發時間

CREATE TRIGGER <觸發器名稱>
{ BEFORE | AFTER } <--
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發的SQL語句>

　　觸發器有執行的時間設定：可以設定為事件發生前或後。

　　3. Syntax: Event文法：事件

CREATE TRIGGER <觸發器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } <--
ON <表名稱>
FOR EACH ROW
<觸發的SQL語句>

　　同樣也能設定觸發的事件：它們可以在執行insert、update或delete的過程中觸發。

　　4. Syntax: Table 文法：表

CREATE TRIGGER <觸發器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱> <--
FOR EACH ROW
<觸發的SQL語句>

　　觸發器是屬於某一個表的:當在這個表上執行插入、更新或刪除操作的時候就導致觸發器的啟用.
我們不能給同一張表的同一個事件安排兩個觸發器。

　　5. Syntax: Granularity 文法：（ ：（ 步長）觸發間隔

CREATE TRIGGER <觸發器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW <--
<觸發的SQL語句>

　　觸發器的執行間隔：FOR EACH ROW子句通知觸發器每隔一行執行一次動作，而不是對整個表執行一次。

　　6. Syntax: Statement 文法：語句

CREATE TRIGGER <觸發器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發的SQL語句> <--

　　觸發器包含所要觸發的SQL語句：這裡的語句可以是任何合法的語句，包括複合陳述式，但是這裡的語句受的限制和函數的一樣。

Privileges許可權

　　你必須擁有相當大的許可權才能建立觸發器（CREATE TRIGGER）。如果你已經是Root使用者，那麼就足夠了。這跟SQL的標準有所不同，我也希望能儘快改成標準的。

　　因此在下一個版本的MySQL中，你完全有可能看到有一種叫做CREATE TRIGGER的新許可權。然後通過這樣的方法賦予：

GRANT CREATE TRIGGER ON <表名稱> TO <使用者或使用者列表>;

　　也可以通過這樣收回許可權：

REVOKE CREATE TRIGGER ON <表名稱> FROM <使用者或使用者列表>;

Referring to OLD and NEW columns 關於舊的和新建立的列的標識

　　在觸發器的SQL語句中，你可以關聯表中的任意列。但你不能僅使用列的名稱去標識，那會使系統混淆，因為那裡可能會有列的新名（這可能正是你要修改的，你的動作可能正是要修改列名），還有列的舊名存在。因此你必須用這樣的文法來標識：

　　"NEW . column_name"或者"OLD . column_name".這樣在技術上處理（NEW | OLD . column_name）新和舊的列名屬於建立了過渡變數（"transition variables"）。

　　對於INSERT語句,只有NEW是合法的；對於DELETE語句，只有OLD才合法；而UPDATE語句可以在和NEW以及OLD同時使用。下面是一個UPDATE中同時使用NEW和OLD的例子。

CREATE TRIGGER t21_au
BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD . s1;
SET @new = NEW.s1;
END;//

　　現在如果t21表中的s1列的值是55，那麼執行了"UPDATE t21 SET s1 = s1 + 1"之後@old的值會變成55，而@new的值將會變成56。

Example of CREATE and INSERT CREATE和INSERT的例子

　　CREATE table with trigger建立有觸發器的表

　　這裡所有的常式中我都假定大家的分隔字元已經設定成//（DELIMITER //）。

CREATE TABLE t22 (s1 INTEGER)//

CREATE TRIGGER t22_bi
BEFORE INSERT ON t22
FOR EACH ROW
BEGIN
SET @x = 'Trigger was activated!';
SET NEW.s1 = 55;
END;//

　　在最開始我建立了一個名字為t22的表，然後在表t22上建立了一個觸發器t22_bi，當我們要向表中的行插入時，觸發器就會被啟用，執行將s1列的值改為55的動作。

　　INSERT on table w ith a trigger使用觸發器執行插入動作

mysql> INSERT INTO t22 VALUES (1)//

　　讓我們看如果向表t2中插入一行資料觸發器對應的表會怎麼樣？

　　這裡的插入的動作是很常見的，我們不需要觸發器的許可權來執行它。甚至不需要知道是否有觸發器關聯。

mysql> SELECT @x, t22.* FROM t22//
+------------------------+------+
| @x | s1 |
+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)

　　大家可以看到INSERT動作之後的結果，和我們預期的一樣，x標記被改動了，同時這裡插入的資料不是我們開始輸入的插入資料，而是觸發器自己的資料。

Example of a "check" constraint
"check"完整性條件約束例子

　　What's a "check" constraint 什麼是"check"約束

　　在標準的SQL語言中，我們可以在（CREATE TABLE）建立表的過程中使用"CHECK (condition)"，
例如：

CREATE TABLE t25
(s1 INT, s2 CHAR(5), PRIMARY KEY (s1),
CHECK (LEFT(s2,1)='A'))
ENGINE=INNODB;

　　這裡CHECK的意思是"當s2列的最左邊的字元不是'A'時，insert和update語句都會非法"，MySQL的視圖不支援CHECK，我個人是很希望它能支援的。但如果你很需要在表中使用這樣的功能，我建議大家使用觸發器來實現。

CREATE TABLE t25
(s1 INT, s2 CHAR(5),
PRIMARY KEY (s1))
ENGINE=INNODB//

CREATE TRIGGER t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

CREATE TRIGGER t25_bu
BEFORE UPDATE ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

　　我只需要使用BEFORE INSERT和BEFORE UPDATE語句就行了，刪除了觸發器不會對錶有影響，同時AFTER的觸發器也不能修改NEW的過程變數（transition variables）。為了啟用觸發器，我執行了向表中的行插入s1＝0的資料，之後只要執行符合LEFT(s2,1) <> 'A'條件的動作都會失敗：

INSERT INTO t25 VALUES (0,'a') /* priming the pump */ //
INSERT INTO t25 VALUES (5,'b') /* gets error '23000' */ //

Don't Believe The Old MySQL Manual
該拋棄舊的MySQL的手冊了

　　我在這裡警告大家不要相信過去的MySQL手冊中所說的了。我們已經去掉了關於觸發器的錯誤的語句，但是仍舊有很多舊版本的手冊在網上，舉個例子，這是一個德國的Url上的：

　　http://dev.mysql.com/doc/mysql/de/ANSI_diff_Triggers.html.

　　這個手冊上說觸發器就是預存程序，忘掉吧，你也已經看見了，觸發器就是觸發器，而預存程序還是預存程序。

　　手冊上還說觸發器可以從其他表上來刪除，或者是當你刪除一個事務的時候激發，無論他說的是什麼意思，忘掉吧，MySQL不會去實現這些的。

　　最後關於說使用觸發器會對查詢速度產生影響的說法也是錯的，觸發器不會對查詢產生任何影響。

　　Bugs
　　（不好的東西就不翻譯了）
　　On December 14 2004, I did an "Advanced Search" in http://bugs.mysql.com for 'trigger' or
　　'triggers', I found that there were 17 active bugs as of that date. Of course they might disappear
　　before you read this, but just in case they haven't, I'll mention the important ones. If they're still
　　there, you'll have to work around them when you're trying triggers.

　　Bug#5859 DROP TABLE does not drop triggers.
　　（刪除表的時候沒有自動刪除觸發器）
　　When you drop a table, dropping the table's triggers should be automatic.

　　Bug#5892 Triggers have the wrong namespace.
　　（觸發器的命名空間有錯，你必須在前面加上表的名字才能刪除觸發器，下面是例子）
　　You have to say "DROP TRIGGER <table name> . <trigger name>".
　　The correct way is "DROP TRIGGER <trigger name>".

　　Bug#5894 Triggers with altered tables cause corrupt databases.
　　（觸發器對錶的改變可能會造成資料庫資料被破壞）
　　Do not alter a table that has a trigger on it, until you know this is fixed.