使用觸發器

1. 觸發器

​ MySQL語句在需要時被執行，儲存過程也是如此。但是，如果你想要某條語句在事件發生時自動執行，怎麼辦？例如：​

• 每當增加一個顧客到某個資料庫時，都檢查其電話號碼格式是否正確，州的縮寫是否大寫；
• 每當訂購一個產品時，都從庫存數量中減去訂購的數量；
• 無論何時刪除一行，都在某個存檔表中保留一個副本。

​ 所有這些例子的共同之處是它們都需要在某個表發生更改時自動處理。這確切地說就是觸發器。觸發器是MySQL響應以下任意語句而自動執行的一條MySQL語句（或位於BEGIN和END語句之間的一組語句）：

• DELETE;
• INSERT;
• UPDATE。

其他MySQL語句不支援觸發器。

---

2. 建立觸發器

在建立觸發器時，需要給出4條資訊：

• 唯一的觸發器名；
• 觸發器關聯的表；
• 觸發器應該響應的活動（DELETE、INSERT或UPDATE）；
• 觸發器何時執行（處理之前或之後）

觸發器用CREATE TRIGGER語句建立。下面是一個簡單的例子：

輸入：

CREATE TRIGGER newproduct AFTER INSERT INTO ON products
FOR EACH ROW SELECT 'Product added';

分析：CREATE TRIGGER 用來建立名為newproduct的觸發器。觸發器可在一個操作發生之前或之後執行，這裡給出了AFTER INSERT，所以此觸發器將在INSERT語句成功執行後執行。這個觸發器還指定FOREACH ROW,因此程式碼對每個插入行執行。在這個例子中，文字Product added 將對每個插入的行顯示一次。

​ 為了測試這個觸發器，使用INSERT語句新增一行或多行到products中，你將看到對每個成功的插入，顯示Product added訊息。

僅支援表 只有表才支援觸發器，檢視不支援（臨時表也不支援）

​ 觸發器按每個表每個事件每次地定義，每個表每個事件每次只允許一個觸發器。因此，每個表最多支援6個觸發器（每條INSERT、UPDATE和DELETE的之前和之後）。單一觸發器不能與多個事件或多個表關聯，所以，如果你需要一個對INSERT和UPDATE操作執行的觸發器，則應該定義兩個觸發器。

觸發器失敗 如果BEFORE觸發器失敗，則MySQL將不執行請求的操作。此外，如果BEFORE觸發器或語句本身失敗，MySQL將不執行AFTER觸發器（如果有的話）。

---

3. 刪除觸發器

​ 現在，刪除觸發器的語法應該很明顯了。為了刪除一個觸發器，可使用DROP TRIGGER語句，如下所示：

輸入：DROP TRIGGER newproduct;

分析：觸發器不能更新或覆蓋。為了修改一個觸發器，必須先刪除它，然後再重新建立。

---

4. 使用觸發器

​ 在有了前面的基礎知識後，我們現在來看所支援的每種觸發器型別以及它們的差別。

4.1 INSERT觸發器

​ INSERT觸發器在INSERT語句執行之前或之後執行。需要知道以下幾點：

• 在INSERT觸發器程式碼內，可引用一個名為NEW的虛擬表，訪問被插入的行；
• 在BEFORE INSERT觸發器中，NEW中的值也可以被更新（允許更改被插入的值）；
• 對於AUTO_INCREMENT列，NEW在INSERT執行之前包含0，在INSERT執行之後包含新的自動生成值。

​ 下面舉一個例子。AUTO_INCREMENT列具有MySQL自動賦予的值。第21章建議了幾種確定新生成值的方法，但下面是一種更好的方法：

輸入：

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

分析：此程式碼建立一個名為neworder的觸發器，它按照AFTER INSERT ON orders執行。在插入一個新訂單到orders表時，MySQL生成一個新訂單號並儲存到order_num中。觸發器從NEW. order_num取得這個值並返回它。此觸發器必須按照AFTER INSERT執行，因為在BEFORE INSERT語句執行之前，新order_num還沒有生成。對於orders的每次插入使用這個觸發器將總是返回新的訂單號。

​ 為了測試這個觸發器，試著插入一下新行，如下所示：

輸入：

INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001);

輸出：

order_num
20010

分析： orders包含3個列。order_date和cust_id必須給出，order_num由MySQL自動生成，而現在order_num還自動被返回。

BEFORE或AFTER? 通常，將BEFORE用於資料驗證和淨化（目的是保證插入表中的資料確實是需要的資料）。本提示也適用於UPDATE觸發器。

4.2 DELETE觸發器

​ DELETE觸發器在DELETE語句執行之前或之後執行。需要知道以下兩點：

• 在DELETE觸發器程式碼內，你可以引用一個名為OLD的虛擬表，訪問被刪除的行；
• OLD中的值全都是隻讀的，不能更新。

​ 下面的例子演示使用OLD儲存將要被刪除的行到一個存檔表中：

輸入：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num,order_date,cust_id)
    VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

分析： 在任意訂單被刪除前將執行此觸發器。它使用一條INSERT語句將OLD中的值（要被刪除的訂單）儲存到一個名為archive_orders的存檔表中（為了實際使用這個例子，你需要用與orders相同的列建立一個名為archive_orders的表）。

​ 使用BEFORE DELETE觸發器的有點（相對於AFTER DELETE觸發器來說）為，如果由於某種原因，訂單不能存檔，DELETE本身將被放棄。

4.3 UPDATE觸發器

​ UPDATE觸發器在UPDATE語句執行之前或之後執行。需要知道以下幾點：

• 在UPDATE觸發器程式碼中，你可以引用一個名為OLD的虛擬表訪問以前（UPDATE語句前）的值，引用一個名為NEW的虛擬表訪問新更新的值；
• 在BEFORE UPDATE觸發器中，NEW中的值也可能被更新（允許更改將要用於UPDATE語句中的值）；
• OLD中的值全部都是隻讀的，不能更新。

​ 下面的例子保證州名總是大寫（不管UPDATE語句中給出的是大寫還是小寫）；

輸入：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

分析：顯然任何資料淨化都需要在UPDATE語句之前進行，就想這個例子中一樣。每次更新一個行時，NEW.vend_state中的值（將用來更新錶行的值）都用Upper（NEW.vend_state）替換。