**目錄**
[TOC]
# 1 Triggers的應用
在“cmdev”資料中有一個“emplog”表格,如果有人執行任何修改“cmdev.emp”表格資料的動作,都要新增一筆訊息到“cmdev.emplog”表格中,查詢這個表格的資料,就可以知道在什么時候曾經修改過“cmdev.emp”表格中的資料:
| 字段名稱 | 型態 | NULL | 索引 | 默認值 | 其它資訊 | 說明 |
| --- | --- | --- | --- | --- | --- | --- |
| logno | bigint(20) | NO | PRI | NULL | auto_increment | 紀錄編號 |
| logdt | timestamp | NO | | CURRENT_TIMESTAMP | | 日期時間 |
| message | varchar(64) | YES | | NULL | | 訊息 |
要完成這樣的需求,每一次修改“cmdev.emp”表格資料時,你都必需執行下列的工作:
[](http://box.kancloud.cn/2015-07-18_55a9d77c279e6.png)
要完成這樣的需求,你可以使用stored routines來處理修改與新增紀錄的工作,或是在應用程式中撰寫程式來解決。不過都會是一件很麻煩的事情,而且比較容易造成遺漏紀錄的情況。
MySQL數據庫提供一種特別的數據庫元件,稱為“triggers”,一般會把它稱為“觸發器”。Triggers可以讓你先把一些在特定狀況要執行的敘述儲存起來,MySQL數據庫會在正確的時機自動幫你執行這些敘述:
[](http://box.kancloud.cn/2015-07-18_55a9d7852e89f.png)
以上列討論的需求來說,每一次修改“cmdev.emp”表格資料,都必須新增一筆紀錄到“cmdev.emplog”表格中。這個需求的主角是“cmdev.emp”表格,所以你可以為這個表格建立一個trigger元件;因為是在修改資料的情況時才需要執行特定的工作,所以你要選擇“UPDATE trigger”;新增一筆紀錄到“cmdev.emplog”表格中的敘述就是儲存在“cmdev.emp”表格的“UPDATE trigger”中。
如果你建立好需要的trigger元件后,MySQL數據庫就會自動幫你執行這些工作:
[](http://box.kancloud.cn/2015-07-18_55a9d78a1305d.png)
# 2 建立Triggers
下列是建立trigger元件的語法:
[](http://box.kancloud.cn/2015-07-18_55a9d78b37d7c.png)
如果trigger元件執行的工作比較復雜,需要一個以上的敘述時,就要把敘述放在“BEGIN”與“END”區塊中:
[](http://box.kancloud.cn/2015-07-18_55a9d78f97a28.png)
你可以依照需求為一個表格建立不同的trigger元件:
[](http://box.kancloud.cn/2015-07-18_55a9d7903c68e.png)
以上列討論的需求來說,每一次修改“cmdev.emp”表格資料,都必須新增一筆紀錄到“cmdev.emplog”表格中。你要為“cmdev.emp”表格建立一個“UPDATE TRIGGER”;而“BEFORE”與“AFTER”就是“之前”與“之后”的意思。如果建立“BEFORE UPDATE TRIGGER”,那就表示在修改資料前會執行trigger;如果建立“AFTER UPDATE TRIGGER”,那就表示在修改資料后會執行trigger。以這個需求來說,“BEFORE”或“AFTER”都是一樣的。
建立trigger元件與建立stored routines的方式一樣,你也要使用“SQL script”來執行建立trigger的工作。下列的范例建立一個名稱為“emp_before_update”的trigger元件:
[](http://box.kancloud.cn/2015-07-18_55a9d79334bf0.png)
執行上列的敘述后,MySQL數據庫會儲存你建立的trigger元件,可是它并不像stored routines可以用來呼叫與執行;MySQL數據庫會自動幫你執行這些儲存在trigger中的敘述。
為“cmdev.emp”表格建立一個“BEFORE UPDATE TRIGGER”以后,只要發生修改“cmdev.emp”表格資料的情況,MySQL數據庫會自動執行這個trigger中的敘述:
[](http://box.kancloud.cn/2015-07-18_55a9d79fe010d.png)
不論是“UPDATE”或是其它兩種Trigger元件,MySQL數據庫都是以“紀錄”來執行trigger。以下列的范例來說,一個會修改三筆紀錄的“UPDATE”敘述,MySQL數據庫會執行trigger三次:
[](http://box.kancloud.cn/2015-07-18_55a9d7b6966cf.png)
如果在執行修改“cmdev.emp”表格敘述以后,實際上并沒有修改任何紀錄資料,那MySQL數據庫也不會執行trigger:
[](http://box.kancloud.cn/2015-07-18_55a9d7c3eed40.png)
在你建立trigger元件時,要特別注意下列的限制:
* 同一個數據庫不可以有相同名稱的Trigger
* TEMPORARY表格與View不可以建立Trigger
* 不可以使用“SELECT”敘述
* 不可以使用“CALL”敘述
* 不可以使用與交易(transactions)相關的敘述,包含“START TRANSACTION”、“COMMIT”與“ROLLBACK”
# 3 刪除Triggers
你可以使用下列的語法刪除不再需要的trigger元件:
[](http://box.kancloud.cn/2015-07-18_55a9d7eb4238d.png)
如果想要修改trigger元件中的敘述,你要先刪除以后,再建立新的trigger元件。所以你可以在在建立trigger元件的敘述中,加入刪除trigger元件的敘述:
[](http://box.kancloud.cn/2015-07-18_55a9d7ec993f7.png)
# 4 OLD與NEW關鍵字
在triggers元件中,可以使用一般的SQL敘述完成需要執行的工作,也可以使用在stored routines中討論過的變量與流程控制,讓triggers元件可以處理比較復雜的需求。MySQL數據庫在triggers元件中額外提供“OLD”與“NEW”兩個關鍵字:
[](http://box.kancloud.cn/2015-07-18_55a9d7f246a50.png)
因為“OLD”與“NEW”兩個關鍵字的特性,所以它們可以使用的triggers種類會有一些限制:
| Trigger種類 | OLD | NEW |
| --- | --- | --- |
| INSERT | 不能使用 | 新增的字段資料 |
| UPDATE | 修改前的字段資料 | 修改后的字段資料 |
| DELETE | 刪除前的字段資料 | 不能使用 |
以“cmdev.emp”表格的“UPDATE TRIGGER”來說,下列是使用“OLD”與“NEW”關鍵字取得的字段值:
[](http://box.kancloud.cn/2015-07-18_55a9d7f4b87b5.png)
延續上列為更新“cmdev.emp”表格執行紀錄工作的trigger來說,如果想要讓紀錄的訊息更加詳細,包含修改前與修改后的部門編號:
[](http://box.kancloud.cn/2015-07-18_55a9d7f56a9f7.png)
要完成上列的需求,就必須使用“OLD”與“NEW”關鍵字取得的字段值:
[](http://box.kancloud.cn/2015-07-18_55a9d7f96755f.png)
為表格建立“UPDATE TRIGGER”以后,就表示執行這個表格的修改動作,都會執行這個trigger元件:
[](http://box.kancloud.cn/2015-07-18_55a9d7fe9fed7.png)
如果要將“emp_before_update”的需求,修改為“只有在修改員工的部門編號時,才需要新增修改紀錄”,你就可以使用在sotred routines討論過的“IF”指令來完成這個需求:
[](http://box.kancloud.cn/2015-07-18_55a9d8066d681.png)
在“INSERT TRIGGER”中使用“NEW”關鍵字時,要特別注意“AUTO_INCREMENT”字段型態:
[](http://box.kancloud.cn/2015-07-18_55a9d80c21f93.png)
如果有需要的話,你也可以使用“SET”敘述設定“NEW”關鍵字指定的字段值。以下列的情況來說:
[](http://box.kancloud.cn/2015-07-18_55a9d8194ce34.png)
要解決上列的問題,你可以要求在新增資料的時候,不要使用小寫的文字。不過使用下列的“BEFORE INSERT TRIGGER”來處理的話,會更方便一些:
[](http://box.kancloud.cn/2015-07-18_55a9d81af2f91.png)
建立好這個“BEFORE INSERT TRIGGER”以后,就算新增的員工資料包含小寫的名稱與職務,這個trigger元件都會在新增紀錄之前,把它們轉換為大寫:
[](http://box.kancloud.cn/2015-07-18_55a9d8281264b.png)
# 5 查詢Triggers的相關資訊
如果想要查詢triggers的相關資訊,可以查詢“information_schema.TRIGGERS”表格,下列是它的主要字段:
| 字段名稱 | 型態 | 說明 |
| --- | --- | --- |
| TRIGGER_SCHEMA | varchar(64) | 數據庫 |
| TRIGGER_NAME | varchar(64) | 名稱 |
| EVENT_MANIPULATION | varchar(6) | 啟動的事件,有INSERT、UPDATE與DELETE |
| EVENT_OBJECT_SCHEMA | varchar(64) | 作用的數據庫 |
| EVENT_OBJECT_TABLE | varchar(64) | 作用的表格 |
| ACTION_STATEMENT | longtext | 執行的工作 |
| ACTION_TIMING | varchar(6) | 啟動的時機,有BEFORE與AFTER |
如果你想要查詢建立某個stored routines的詳細資訊,可以使用下列的語法:
[](http://box.kancloud.cn/2015-07-18_55a9d83673948.png)