mysql觸發(fā)器的創(chuàng)建和使用

　　什么是觸發(fā)器

　　觸發(fā)器（TRIGGER）是MySQL的數(shù)據(jù)庫(kù)對(duì)象之一，從5.0.2版本開(kāi)始支持。該對(duì)象與編程語(yǔ)言中的函數(shù)非常類(lèi)似，都需要聲明、執(zhí)行等。但是觸發(fā)器的執(zhí)行不是由程序調(diào)用，也不是由手工啟動(dòng)，而是由事件來(lái)觸發(fā)、激活從而實(shí)現(xiàn)執(zhí)行。有點(diǎn)類(lèi)似DOM中的事件。

　　那么為什么要使用數(shù)據(jù)庫(kù)對(duì)象觸發(fā)器呢？在具體開(kāi)發(fā)項(xiàng)目時(shí)，經(jīng)常會(huì)遇到如下實(shí)例：

　　《1》 在學(xué)生表中擁有字段學(xué)生姓名，字段學(xué)生總數(shù)，每當(dāng)添加一條學(xué)生信息時(shí)，學(xué)生的總數(shù)就必須同時(shí)更改。

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

　　上面的例子使用觸發(fā)器完成時(shí)具有這樣的特點(diǎn)，需要在表發(fā)生改變時(shí)，自動(dòng)進(jìn)行一些處理。MySQL在觸發(fā)DELETE/UPDATE/INSERT語(yǔ)句時(shí)就會(huì)自動(dòng)執(zhí)行所設(shè)置的操作，其他SQL語(yǔ)句則不會(huì)激活觸發(fā)器。

　　mySQL

　　MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開(kāi)發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS （Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)） 應(yīng)用軟件。

　　MySQL是一種關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)系數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。

　　MySQL所使用的 SQL 語(yǔ)言是用于訪問(wèn)數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語(yǔ)言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開(kāi)放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開(kāi)發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。

　　由于其社區(qū)版的性能卓越，搭配 PHP 和 Apache 可組成良好的開(kāi)發(fā)環(huán)境。

　　創(chuàng)建觸發(fā)器

　　使用幫助命令查看具體的語(yǔ)法：

　　CREATE

　?。跠EFINER = { user | CURRENT_USER }］

　　TRIGGER trigger_name

　　trigger_time trigger_event

　　ON tbl_name FOR EACH ROW

　　trigger_body

　　語(yǔ)法中

　　trigger_name：觸發(fā)器的名稱(chēng)，不能與已經(jīng)存在的觸發(fā)器重復(fù)；

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

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

　　tbl_name：該觸發(fā)器作用在tbl_name上；

　　創(chuàng)建簡(jiǎn)單觸發(fā)器

　　示例1，創(chuàng)建簡(jiǎn)單觸發(fā)器

　　《1》 準(zhǔn)備學(xué)生表和學(xué)生數(shù)目統(tǒng)計(jì)表

　　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》 創(chuàng)建簡(jiǎn)單觸發(fā)器，在向?qū)W生表INSERT數(shù)據(jù)時(shí)，學(xué)生數(shù)增加，DELETE學(xué)生時(shí)，學(xué)生數(shù)減少

　　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數(shù)據(jù)，查看觸發(fā)器是否正常工作

　　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）

　　可以看到無(wú)論是INSERT還是DELETE學(xué)生，學(xué)生數(shù)目都是跟隨著變化的。

　　創(chuàng)建包含多條執(zhí)行語(yǔ)句的觸發(fā)器

　　在trigger_body中可以執(zhí)行多條SQL語(yǔ)句，此時(shí)的trigger_body需要使用BEGIN和END作為開(kāi)始和結(jié)束的標(biāo)志：

　　CREATE

　?。跠EFINER = { user | CURRENT_USER }］

　　TRIGGER trigger_name

　　trigger_time trigger_event

　　ON tbl_name FOR EACH ROW

　　BEGIN

　　trigger_statement

　　END;

　　示例2，創(chuàng)建包含多條執(zhí)行語(yǔ)句的觸發(fā)器

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

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

　　《2》 創(chuàng)建觸發(fā)器，在INSERT時(shí)首先增加學(xué)生總?cè)藬?shù)，然后判斷新增的學(xué)生是幾年級(jí)的，再增加對(duì)應(yīng)年級(jí)的學(xué)生總數(shù)：

　　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》 創(chuàng)建觸發(fā)器，在DELETE時(shí)首先減少學(xué)生總?cè)藬?shù)，然后判斷刪除的學(xué)生是幾年級(jí)的，再減少對(duì)應(yīng)年級(jí)的學(xué)生總數(shù)：

　　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》 向?qū)W生表中分別插入多條不同年級(jí)的學(xué)生信息，查看觸發(fā)器是否起作用：

　　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條數(shù)據(jù)，學(xué)生總數(shù)是6，1年級(jí)4個(gè)，2年級(jí)2個(gè)，trigger正確執(zhí)行。

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

　　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）

　　從學(xué)生表中將姓名以A開(kāi)頭的學(xué)生信息刪除，學(xué)生信息刪除的同時(shí)，數(shù)量表也跟隨變化。

