MySQL觸發器分析

觸發器是一種特殊的存儲過程，它在插入，删除或修改特定表中的資料時觸發執行，它比資料庫本身标準的功能有更精細和更複雜的資料控制能力。

資料庫觸發器有以下的作用：

1.安全性。可以基于資料庫的值使使用者具有操作資料庫的某種權利。

  # 可以基于時間限制使用者的操作，例如不允許下班後和節假日修改資料庫資料。

  # 可以基于資料庫中的資料限制使用者的操作，例如不允許股票的價格的升幅一次超過10%。

2.審計。可以跟蹤使用者對資料庫的操作。   

  # 審計使用者操作資料庫的語句。

  # 把使用者對資料庫的更新寫入審計表。

3.實作複雜的資料完整性規則

  # 實作非标準的資料完整性檢查和限制。觸發器可産生比規則更為複雜的限制。與規則不同，觸發器可以引用列或資料庫對象。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。

  # 提供可變的預設值。

4.實作複雜的非标準的資料庫相關完整性規則。觸發器可以對資料庫中相關的表進行連環更新。例如，在auths表author_code列上的删除觸發器可導緻相應删除在其它表中的與之比對的行。

  # 在修改或删除時級聯修改或删除其它表中的與之比對的行。

  # 在修改或删除時把其它表中的與之比對的行設成NULL值。

  # 在修改或删除時把其它表中的與之比對的行級聯設成預設值。

  # 觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行資料更新的事務。當插入一個與其主健不比對的外部鍵時，這種觸發器會起作用。例如，可以在books.author_code 列上生成一個插入觸發器，如果新值與auths.author_code列中的某值不比對時，插入被回退。

5.同步實時地複制表中的資料。

6.自動計算資料值，如果資料的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳号上的資金低于5萬元則立即給财務人員發送警告資料。

MySQL觸發器文法詳解.

㈠CREATE TRIGGER文法

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

觸發程式是與表有關的命名資料庫對象，當表上出現特定事件時，将激活該對象。

觸發程式與命名為tbl_name的表相關。tbl_name必須引用永久性表。不能将觸發程式與

TEMPORARY表或視圖關聯起來。

trigger_time是觸發程式的動作時間。它可以是BEFORE或AFTER，以指明觸發程式是在激活它的語句之前或之後觸發。

trigger_event指明了激活觸發程式的語句的類型。trigger_event可以是下述值之一：

·         INSERT：将新行插入表時激活觸發程式，例如，通過INSERT、LOAD DATA和REPLACE

語句。

·         UPDATE：更改某一行時激活觸發程式，例如，通過UPDATE語句。

·         DELETE：從表中删除某一行時激活觸發程式，例如，通過DELETE和REPLACE語句。

請注意，trigger_event與以表操作方式激活觸發程式的SQL語句并不很類似，這點很重要。例如，

關于INSERT的BEFORE觸發程式不僅能被INSERT語句激活，也能被LOAD DATA語句激活。

可能會造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...文法：BEFORE INSERT

觸發程式對于每一行将激活，後跟AFTER INSERT觸發程式，或BEFORE UPDATE和AFTER 

UPDATE觸發程式，具體情況取決于行上是否有重複鍵。

對于具有相同觸發程式動作時間和事件的給定表，不能有兩個觸發程式。例如，對于某一表，不

能有兩個BEFORE UPDATE觸發程式。但可以有1個BEFORE UPDATE觸發程式和1個BEFORE 

INSERT觸發程式，或1個BEFORE UPDATE觸發程式和1個AFTER UPDATE觸發程式。

trigger_stmt是當觸發程式激活時執行的語句。如果你打算執行多個語句，可使用BEGIN ... END複

合語句結構。這樣，就能使用存儲子程式中允許的相同語句

㈡DROP TRIGGER文法

DROP TRIGGER [schema_name.]trigger_name

舍棄觸發程式。方案名稱（schema_name）是可選的。如果省略了schema（方案），将從目前方

案中舍棄觸發程式。

注釋：從MySQL 5.0.10之前的MySQL版本更新到5.0.10或更高版本時（包括所有的MySQL 5.1版

