什么是觸發(fā)器

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

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

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

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

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

 

mySQL

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

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

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

由于其社區(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ā)器的名稱，不能與已經存在的觸發(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》 準備學生表和學生數目統(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ā)器，在向學生表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數據，查看觸發(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學生，學生數目都是跟隨著變化的。

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

在trigger_body中可以執(zhí)行多條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，創(chuàng)建包含多條執(zhí)行語句的觸發(fā)器

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

《1》 刪除上例中的兩個觸發(fā)器，初始化student_count表中數據，插入三條數據(0，0)，(1，0)，(2，0)表示全年級、一年級、二年級的初始人數都是0;[!--empirenews.page--]

《2》 創(chuàng)建觸發(fā)器，在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》 創(chuàng)建觸發(fā)器，在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》 向學生表中分別插入多條不同年級的學生信息，查看觸發(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條數據，學生總數是6，1年級4個，2年級2個，trigger正確執(zhí)行。

《5》 從學生表中分別刪除多條不同年級的學生信息，查看觸發(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)

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

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

DELIMITER

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

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

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

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

NEW和OLD

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

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

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

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

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

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

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

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

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

《6》 觸發(fā)器中不允許返回值，因此觸發(fā)器中不能有返回語句，如果要立即停止一個觸發(fā)器，應該使用LEAVE語句;[!--empirenews.page--]

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

MySQL的觸發(fā)器是按照BEFORE觸發(fā)器、行操作、AFTER觸發(fā)器的順序執(zhí)行的，其中任何一步發(fā)生錯誤都不會繼續(xù)執(zhí)行剩下的操作。如果是對事務表進行的操作，那么會整個作為一個事務被回滾，但是如果是對非事務表進行的操作，那么已經更新的記錄將無法回滾，這也是設計觸發(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;