什么是觸發器

　　觸發器（TRIGGER）是MySQL的數據庫對象之一，從5.0.2版本開始支持。該對象與編程語言中的函數非常類似，都需要聲明、執行等。但是觸發器的執行不是由程序調用，也不是由手工啟動，而是由事件來觸發、激活從而實現執行。有點類似DOM中的事件。

　　那么為什么要使用數據庫對象觸發器呢？在具體開發項目時，經常會遇到如下實例：

　　《1》 在學生表中擁有字段學生姓名，字段學生總數，每當添加一條學生信息時，學生的總數就必須同時更改。

　　《2》 在學生表中還會有學生姓名的縮寫，學生住址等字段，添加學生信息時，往往需要檢查電話、郵箱等格式是否正確。

　　上面的例子使用觸發器完成時具有這樣的特點，需要在表發生改變時，自動進行一些處理。MySQL在觸發DELETE/UPDATE/INSERT語句時就會自動執行所設置的操作，其他SQL語句則不會激活觸發器。

　　mySQL

　　MySQL是一個關系型數據庫管理系統，由瑞典MySQL AB 公司開發，目前屬于 Oracle 旗下產品。MySQL 是最流行的關系型數據庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS （Relational Database Management System，關系數據庫管理系統） 應用軟件。

　　MySQL是一種關系數據庫管理系統，關系數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度并提高了靈活性。

　　MySQL所使用的 SQL 語言是用于訪問數據庫的最常用標準化語言。MySQL 軟件采用了雙授權政策，分為社區版和商業版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站數據庫。

　　由于其社區版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發環境。

　　創建觸發器

　　使用幫助命令查看具體的語法：

　　CREATE

　　［DEFINER = { user | CURRENT_USER }］

　　TRIGGER trigger_name

　　trigger_time trigger_event

　　ON tbl_name FOR EACH ROW

　　trigger_body

　　語法中

　　trigger_name：觸發器的名稱，不能與已經存在的觸發器重復；

　　trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后觸發；

　　trigger_event：：{ INSERT |UPDATE | DELETE }，觸發該觸發器的具體事件；

　　tbl_name：該觸發器作用在tbl_name上；

　　創建簡單觸發器

　　示例1，創建簡單觸發器

　　《1》 準備學生表和學生數目統計表

　　CREATE TABLE student_info （

　　stu_no INT（11） NOT NULL AUTO_INCREMENT，

　　stu_name VARCHAR（255） DEFAULT NULL，

　　PRIMARY KEY （stu_no）

　　）;

　　CREATE TABLE student_count （

　　student_count INT（11） DEFAULT 0

　　）;

　　INSERT INTO student_count VALUES（0）;

　　《2》 創建簡單觸發器，在向學生表INSERT數據時，學生數增加，DELETE學生時，學生數減少

　　CREATE TRIGGER trigger_student_count_insert

　　AFTER INSERT

　　ON student_info FOR EACH ROW

　　UPDATE student_count SET student_count=student_count+1;

　　CREATE TRIGGER trigger_student_count_delete

　　AFTER DELETE

　　ON student_info FOR EACH ROW

　　UPDATE student_count SET student_count=student_count-1;

　　《3》 INSERT、DELETE數據，查看觸發器是否正常工作

　　mysql》 INSERT INTO student_info VALUES（NULL，‘張明’），（NULL，‘李明’），（NULL，‘王明’）;

　　Query OK， 3 rows affected （0.02 sec）

　　Records： 3 Duplicates： 0 Warnings： 0

　　mysql》 SELECT * FROM student_info;

　　+--------+----------+

　　| stu_no | stu_name |

　　+--------+----------+

　　| 1 | 張明 |

　　| 2 | 李明 |

　　| 3 | 王明 |

　　+--------+----------+

　　3 rows in set （0.00 sec）

　　mysql》 SELECT * FROM student_count;

　　+---------------+

　　| student_count |

　　+---------------+

　　| 3 |

　　+---------------+

　　1 row in set （0.00 sec）

　　mysql》 DELETE FROM student_info WHERE stu_name IN（‘張明’，‘李明’）;

　　Query OK， 2 rows affected （0.00 sec）

　　mysql》 SELECT * FROM student_info;

