電子發(fā)燒友App
什么是觸發(fā)器
觸發(fā)器（TRIGGER）是MySQL的數(shù)據(jù)庫對象之一，從5.0.2版本開始支持。該對象與編程語言中的函數(shù)非常類似，都需要聲明、執(zhí)行等。但是觸發(fā)器的執(zhí)行不是由程序調(diào)用，也不是由手工啟動，而是由事件來觸發(fā)、激活從而實現(xiàn)執(zhí)行。有點類似DOM中的事件。
那么為什么要使用數(shù)據(jù)庫對象觸發(fā)器呢？在具體開發(fā)項目時，經(jīng)常會遇到如下實例：
《1》 在學(xué)生表中擁有字段學(xué)生姓名，字段學(xué)生總數(shù)，每當(dāng)添加一條學(xué)生信息時，學(xué)生的總數(shù)就必須同時更改。
《2》 在學(xué)生表中還會有學(xué)生姓名的縮寫，學(xué)生住址等字段，添加學(xué)生信息時，往往需要檢查電話、郵箱等格式是否正確。
上面的例子使用觸發(fā)器完成時具有這樣的特點，需要在表發(fā)生改變時，自動進(jìn)行一些處理。MySQL在觸發(fā)DELETE/UPDATE/INSERT語句時就會自動執(zhí)行所設(shè)置的操作，其他SQL語句則不會激活觸發(fā)器。
mySQL
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS （Relational Database Management System，關(guān)系數(shù)據(jù)庫管理系統(tǒng)） 應(yīng)用軟件。
MySQL是一種關(guān)系數(shù)據(jù)庫管理系統(tǒng)，關(guān)系數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個大倉庫內(nèi)，這樣就增加了速度并提高了靈活性。
MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用標(biāo)準(zhǔn)化語言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫。
由于其社區(qū)版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發(fā)環(huán)境。
創(chuàng)建觸發(fā)器
使用幫助命令查看具體的語法：
CREATE
［DEFINER = { user | CURRENT_USER }］
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
語法中
trigger_name：觸發(fā)器的名稱，不能與已經(jīng)存在的觸發(fā)器重復(fù)；
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后觸發(fā)；
trigger_event：：{ INSERT |UPDATE | DELETE }，觸發(fā)該觸發(fā)器的具體事件；
tbl_name：該觸發(fā)器作用在tbl_name上；
創(chuàng)建簡單觸發(fā)器
示例1，創(chuàng)建簡單觸發(fā)器
《1》 準(zhǔn)備學(xué)生表和學(xué)生數(shù)目統(tǒng)計表
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)建簡單觸發(fā)器，在向?qū)W生表INSERT數(shù)據(jù)時，學(xué)生數(shù)增加，DELETE學(xué)生時，學(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）
可以看到無論是INSERT還是DELETE學(xué)生，學(xué)生數(shù)目都是跟隨著變化的。
創(chuàng)建包含多條執(zhí)行語句的觸發(fā)器
在trigger_body中可以執(zhí)行多條SQL語句，此時的trigger_body需要使用BEGIN和END作為開始和結(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í)行語句的觸發(fā)器
依然沿用上面的例子中的表，對student_count表做如下變更：增加student_class字段表示具體年級的學(xué)生數(shù)，其中0表示全年級，1代表1年級……；同樣學(xué)生表中也增加該字段。清空兩個表中的所有數(shù)據(jù)。
《1》 刪除上例中的兩個觸發(fā)器，初始化student_count表中數(shù)據(jù)，插入三條數(shù)據(jù)（0，0），（1，0），（2，0）表示全年級、一年級、二年級的初始人數(shù)都是0；
《2》 創(chuàng)建觸發(fā)器，在INSERT時首先增加學(xué)生總?cè)藬?shù)，然后判斷新增的學(xué)生是幾年級的，再增加對應(yīng)年級的學(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時首先減少學(xué)生總?cè)藬?shù)，然后判斷刪除的學(xué)生是幾年級的，再減少對應(yīng)年級的學(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生表中分別插入多條不同年級的學(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年級4個，2年級2個，trigger正確執(zhí)行。
《5》 從學(xué)生表中分別刪除多條不同年級的學(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開頭的學(xué)生信息刪除，學(xué)生信息刪除的同時，數(shù)量表也跟隨變化。
在上面的示例中，使用了三個新的關(guān)鍵字：DELIMITER、NEW、OLD，這三個關(guān)鍵字在官網(wǎng)上“觸發(fā)器語法”一節(jié)中都有介紹，整理如下：
DELIMITER
使用BEGIN…END結(jié)構(gòu)，可以定義一個執(zhí)行多句SQL的觸發(fā)器。在BEGIN語句塊中，還可以使用其它的語法，例如條件語句和循環(huán)語句。在MySQL中，分號”;”標(biāo)志著SQL語句的結(jié)束，但是在觸發(fā)器要執(zhí)行的SQL語句中使用到了”;”作為要執(zhí)行SQL語句的結(jié)束標(biāo)記，所以你需要重新定義結(jié)束標(biāo)識符。
重新定義結(jié)束標(biāo)識符使用DELIMITER關(guān)鍵字，后面跟空格和重新定義的結(jié)束標(biāo)識符。
注意：該語句與其他語句不同的是不需要在語句末尾添加結(jié)束標(biāo)志符，如DELIMITER
的作用是將現(xiàn)有的結(jié)束標(biāo)識符重新定義為，但是，此時由于習(xí)慣或是疏忽在末尾添加了”;”也就是” DELIMITER;”那么該語句的作用就變成了將符號”;”作為新的結(jié)束標(biāo)志符。
NEW和OLD
NEW在觸發(fā)器為INSERT事件類型時有效，表示當(dāng)前正在插入的數(shù)據(jù)；同理，OLD在觸發(fā)器類型為DELETE事件類型時有效，表示當(dāng)前正在刪除的數(shù)據(jù)。
如上面的示例中，可以在觸發(fā)器中使用NEW.student_class取得正在插入的學(xué)生信息中年級值，使用OLD.student_class取得正在刪除的學(xué)生信息中的年級值。
觸發(fā)器的使用限制
官網(wǎng)“觸發(fā)器語法和示例” http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html
《1》 觸發(fā)器只能創(chuàng)建在永久表上，不能對臨時表創(chuàng)建觸發(fā)器；
《2》 觸發(fā)器不能使用CALL語句調(diào)用具有返回值或使用了動態(tài)SQL的存儲過程（存儲過程可以使用OUT或INOUT參數(shù)返回給觸發(fā)器返回值）。
《3》 觸發(fā)器中不能使用開啟或結(jié)束事務(wù)的語句段，比如，開始事務(wù)（START TRANSACTION）、提交事務(wù)（COMMIT）或是回滾事務(wù)（ROLLBACK），但是回滾到一個保存點（SAVEPOINT是允許的，因為回滾到保存點不會結(jié)束事務(wù)）；
《4》 外鍵不會激活觸發(fā)器；
《5》 當(dāng)使用基于行的復(fù)制時，從表上的觸發(fā)器不會因操作主表中的數(shù)據(jù)而激活。當(dāng)使用基于語句的復(fù)制時，從表上的觸發(fā)器會被激活。參考 Section 17.4.1.34，“Replication and Triggers”；
《6》 觸發(fā)器中不允許返回值，因此觸發(fā)器中不能有返回語句，如果要立即停止一個觸發(fā)器，應(yīng)該使用LEAVE語句；
觸發(fā)器中的異常機(jī)制
MySQL的觸發(fā)器是按照BEFORE觸發(fā)器、行操作、AFTER觸發(fā)器的順序執(zhí)行的，其中任何一步發(fā)生錯誤都不會繼續(xù)執(zhí)行剩下的操作。如果是對事務(wù)表進(jìn)行的操作，那么會整個作為一個事務(wù)被回滾，但是如果是對非事務(wù)表進(jìn)行的操作，那么已經(jīng)更新的記錄將無法回滾，這也是設(shè)計觸發(fā)器的時候需要注意的問題。
查看觸發(fā)器
可以通過執(zhí)行SHOW TRIGGERS命令查看觸發(fā)器，但是因為不能查詢指定的觸發(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;
工商網(wǎng)監(jiān)
評論