MySQL觸發器的用途和用法詳解介紹

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

1. 安全性。可以基於資料庫的值使使用者具有操作資料庫的某種權利；
可以基於時間限制使用者的操作，例如不允許下班後和節假日修改資料庫資料；
可以基於資料庫中的資料限制使用者的操作，例如不允許股票的價格的升幅一次超過10%。
2. 審計。可以跟蹤使用者對資料庫的操作；
審計使用者操作資料庫的語句；
把使用者對資料庫的更新寫入審計表。
3. 實現複雜的資料完整性規則；
實現非標準的資料完整性檢查和約束。觸發器可產生比規則更為複雜的限制。與規則不同，觸發器可以引用列或資料庫物件。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨；
提供可變的預設值。
4. 實現複雜的非標準的資料庫相關完整性規則。觸發器可以對資料庫中相關的表進行連環更新。例如，在auths表author_code列上的刪除觸發器可導致相應刪除在其它表中的與之匹配的行。
在修改或刪除時級聯修改或刪除其它表中的與之匹配的行；
在修改或刪除時把其它表中的與之匹配的行設成NULL值；
在修改或刪除時把其它表中的與之匹配的行級聯設成預設值；
觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行資料更新的事務。當插入一個與其主健不匹配的外部鍵時，這種觸發器會起作用。例如，可以在books.author_code 列上產生一個插入觸發器，如果新值與auths.author_code列中的某值不匹配時，插入被回退。
5. 同步即時地複製表中的資料。
6. 自動計算資料值，如果資料的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低於5萬元則立即給財務人員發送警告資料。
觸發器文法詳解：

1. 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,update和delete三者之一，分別代表資料庫的增改刪操作；值得注意的是，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複合陳述式結構。這樣，就能使用儲存子程式中允許的相同語句。

2. 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許可權。

3. 使用觸發器

觸發器是與表有關的命名資料庫物件，當表上出現特定事件時，將啟用該對象。在某些觸發器的用法中，可用於檢查插入到表中的值，或對更新涉及的值進行計算。
觸發器與表相關，當對錶執行INSERT、DELETE或UPDATE語句時，將啟用觸發器。可以將觸發器設定為在執行語句之前或之後啟用。例如，可以在從表中刪除每一行之前，或在更新了每一行後啟用觸發器。

例如：

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
SET @sum = 0;
INSERT INTO account values(5,12.5);
SELECT @num;

使用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觸發程式，這是因為，需要在將值用於更新行之前對其進行檢查：
 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;

較為簡單的方法是，單獨定義預存程序，然後使用簡單的CALL語句從觸發器調用預存程序。如果你打算從數個觸發器內部調用相同的子程式，該方法也很有協助。
在觸發器的執行過程中，MySQL處理錯誤的方式如下：

如果BEFORE觸發器失敗，不執行相應行上的操作；
僅當BEFORE觸發器（如果有的話）和行操作均已成功執行，才執行AFTER觸發器；
如果在BEFORE或AFTER觸發器的執行過程中出現錯誤，將導致調用觸發器的整個語句的失敗；
對於事務性表，如果觸發器失敗（以及由此導致的整個語句的失敗），該語句所執行的所有更改將復原；對於非事務性表，不能執行這類復原，因而，即使語句失敗，失敗之前所作的任何更改依然有效。

下面是使用觸發器的一些限制：
觸發器不能調用將資料返回用戶端的預存程序，也不能使用採用CALL語句的動態SQL（允許預存程序通過參數將資料返回觸發器）。
觸發器不能使用以顯式或隱式方式開始或結束事務的語句，如START TRANSACTION、COMMIT或ROLLBACK。

建立觸發器

文法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt
觸發程式是與表有關的命名資料庫物件，當表上出現特定事件時，將啟用該對象。

觸發程式與命名為tbl_name的表相關。tbl_name必須引用永久性表。不能將觸發程式與暫存資料表表或視圖關聯起來。

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複合陳述式結構。這樣，就能使用儲存子程式中允許的相同語句。

 

建立一個單執行語句的觸發器

