25. 使用MySQL之使用觸發器

1. 觸發器

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

• 每當增加一個顧客到某個資料庫表時，都檢查其電話號碼格式是
  否正確，州的縮寫是否為大寫；

• 每當訂購一個產品時，都從庫存數量中減去訂購的數量；

• 無論何時刪除一行，都在某個存檔表中保留一個副本。

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

• DELETE；

• INSERT

• UPDATE

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

2. 建立觸發器

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

• 唯一的觸發器名；

• 觸發器關聯的表；

• 觸發器應該響應的活動（DELETE、INSERT或UPDATE）；

• 觸發器何時執行（處理之前或之後）。

補充：

• 保持每個資料庫的觸發器名唯一：

  在MySQL 5中，觸發器名必須在每個表中唯一，但不是在每個資料庫中唯一。這表示同一資料庫中的兩個表可具有相同名字的觸發器。這在其他每個資料庫觸發器名必須唯一的DBMS中是不允許的，而且以後的MySQL版本很可能會使命名規則更為嚴格。因此，現在最好是在資料庫範圍內使用唯一的觸發器名。

  可以這樣理解上面這段話：

  1. 在MySQL 5中，觸發器名在表級範圍內是唯一的：也就是說，在同一個表中，觸發器的名稱不能重複，但不同表之間可以有相同名稱的觸發器。例如，在資料庫 db1 中，你可以在表 table1 和 table2 中分別建立名為 trigger1 的觸發器，系統允許這種情況，因為它們作用於不同的表。

  2. 其他DBMS要求觸發器在資料庫級範圍內唯一：在許多其他資料庫系統中（例如Oracle、SQL Server等），要求整個資料庫範圍內觸發器名必須唯一，不能在不同表上建立相同名稱的觸發器。也就是說，一旦在資料庫的某個表上建立了名為 trigger1 的觸發器，那麼在同一個資料庫的其他表上不能再建立同名觸發器。

觸發器用CREATE TRIGGER語句建立。

比如：

create trigger newproduct after insert on products
for each row select 'Product added';

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

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

插一句題外話

其實如果按書上的這個語句寫的話，是會報錯的：Error Code: 1415. Not allowed to return a result set from a trigger

改成這樣就不報錯了：

create trigger newproduct after insert on products
for each row select 'Product added' into @test;

原因是從MySQL5以後不支援觸發器返回結果集，需要用一個變數接受結果集（此處即@test變數）。

向products表插入兩條資料後，再使用select @test;檢視變數@test

輸出如下：

但是其實上面新增一個變數的方法感覺治標不治本，還有別的更好的方法：

可以使用 INSERT 記錄日誌資訊：

-- 建立日誌表
CREATE TABLE product_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 建立觸發器，將日誌資訊插入到日誌表
DELIMITER //
CREATE TRIGGER newproduct AFTER INSERT ON products 
FOR EACH ROW 
BEGIN
    INSERT INTO product_log (message) VALUES ('Product added');
END //
DELIMITER ;

這樣，每當在 products 表中插入一條新記錄，觸發器就會自動將“Product added”訊息插入到 product_log 表中。

迴歸正題

注意：

• 僅支援表：

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

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

補充：