　　+--------+----------+

　　| stu_no | stu_name |

　　+--------+----------+

　　| 3 | 王明 |

　　+--------+----------+

　　1 row in set （0.00 sec）

　　mysql》 SELECT * FROM student_count;

　　+---------------+

　　| student_count |

　　+---------------+

　　| 1 |

　　+---------------+

　　1 row in set （0.00 sec）

　　可以看到無論是INSERT還是DELETE學生，學生數目都是跟隨著變化的。

　　創建包含多條執行語句的觸發器

　　在trigger_body中可以執行多條SQL語句，此時的trigger_body需要使用BEGIN和END作為開始和結束的標志：

　　CREATE

　　［DEFINER = { user | CURRENT_USER }］

　　TRIGGER trigger_name

　　trigger_time trigger_event

　　ON tbl_name FOR EACH ROW

　　BEGIN

　　trigger_statement

　　END;

　　示例2，創建包含多條執行語句的觸發器

　　依然沿用上面的例子中的表，對student_count表做如下變更：增加student_class字段表示具體年級的學生數，其中0表示全年級，1代表1年級……；同樣學生表中也增加該字段。清空兩個表中的所有數據。

　　《1》 刪除上例中的兩個觸發器，初始化student_count表中數據，插入三條數據（0，0），（1，0），（2，0）表示全年級、一年級、二年級的初始人數都是0；

　　《2》 創建觸發器，在INSERT時首先增加學生總人數，然后判斷新增的學生是幾年級的，再增加對應年級的學生總數：

　　DELIMITER $$

　　CREATE TRIGGER trigger_student_count_insert

　　AFTER INSERT

　　ON student_info FOR EACH ROW

　　BEGIN

　　UPDATE student_count SET student_count=student_count+1 WHERE student_class=0;

　　UPDATE student_count SET student_count=student_count+1 WHERE student_class= NEW.student_class;

　　END

　　$$

　　DELIMITER ;

　　《3》 創建觸發器，在DELETE時首先減少學生總人數，然后判斷刪除的學生是幾年級的，再減少對應年級的學生總數：

　　DELIMITER $$

　　CREATE TRIGGER trigger_student_count_delete

　　AFTER DELETE

　　ON student_info FOR EACH ROW

　　BEGIN

　　UPDATE student_count SET student_count=student_count-1 WHERE student_class=0;

　　UPDATE student_count SET student_count=student_count-1 WHERE student_class= OLD.student_class;

　　END

　　$$

　　DELIMITER ;

　　《4》 向學生表中分別插入多條不同年級的學生信息，查看觸發器是否起作用：

　　mysql》 INSERT INTO student_info VALUES（NULL，‘AAA’，1），（NULL，‘BBB’，1），（NULL，‘CCC’，2），（NULL，‘DDD’，2），（NULL，‘ABB’，1），（NULL，‘ACC’，1）;

　　Query OK， 6 rows affected （0.02 sec）

　　Records： 6 Duplicates： 0 Warnings： 0

　　mysql》 SELECT * FROM student_info;

　　+--------+----------+---------------+

　　| stu_no | stu_name | student_class |

　　+--------+----------+---------------+

　　| 4 | AAA | 1 |

　　| 5 | BBB | 1 |

　　| 6 | CCC | 2 |

　　| 7 | DDD | 2 |

　　| 8 | ABB | 1 |

　　| 9 | ACC | 1 |

　　+--------+----------+---------------+

　　6 rows in set （0.00 sec）

　　mysql》 SELECT * FROM student_count;

　　+---------------+---------------+

　　| student_count | student_class |

　　+---------------+---------------+

　　| 6 | 0 |

　　| 4 | 1 |

　　| 2 | 2 |

　　+---------------+---------------+

　　3 rows in set （0.00 sec）

　　可以看到，總共插入了6條數據，學生總數是6，1年級4個，2年級2個，trigger正確執行。

　　《5》 從學生表中分別刪除多條不同年級的學生信息，查看觸發器是否起作用：

　　mysql》 DELETE FROM student_info WHERE stu_name LIKE ‘A%’;

　　Query OK， 3 rows affected （0.02 sec）

　　mysql》 SELECT * FROM student_info;

