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

時(shí)間：2017-12-26 22:46:29

關(guān)鍵字： mysql 驅(qū)動(dòng)開(kāi)發(fā) mysql觸發(fā)器

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]什么是觸發(fā)器觸發(fā)器(TRIGGER)是MySQL的數(shù)據(jù)庫(kù)對(duì)象之一，從5.0.2版本開(kāi)始支持。該對(duì)象與編程語(yǔ)言中的函數(shù)非常類似，都需要聲明、執(zhí)行等。但是觸發(fā)器的執(zhí)行不是由程序調(diào)用，也

什么是觸發(fā)器

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

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_TIme trigger_event

ON tbl_name FOR EACH ROW

trigger_body

語(yǔ)法中

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)建簡(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

[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í)行語(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;[!--empirenews.page--]

《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事件類型時(shí)有效，表示當(dāng)前正在插入的數(shù)據(jù);同理，OLD在觸發(fā)器類型為DELETE事件類型時(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ǔ)句;[!--empirenews.page--]

觸發(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;