• 觸發器失敗：

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

  插句題外話

  這段話的意思是：在MySQL中，如果 BEFORE 觸發器執行失敗，那麼MySQL會中止該操作，即不再執行實際的資料庫操作（例如 INSERT、UPDATE 或 DELETE），而且 AFTER 觸發器（如果存在）也不會被觸發。這是MySQL的一個重要機制，用於確保資料的完整性和一致性。

  具體解釋如下：

  1. BEFORE 觸發器失敗時：

  BEFORE 觸發器是在指定操作（如 INSERT、UPDATE 或 DELETE）之前執行的。如果 BEFORE 觸發器遇到錯誤或執行失敗，MySQL會取消該操作，因此不會執行該操作本身。例如，如果一個 INSERT 操作的 BEFORE INSERT 觸發器失敗，那麼這條 INSERT 語句就不會被執行。同時，任何相關的AFTER 觸發器也不會被執行，因為 AFTER 觸發器只有在請求的操作成功執行後才會被觸發。

  2. 語句本身失敗時：

  即使 BEFORE 觸發器成功執行，但如果實際的資料庫操作（例如 INSERT）本身因某種原因失敗（例如違反了唯一性約束），那麼 AFTER 觸發器也不會執行。因為 AFTER 觸發器是基於操作成功執行後的情況來觸發的。

  3. 舉個例子

  假設有一張 products 表，併為 INSERT 操作定義了 BEFORE INSERT 和 AFTER INSERT 觸發器。

  DELIMITER //
  
  -- BEFORE INSERT 觸發器
  CREATE TRIGGER before_insert_product BEFORE INSERT ON products
  FOR EACH ROW
  BEGIN
      -- 如果某些條件不滿足，觸發器可以引發錯誤
      IF NEW.price <= 0 THEN
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be greater than zero';
      END IF;
  END//
  
  -- AFTER INSERT 觸發器
  CREATE TRIGGER after_insert_product AFTER INSERT ON products
  FOR EACH ROW
  BEGIN
      -- 記錄日誌或執行其他操作
      INSERT INTO product_log (message) VALUES ('Product added');
  END//
  
  DELIMITER ;
  

  在這個例子中：

  • 如果在 BEFORE INSERT 觸發器中發現 price 小於等於 0，那麼觸發器會引發錯誤並停止執行，這樣 INSERT 操作就不會完成，也不會觸發AFTER INSERT 觸發器。

  • 只有當 BEFORE INSERT 成功且 INSERT 語句本身沒有出錯時，AFTER INSERT 觸發器才會執行。

  透過這種機制，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;

插句題外話

如果按上面那樣寫的話，會報大大的錯

可以嘗試寫成這樣：

create trigger neworder after insert on orders
for each row select new.order_num into @neworder_num;

為什麼會報錯呢？ 為什麼寫成這樣不報錯呢？

• 在 MySQL5 後，觸發器（trigger）不允許返回結果集（即執行 SELECT 語句直接輸出資料）。

• 在 MySQL 中，SELECT ... INTO @變數名 和 SELECT ... 的行為有所不同：

  • SELECT ... INTO @變數名：這種語法將查詢結果儲存到一個使用者定義的變數中，不會返回結果集。這在觸發器或儲存過程內部是允許的，因為它不會將資料返回到客戶端，只是在內部操作。

  • SELECT ...：直接執行 SELECT 查詢會返回一個結果集給客戶端，觸發器內部不允許這樣做，因此會報錯 1415。

  透過使用 SELECT ... INTO @變數名，可以將值儲存在變數中進行後續處理，而不觸發返回結果集的限制。這也是為什麼加上 INTO 後，程式碼在觸發器中可以正常執行。

• 想了解一下@變數名的話可以看下這篇部落格 MySQL中的@變數名

迴歸正題

此程式碼建立一個名為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);

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 TABLE archive_orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
);

delimiter //
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 //
delimiter ;

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

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

補充：

• 多語句觸發器：

  正如所見，觸發器deleteorder使用BEGIN和END語句標記觸發器體。這在此例子中並不是必需的，不過也沒有害處。使用BEGIN END塊的好處是觸發器能容納多條SQL語句（在BEGIN END塊中一條挨著一條）。

插句題外話

給出我的實踐過程圖：

未對orders表執行刪除訂單操作時，orders表的樣子：

對orders表執行刪除訂單操作後，檢視archive_ orders表和orders表：

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)替換。

4.4 關於觸發器的進一步介紹

我們再介紹一些使用觸發器時需要記住的重點。

• 與其他DBMS相比，MySQL 5中支援的觸發器相當初級。未來的MySQL版本中有一些改進和增強觸發器支援的計劃。

• 建立觸發器可能需要特殊的安全訪問許可權，但是，觸發器的執行是自動的。如果INSERT、UPDATE或DELETE語句能夠執行，則相關的觸發器也能執行。

• 應該用觸發器來保證資料的一致性（大小寫、格式等）。在觸發器中執行這種型別的處理的優點是它總是進行這種處理，而且是透明地進行，與客戶機應用無關。

• 觸發器的一種非常有意義的使用是建立審計跟蹤。使用觸發器，把更改（如果需要，甚至還有之前和之後的狀態）記錄到另一個表非常容易。

• 遺憾的是，MySQL觸發器中不支援CALL語句。這表示不能從觸發器內呼叫儲存過程。所需的儲存過程程式碼需要複製到觸發器內。