CREATE TABLE account(acct_num INT ,amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @SUM=@SUM+new.amount;
首先建立一個account表，表中有兩個欄位，分別為：acct_num欄位（定義為int類型）

amount欄位（定義成浮點類型）；其次建立一個名為ins_sum的觸發器，觸發的條件是向資料表account插入資料之前，

對新插入的amount欄位值進行求和計算

DECLARE @num INT
SET @num=0
INSERT INTO account VALUES(1,1.00),(2,2.00)
SELECT @num
首先建立一個account表，在向表account插入資料之前，計算所有新插入的account表的amount值之和，

觸發器的名稱為ins_num，條件是在向表插入資料之前觸發。

 

 

 

建立有多個執行語句的觸發器，文法如下：

DELIMITER |
 
CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1; 
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
|

查看觸發器

查看觸發器是指資料庫中已存在的觸發器的定義、狀態、文法資訊等。

可以使用SHOW TRIGGERS 和在TRIGGERS 表中查看觸發器資訊

SHOW TRIGGERS
 

Trigger  Event   Table    Statement                 Timing  Created  sql_mode  Definer         character_set_client  collation_connection  Database Collation
-------  ------  -------  ------------------------  ------  -------  --------  --------------  --------------------  --------------------  ------------------
ins_sum  INSERT  account  set @sum=@sum+new.amount  BEFORE  (NULL)             root@localhost  utf8                  utf8_general_ci       utf8_general_ci  

EVENT表示啟用觸發器的事件，這裡的觸發事件為插入操作INSERT，TABLE表示啟用觸發器的對象表，這裡為account 表

Timing表示觸發器的時間，為插入之前（BEFORE）；Statement 表示觸發器執行的操作，還有一些其他資訊，比如SQL模式，觸發器的定義帳戶和字元集等

 

在TRIGGERS 表中查看觸發器資訊

information_schema資料庫的TRIGGERS 表中，可以通過查詢查看觸發器資訊

SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='ins_sum'

TRIGGER_CATALOG  TRIGGER_SCHEMA  TRIGGER_NAME  EVENT_MANIPULATION  EVENT_OBJECT_CATALOG  EVENT_OBJECT_SCHEMA  EVENT_OBJECT_TABLE  ACTION_ORDER  ACTION_CONDITION  ACTION_STATEMENT          ACTION_ORIENTATION  ACTION_TIMING  ACTION_REFERENCE_OLD_TABLE  ACTION_REFERENCE_NEW_TABLE  ACTION_REFERENCE_OLD_ROW  ACTION_REFERENCE_NEW_ROW  CREATED  SQL_MODE  DEFINER         CHARACTER_SET_CLIENT  COLLATION_CONNECTION  DATABASE_COLLATION
---------------  --------------  ------------  ------------------  --------------------  -------------------  ------------------  ------------  ----------------  ------------------------  ------------------  -------------  --------------------------  --------------------------  ------------------------  ------------------------  -------  --------  --------------  --------------------  --------------------  ------------------
def              school          ins_sum       INSERT              def                   school               account                        0  (NULL)            set @sum=@sum+new.amount  ROW                 BEFORE         (NULL)                      (NULL)                      OLD                       NEW                       (NULL)             root@localhost  utf8                  utf8_general_ci       utf8_general_ci  

TRIGGER_SCHEMA 表示觸發器所在的資料庫

TRIGGER_NAME表示觸發器的名稱

EVENT_OBJECT_TABLE表示在哪個表上觸發

ACTION_STATEMENT 表示觸發器觸發的時候執行的具體操作

ACTION_ORIENTATION是ROW，表示在每條記錄上都觸發

ACTION_TIMING表示觸發的時刻是BEFORE

刪除觸發器

使用DROP TRIGGER 語句可以刪除MYSQL中已經定義的觸發器，刪除觸發器的基本文法

DROP TRIGGER [schema_name.]trigger_name
其中（schema_name）是可選的

如果省略了schema（方案），將從當前方案中捨棄觸發程式。

刪除ins_sum觸發器

DROP TRIGGER `school`.`ins_sum`

觸發器ins_sum刪除成功

總結

對於相同的表，相同的事件只能建立一個觸發器，比如對錶account建立了BEFORE INSERT觸發器

那麼如果對錶account再次建立一個BEFORE INSERT觸發器，MYSQL就會報錯，此時，只可以在表account上

建立AFTER INSERT或者BEFORE UPDATE類型的觸發器

 

如有不對的地方，歡迎大家拍磚o(∩_∩)o 

本文著作權歸作者所有，未經作者同意不得轉載。

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

CREATE TRIGGER ins_sum BEFORE INSERT
ON account FOR EACH ROW
UPDATE
  `employee`
SET
  `Age` = new.acct_num + 1
WHERE `ID` = new.acct_num ;

INSERT INTO account(acct_num,amount) VALUES(1,2.00);

SELECT * FROM `employee`

需要注意以下幾點：

1.MySQL觸發器針對行來操作，因此當處理大資料集的時候可能效率很低。
2.觸發器不能保證原子性，例如在MYISAM中，當一個更新觸發器在更新一個表後，觸發對另外一個表的更新，若觸發器失敗，不會復原第一個表的更新。InnoDB中的觸發器和操作則是在一個事務中完成，是原子操作。