　　+--------+----------+---------------+

　　| stu_no | stu_name | student_class |

　　+--------+----------+---------------+

　　| 5 | BBB | 1 |

　　| 6 | CCC | 2 |

　　| 7 | DDD | 2 |

　　+--------+----------+---------------+

　　3 rows in set （0.00 sec）

　　mysql》 SELECT * FROM student_count;

　　+---------------+---------------+

　　| student_count | student_class |

　　+---------------+---------------+

　　| 3 | 0 |

　　| 1 | 1 |

　　| 2 | 2 |

　　+---------------+---------------+

　　3 rows in set （0.00 sec）

　　從學生表中將姓名以A開頭的學生信息刪除，學生信息刪除的同時，數量表也跟隨變化。

　　在上面的示例中，使用了三個新的關鍵字：DELIMITER、NEW、OLD，這三個關鍵字在官網上“觸發器語法”一節中都有介紹，整理如下：

　　DELIMITER

　　使用BEGIN…END結構，可以定義一個執行多句SQL的觸發器。在BEGIN語句塊中，還可以使用其它的語法，例如條件語句和循環語句。在MySQL中，分號”;”標志著SQL語句的結束，但是在觸發器要執行的SQL語句中使用到了”;”作為要執行SQL語句的結束標記，所以你需要重新定義結束標識符。

　　重新定義結束標識符使用DELIMITER關鍵字，后面跟空格和重新定義的結束標識符。

　　注意：該語句與其他語句不同的是不需要在語句末尾添加結束標志符，如DELIMITER

　　的作用是將現有的結束標識符重新定義為，但是，此時由于習慣或是疏忽在末尾添加了”;”也就是” DELIMITER;”那么該語句的作用就變成了將符號”;”作為新的結束標志符。

　　NEW和OLD

　　NEW在觸發器為INSERT事件類型時有效，表示當前正在插入的數據；同理，OLD在觸發器類型為DELETE事件類型時有效，表示當前正在刪除的數據。

　　如上面的示例中，可以在觸發器中使用NEW.student_class取得正在插入的學生信息中年級值，使用OLD.student_class取得正在刪除的學生信息中的年級值。

　　觸發器的使用限制

　　官網“觸發器語法和示例” http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

　　《1》 觸發器只能創建在永久表上，不能對臨時表創建觸發器；

　　《2》 觸發器不能使用CALL語句調用具有返回值或使用了動態SQL的存儲過程（存儲過程可以使用OUT或INOUT參數返回給觸發器返回值）。

　　《3》 觸發器中不能使用開啟或結束事務的語句段，比如，開始事務（START TRANSACTION）、提交事務（COMMIT）或是回滾事務（ROLLBACK），但是回滾到一個保存點（SAVEPOINT是允許的，因為回滾到保存點不會結束事務）；

　　《4》 外鍵不會激活觸發器；

　　《5》 當使用基于行的復制時，從表上的觸發器不會因操作主表中的數據而激活。當使用基于語句的復制時，從表上的觸發器會被激活。參考 Section 17.4.1.34，“Replication and Triggers”；

　　《6》 觸發器中不允許返回值，因此觸發器中不能有返回語句，如果要立即停止一個觸發器，應該使用LEAVE語句；

　　觸發器中的異常機制

　　MySQL的觸發器是按照BEFORE觸發器、行操作、AFTER觸發器的順序執行的，其中任何一步發生錯誤都不會繼續執行剩下的操作。如果是對事務表進行的操作，那么會整個作為一個事務被回滾，但是如果是對非事務表進行的操作，那么已經更新的記錄將無法回滾，這也是設計觸發器的時候需要注意的問題。

　　查看觸發器

　　可以通過執行SHOW TRIGGERS命令查看觸發器，但是因為不能查詢指定的觸發器，所以每次都返回所有的觸發器的信息，使用不方便。但是可以使用查詢系統表information_schema.triggers的方式指定查詢條件，查看指定的觸發器信息。如：

　　mysql》 USE information_schema;

　　Database changed

　　mysql》 SELECT * FROM triggers WHERE trigger_name=‘trigger_student_count_insert’;

　　刪除觸發器

　　DROP TRIGGER trigger_name;