專欄作者新書出版：Android App程式開發剖析 第三版（適用Android 8 Oreo與Android Studio 3）

MySQL 超新手入門（15）Stored Routines 進階 << 前情

1 Triggers的應用

在「cmdev」資料中有一個「emplog」表格，如果有人執行任何修改「cmdev.emp」表格資料的動作，都要新增一筆訊息到「cmdev.emplog」表格中，查詢這個表格的資料，就可以知道在什麼時候曾經修改過「cmdev.emp」表格中的資料：

要完成這樣的需求，每一次修改「cmdev.emp」表格資料時，你都必需執行下列的工作：

要完成這樣的需求，你可以使用stored routines來處理修改與新增紀錄的工作，或是在應用程式中撰寫程式來解決。不過都會是一件很麻煩的事情，而且比較容易造成遺漏紀錄的情況。

MySQL資料庫提供一種特別的資料庫元件，稱為「triggers」，一般會把它稱為「觸發器」。Triggers可以讓你先把一些在特定狀況要執行的敘述儲存起來，MySQL資料庫會在正確的時機自動幫你執行這些敘述：

以上列討論的需求來說，每一次修改「cmdev.emp」表格資料，都必須新增一筆紀錄到「cmdev.emplog」表格中。這個需求的主角是「cmdev.emp」表格，所以你可以為這個表格建立一個trigger元件；因為是在修改資料的情況時才需要執行特定的工作，所以你要選擇「UPDATE trigger」；新增一筆紀錄到「cmdev.emplog」表格中的敘述就是儲存在「cmdev.emp」表格的「UPDATE trigger」中。

如果你建立好需要的trigger元件後，MySQL資料庫就會自動幫你執行這些工作：

2 建立Triggers

下列是建立trigger元件的語法：

如果trigger元件執行的工作比較複雜，需要一個以上的敘述時，就要把敘述放在「BEGIN」與「END」區塊中：

你可以依照需求為一個表格建立不同的trigger元件：

以上列討論的需求來說，每一次修改「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元件：

執行上列的敘述後，MySQL資料庫會儲存你建立的trigger元件，可是它並不像stored routines可以用來呼叫與執行；MySQL資料庫會自動幫你執行這些儲存在trigger中的敘述。

為「cmdev.emp」表格建立一個「BEFORE UPDATE TRIGGER」以後，只要發生修改「cmdev.emp」表格資料的情況，MySQL資料庫會自動執行這個trigger中的敘述：

不論是「UPDATE」或是其它兩種Trigger元件，MySQL資料庫都是以「紀錄」來執行trigger。以下列的範例來說，一個會修改三筆紀錄的「UPDATE」敘述，MySQL資料庫會執行trigger三次：

如果在執行修改「cmdev.emp」表格敘述以後，實際上並沒有修改任何紀錄資料，那MySQL資料庫也不會執行trigger：

在你建立trigger元件時，要特別注意下列的限制：

• 同一個資料庫不可以有相同名稱的Trigger
• TEMPORARY表格與View不可以建立Trigger
• 不可以使用「SELECT」敘述
• 不可以使用「CALL」敘述
• 不可以使用與交易(transactions)相關的敘述，包含「START TRANSACTION」、「COMMIT」與「ROLLBACK」

3 刪除Triggers

你可以使用下列的語法刪除不再需要的trigger元件：

如果想要修改trigger元件中的敘述，你要先刪除以後，再建立新的trigger元件。所以你可以在在建立trigger元件的敘述中，加入刪除trigger元件的敘述：

4 OLD與NEW關鍵字

在triggers元件中，可以使用一般的SQL敘述完成需要執行的工作，也可以使用在stored routines中討論過的變數與流程控制，讓triggers元件可以處理比較複雜的需求。MySQL資料庫在triggers元件中額外提供「OLD」與「NEW」兩個關鍵字：

因為「OLD」與「NEW」兩個關鍵字的特性，所以它們可以使用的triggers種類會有一些限制：

以「cmdev.emp」表格的「UPDATE TRIGGER」來說，下列是使用「OLD」與「NEW」關鍵字取得的欄位值：

延續上列為更新「cmdev.emp」表格執行紀錄工作的trigger來說，如果想要讓紀錄的訊息更加詳細，包含修改前與修改後的部門編號：

要完成上列的需求，就必須使用「OLD」與「NEW」關鍵字取得的欄位值：

為表格建立「UPDATE TRIGGER」以後，就表示執行這個表格的修改動作，都會執行這個trigger元件：

如果要將「emp_before_update」的需求，修改為「只有在修改員工的部門編號時，才需要新增修改紀錄」，你就可以使用在sotred routines討論過的「IF」指令來完成這個需求：

在「INSERT TRIGGER」中使用「NEW」關鍵字時，要特別注意「AUTO_INCREMENT」欄位型態：

如果有需要的話，你也可以使用「SET」敘述設定「NEW」關鍵字指定的欄位值。以下列的情況來說：

要解決上列的問題，你可以要求在新增資料的時候，不要使用小寫的文字。不過使用下列的「BEFORE INSERT TRIGGER」來處理的話，會更方便一些：

建立好這個「BEFORE INSERT TRIGGER」以後，就算新增的員工資料包含小寫的名稱與職務，這個trigger元件都會在新增紀錄之前，把它們轉換為大寫：

5 查詢Triggers的相關資訊

如果想要查詢triggers的相關資訊，可以查詢「information_schema.TRIGGERS」表格，下列是它的主要欄位：

如果你想要查詢建立某個stored routines的詳細資訊，可以使用下列的語法：

後續 >> MySQL 超新手入門（17）查詢 information_schema