本），必須在更新之前舍棄所有的觸發程式，并在随後重新建立它們，否則，在更新之後DROP 

TRIGGER不工作。

DROP TRIGGER語句需要SUPER權限。

㈢使用觸發程式

在本節中，介紹了在MySQL 5.1中使用觸發程式的方法，并介紹了在使用觸發程式方面的限制。

觸發程式是與表有關的命名資料庫對象，當表上出現特定事件時，将激活該對象。在某些觸發程

序的用法中，可用于檢查插入到表中的值，或對更新涉及的值進行計算。

觸發程式與表相關，當對表執行INSERT、DELETE或UPDATE語句時，将激活觸發程式。可以将

觸發程式設定為在執行語句之前或之後激活。例如，可以在從表中删除每一行之前，或在更新了

每一行後激活觸發程式。

要想建立觸發程式或舍棄觸發程式，可使用CREATE TRIGGER或DROP TRIGGER語句

·         觸發程式不能調用将資料傳回用戶端的存儲程式，也不能使用采用CALL語句的動态SQL

（允許存儲程式通過參數将資料傳回觸發程式）。

·         觸發程式不能使用以顯式或隐式方式開始或結束事務的語句，如START TRANSACTION、

COMMIT或ROLLBACK。

使用OLD和NEW關鍵字，能夠通路受觸發程式影響的行中的列（OLD和NEW不區分大小寫）。

在INSERT觸發程式中，僅能使用NEW.col_name，沒有舊行。在DELETE觸發程式中，僅能使用

OLD.col_name，沒有新行。在UPDATE觸發程式中，可以使用OLD.col_name來引用更新前的某一

行的列，也能使用NEW.col_name來引用更新後的行中的列。

用OLD命名的列是隻讀的。你可以引用它，但不能更改它。對于用NEW命名的列，如果具有

SELECT權限，可引用它。在BEFORE觸發程式中，如果你具有UPDATE權限，可使用“SET NEW.

col_name = value”更改它的值。這意味着，你可以使用觸發程式來更改将要插入到新行中的值，

或用于更新行的值。

在BEFORE觸發程式中，AUTO_INCREMENT列的NEW值為0，不是實際插入新記錄時将自動生

成的序列号。

通過使用BEGIN ... END結構，能夠定義執行多條語句的觸發程式。在BEGIN塊中，還能使用存儲

子程式中允許的其他文法，如條件和循環等。但是，正如存儲子程式那樣，定義執行多條語句的

觸發程式時，如果使用mysql程式來輸入觸發程式，需要重新定義語句分隔符，以便能夠在觸發

程式定義中使用字元“;”。在下面的示例中，示範了這些要點。在該示例中，定義了1個

UPDATE觸發程式，用于檢查更新每一行時将使用的新值，并更改值，使之位于0～100的範圍

内。它必須是BEFORE觸發程式，這是因為，需要在将值用于更新行之前對其進行檢查：

mysql> delimiter //

mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account

    -> FOR EACH ROW

    -> BEGIN

    ->     IF NEW.amount < 0 THEN

    ->         SET NEW.amount = 0;

    ->     ELSEIF NEW.amount > 100 THEN

    ->         SET NEW.amount = 100;

    ->     END IF;

    -> END;//

mysql> delimiter ;

較為簡單的方法是，單獨定義存儲程式，然後使用簡單的CALL語句從觸發程式調用存儲程式。

如果你打算從數個觸發程式内部調用相同的子程式，該方法也很有幫助。

在觸發程式的執行過程中，MySQL處理錯誤的方式如下：

·         如果BEFORE觸發程式失敗，不執行相應行上的操作。

·         僅當BEFORE觸發程式（如果有的話）和行操作均已成功執行，才執行AFTER觸發程式。

·         如果在BEFORE或AFTER觸發程式的執行過程中出現錯誤，将導緻調用觸發程式的整個語

句的失敗。

·         對于事務性表，如果觸發程式失敗（以及由此導緻的整個語句的失敗），該語句所執行的

所有更改将復原。對于非事務性表，不能執行這類復原，因而，即使語句失敗，失敗之前所作的

任何更改依然有效。

例一：

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account

    -> FOR EACH ROW SET @sum = @sum + NEW.amount;