學習資源: Codedata - MySQL 超新手入門（16）Triggers

• Triggers 的應用
• 建立 Triggers
• 刪除 Triggers
• OLD 與 NEW 關鍵字
• 查詢 Triggers 的相關資訊

Triggers的應用

1. Triggers可以讓你先把一些在特定狀況要執行的敘述儲存起來，MySQL資料庫會在正確的時機自動幫你執行這些敘述

建立Triggers

1. 語法，當需要一個以上的敘述時

   CREATE TRIGGER 名稱 時機 事件
   ON 表格名稱 FOR EACH ROW 
   BEGIN
       敘述
   END;
   

2. 使用「SQL script」來執行建立trigger，範例為「emp_before_update」的trigger元件

   DELIMITER $$
   CREATE TRIGGER cmdev.emp_before_update BEFORE UPDATE
   ON cmdev.emp FOR EACH ROW
   BEGIN
     INSERT INTO emlog (message)
     VALUES ('BEFORE UPDATE!');
   END $$
   DELIMTER;
   

   1. 使用「DELIMITER」把delimiter從「;」設定為「$$」
3. 建立trigger元件時，要特別注意下列的限制：
   1. 同一個資料庫不可以有相同名稱的Trigger
   2. TEMPORARY表格與View不可以建立Trigger
   3. 不可以使用「SELECT」敘述與 「CALL」敘述
   4. 不可以使用與交易(transactions)相關的敘述，包含「START TRANSACTION」、「COMMIT」與「ROLLBACK」

刪除Triggers

1. 語法

   DROP TRIGGER IF EXISTS trigger名稱
   

   1. 想要修改trigger元件中的敘述，你要先刪除以後，再建立新的trigger元件
   2. 所以你可以在在建立trigger元件的敘述中，加入刪除trigger元件的敘述

   DELIMITER $
   DROP TRIGGER IF EXISTS cmdev.mytrigger$$
   CREATE TRIGGER cmdev.mytrigger BEFORE UPDATE
   ON cmdev.emp FOR EACH ROW
   BEGIN
     ...
   END $$
   DELIMITER ;
   

OLD 與 NEW 關鍵字

• OLD 與 NEW
  • 「OLD.欄位名稱」: 修改或刪除前的欄位值
  • 「NEW.欄位名稱」: 修改後或新增的欄位值
  • 範例:

    DELIMITER $$
    DROP TRIGGER IF EXISTS cmdev.emp_before_epdate$$
    
    CREATE TRIGGER cmdev.emp_before_update BEFORE UPDATE
    ON cmdev.emp FOR EACH ROW
    BEGIN
      DEVLARE v_message VARCHAR(64) DEFAULT 'BEFORE UPDATE: ';
      SET v_message = CONCAT( v_message,
                              OLD.deptno, ' -> ', NEW.deptno);
      INSERT INTO emplog (message) VALUES (v_message);
    END $$
    
    DELIMITER;
    

1. 使用「NEW」關鍵字時，要特別注意「AUTO_INCREMENT」欄位型態
2. 可以要求在新增資料的時候，不要使用小寫的文字，建立「BEFORE INSERT TRIGGER」以後，算新增的員工資料包含小寫的名稱與職務，這個trigger元件都會在新增紀錄之前，把它們轉換為大寫

   DELIMITER $$
   
   DROP TRIGGER IF EXISTS cmdev.emp_before_insert$$
   
   CREATE TRIGGER cmdev.emp_before_insert BEFORE INSERT
   ON cmdev.emp FOR EACH ROW
   BEGIN
     SET NEW.ename = UPPER(NEW.ename);
     SET NEW.job = UPPER(NEW.job);
   END $$
   DELIMITER;
   

查詢Triggers的相關資訊

1. 查詢「information_schema.TRIGGERS」表格
2. 語法，查詢建立某個stored routines的詳細資訊

   SHOW CREATE TRIGGER trigger名稱