MySQL 儲存程式之觸發器（Trigger）／事件（Event）

MySQL 資料複製基礎介紹 << 前情

上次講到 資料庫的儲存程序（Stored Routine）的運用，這次要為各位介紹的是觸發器（Trigger）。在介紹 Trigger 的語法前，先和讀者聊聊觸發器的特性。

MySQL 正式支援觸發器是在 MySQL 5.0.2 的版本之後。觸發器是註冊在資料庫表格上的程式。所以，在事件發生（對資料列做新增/修改/刪除）時，資料庫會依照觸發條件（事件前/事件後）幫你執行預先儲存好的程式。因此，常常有人利用觸發器來驗證資料的正確性，或做資料計算。

也因為觸發器的功能十分強大，所以偶而就會發現有些人喜歡在將商業邏輯加到觸發器中，甚至加了大量的觸發器在單一的資料表中。但是觸發器是非常昂貴的解決方案，就像是金庸筆下的七傷拳（先傷己，再傷敵；傷敵七分，自損三分），它會耗用大量的系統資源（尤其當在資料量非常龐大的系統中），過度的使用，反而造成資料庫的效能下降，不可不防。

另外一個重點是，在支援交易（transaction）的儲存引擎中，一但表格有設定觸發器，則整個交易行為（包含觸發器的新增/修改/刪除等全部的操作），也是不可分割的（atomic）。

接下來，我們來介紹出發器的時機，事件與物件的種類與關係。

觸發器的時機：

事件前：
　before
事件後：
　after

觸發器事件：

新增：
　insert
　load data
　replace
修改：
　update
刪除：
　update
　replace
　不包括（drop table, truncate table）

參考物件：

NEW：表示”新”的資料物件。在下列的操作時會出現。
　insert
　update
OLD：表示”舊”的資料物件。在下列的操作時會出現。
　update
　delete

如果你想取得線上手冊，同樣的，你可以使用”help create trigger”來取得創建觸發器的語法。

創建觸發器語法如下：

mysql> help create trigger
Name: 'CREATE TRIGGER'
Description:
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body

在創建觸發器時，有下列的步驟需預先知道或特別注意的：

• 首先，創建觸發器時，使用者必須要有SUPER的權限。
• 和先前創建Stored Procedure一樣，在創建觸發器時，可以指定DEFINER。而在執行觸發器的時候，會先檢查DEFINER的設定。而該使用者必須要有三個權限：
• SUPER的權限。
• 如果使用OLD.[欄位]或NEW.[欄位]，則必須要有SELECT資料表的權限。
• 如果使用SET NEW.[攔位] = “值”，則必須要有UPDATE資料表的權限。
• 指定DEFINER後，要再指定trigger的時間點是註冊的事件。同時指定”for each row”，讓每筆資料修改都觸發MySQL的操作。
• 最後，在begin和end的語法中，指定觸發器要操作的行為。

再來，我們來看看幾個創建觸發器的範例：

範例一：新增後的觸發器。

(after insert)

mysql> delimiter //
mysql> create trigger ai_t1
-> after insert
-> on t1
-> for each row
-> begin
-> insert into i1 values (new.id, new.name);
-> end//
mysql> delimiter ;

這個觸發器就是在新增動作結束後，便將新增資料的id, name同步儲存在i1的資料表中。

範例二：更新後的觸發器。

(after update)

mysql> delimiter //
mysql> create trigger au_t1
-> after update
-> on t1
-> for each row
-> begin
-> insert into u1 values(old.id, old.name, new.id, new.name)//
-> end//
mysql> delimiter ;

這個觸發器就是在更新動作結束後，便將新/舊的id, name資料，同步儲存在u1的資料表中。

範例三：刪除後的觸發器。

(after delete)

mysql> delimiter //
mysql> drop trigger if exists ad_p//
mysql> create trigger ad_p
    -> after delete
    -> on p
    -> for each row
    -> begin
    -> insert into old_p values (old.id, old.no);
    -> delete from c where c.id=old.id;
    -> end//
mysql> delimiter ;

這個觸發器就是在刪除動作結束後，便將舊的id, no資料，同步儲存在old_p的資料表中，同時，將資料表c中有關於被刪除id的相關資料也一併刪除(這樣的行為雷同資料表的外來鍵delete cascade，但還多了一個logging的機制)。

看完範例後，你也可以開始在你的MySQL資料庫中，創建一個觸發器來玩玩囉。

再來我們要介紹MySQL的事件。

什麼是”事件”？”事件”是資料庫系統根據時間排程而執行的工作。
由於它是由”時間”來觸發執行，所以，我們可以將”事件”視為是一種”暫時”的觸發器。
事件是由一個特定的線程來管理，而擁有PROCESS權限的使用者則可以用”SHOW PROCESSLIST”指令來看目前執行的工作。

首先，我們可以先來檢查MySQL的事件排程是否已經啟動。

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF |
+-------------------+
1 row in set (0.00 sec)

如果沒有啟動，則你可以利用下面的指令將它開啟。

mysql> set global event_scheduler = ON;
mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 8 | event_scheduler | localhost | NULL | Daemon | 240 | Waiting on empty queue | NULL |
| 9 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

再來我們要看看創建事件的語法。

類似的指令，我們可以由”help create event”來取得創建事件的語法：

創建事件的語法如下：

mysql> help create event
Name: 'CREATE EVENT'
Description:
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;

schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

在使用創建事件時，我們需要注意的地方如下：

1. 創建者（DEFINER）：如之前預存函數/觸發器的設定一樣，使用者可以設定DEFINER。
2. 時間觸發的次數：可分為一次性或多次觸發。如果是一次性的觸發，可使用”AT”；如果是多次的觸發，則使用”EVERY”。
3. 觸發的起始時間設定：可用”STARTS”來設定起始時間；”ENDS”來設定結束時間。
4. 時間間隔（interval）：時間間隔的單位，可參考線上手冊。
5. 保存事件：預設值是”ON COMPLETION NOT PRESERVE”。也就是在事件完成後(如果是多次觸發事件，則是超過事件結束時間”ENDS”)，該事件就會自動被刪除。如果，你想保留這個事件，則必須指定”ON COMPLETION PRESERVE”。

再來，我們來看個創建事件的範例：

mysql> set global event_scheduler=on;
mysql> grant event on stanley.* to 'bob'@'localhost';
mysql> create
-> definer='stanley'@'localhost'
-> event if not exists event_name
-> on schedule every 10 second
-> on completion preserve
-> enable
-> comment 'Trigger this event in every 10 seconds.'
-> do
-> select now() into outfile '/tmp/now.txt';

這個事件就是每十秒觸發一次的事件，其動作是將系統時間儲存在外部的檔案上(/tmp/now.txt)，而且這是一個沒有設定結束時間的事件。

結論：

觸發器與事件是一個好用的工具，資料庫管理師可以將商業邏輯包裝在資料庫中，則可以減少應用程式的工作，並可確保跨應用程式中，仍可確保相同的”資料”邏輯，常見的用途是對資料庫的稽核。但如開宗明義所說的，觸發器是把雙面刃，濫用的情況下，反而會造成資源的耗損，不可不查也～