　　在上面的示例中，使用了三個(gè)新的關(guān)鍵字：DELIMITER、NEW、OLD，這三個(gè)關(guān)鍵字在官網(wǎng)上“觸發(fā)器語(yǔ)法”一節(jié)中都有介紹，整理如下：

　　DELIMITER

　　使用BEGIN…END結(jié)構(gòu)，可以定義一個(gè)執(zhí)行多句SQL的觸發(fā)器。在BEGIN語(yǔ)句塊中，還可以使用其它的語(yǔ)法，例如條件語(yǔ)句和循環(huán)語(yǔ)句。在MySQL中，分號(hào)”;”標(biāo)志著SQL語(yǔ)句的結(jié)束，但是在觸發(fā)器要執(zhí)行的SQL語(yǔ)句中使用到了”;”作為要執(zhí)行SQL語(yǔ)句的結(jié)束標(biāo)記，所以你需要重新定義結(jié)束標(biāo)識(shí)符。

　　重新定義結(jié)束標(biāo)識(shí)符使用DELIMITER關(guān)鍵字，后面跟空格和重新定義的結(jié)束標(biāo)識(shí)符。

　　注意：該語(yǔ)句與其他語(yǔ)句不同的是不需要在語(yǔ)句末尾添加結(jié)束標(biāo)志符，如DELIMITER

　　的作用是將現(xiàn)有的結(jié)束標(biāo)識(shí)符重新定義為，但是，此時(shí)由于習(xí)慣或是疏忽在末尾添加了”;”也就是” DELIMITER;”那么該語(yǔ)句的作用就變成了將符號(hào)”;”作為新的結(jié)束標(biāo)志符。

　　NEW和OLD

　　NEW在觸發(fā)器為INSERT事件類(lèi)型時(shí)有效，表示當(dāng)前正在插入的數(shù)據(jù)；同理，OLD在觸發(fā)器類(lèi)型為DELETE事件類(lèi)型時(shí)有效，表示當(dāng)前正在刪除的數(shù)據(jù)。

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

　　觸發(fā)器的使用限制

　　官網(wǎng)“觸發(fā)器語(yǔ)法和示例” http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

　　《1》 觸發(fā)器只能創(chuàng)建在永久表上，不能對(duì)臨時(shí)表創(chuàng)建觸發(fā)器；

　　《2》 觸發(fā)器不能使用CALL語(yǔ)句調(diào)用具有返回值或使用了動(dòng)態(tài)SQL的存儲(chǔ)過(guò)程（存儲(chǔ)過(guò)程可以使用OUT或INOUT參數(shù)返回給觸發(fā)器返回值）。

　　《3》 觸發(fā)器中不能使用開(kāi)啟或結(jié)束事務(wù)的語(yǔ)句段，比如，開(kāi)始事務(wù)（START TRANSACTION）、提交事務(wù)（COMMIT）或是回滾事務(wù)（ROLLBACK），但是回滾到一個(gè)保存點(diǎn)（SAVEPOINT是允許的，因?yàn)榛貪L到保存點(diǎn)不會(huì)結(jié)束事務(wù)）；

　　《4》 外鍵不會(huì)激活觸發(fā)器；

　　《5》 當(dāng)使用基于行的復(fù)制時(shí)，從表上的觸發(fā)器不會(huì)因操作主表中的數(shù)據(jù)而激活。當(dāng)使用基于語(yǔ)句的復(fù)制時(shí)，從表上的觸發(fā)器會(huì)被激活。參考 Section 17.4.1.34，“Replication and Triggers”；

　　《6》 觸發(fā)器中不允許返回值，因此觸發(fā)器中不能有返回語(yǔ)句，如果要立即停止一個(gè)觸發(fā)器，應(yīng)該使用LEAVE語(yǔ)句；

　　觸發(fā)器中的異常機(jī)制

　　MySQL的觸發(fā)器是按照BEFORE觸發(fā)器、行操作、AFTER觸發(fā)器的順序執(zhí)行的，其中任何一步發(fā)生錯(cuò)誤都不會(huì)繼續(xù)執(zhí)行剩下的操作。如果是對(duì)事務(wù)表進(jìn)行的操作，那么會(huì)整個(gè)作為一個(gè)事務(wù)被回滾，但是如果是對(duì)非事務(wù)表進(jìn)行的操作，那么已經(jīng)更新的記錄將無(wú)法回滾，這也是設(shè)計(jì)觸發(fā)器的時(shí)候需要注意的問(wèn)題。

　　查看觸發(fā)器

　　可以通過(guò)執(zhí)行SHOW TRIGGERS命令查看觸發(fā)器，但是因?yàn)椴荒懿樵冎付ǖ挠|發(fā)器，所以每次都返回所有的觸發(fā)器的信息，使用不方便。但是可以使用查詢系統(tǒng)表information_schema.triggers的方式指定查詢條件，查看指定的觸發(fā)器信息。如：

　　mysql》 USE information_schema;

　　Database changed

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

　　刪除觸發(fā)器

　　DROP TRIGGER trigger_name;