MySQL觸發(fā)器trigger的使用
一、觸發(fā)器簡(jiǎn)介
1、什么是觸發(fā)器?
觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫(kù)對(duì)象,在滿(mǎn)足定義條件時(shí)觸發(fā),并執(zhí)行觸發(fā)器中定義的語(yǔ)句集合。
2、觸發(fā)器的特性
- 有
begin end
體,begin end
;之間的語(yǔ)句可以寫(xiě)的簡(jiǎn)單或者復(fù)雜 - 什么條件會(huì)觸發(fā):I、D、U
- 什么時(shí)候觸發(fā):在增刪改前或者后
- 觸發(fā)頻率:針對(duì)每一行執(zhí)行
- 觸發(fā)器定義在表上,附著在表上。
也就是由事件來(lái)觸發(fā)某個(gè)操作,事件包括INSERT
語(yǔ)句,UPDATE
語(yǔ)句和DELETE
語(yǔ)句;可以協(xié)助應(yīng)用在數(shù)據(jù)庫(kù)端確保數(shù)據(jù)的完整性。
注意:cannot associate a trigger with a TEMPORARY table or a view.
?。”M量少使用觸發(fā)器,不建議使用。
假設(shè)觸發(fā)器觸發(fā)每次執(zhí)行1s,insert table 500
條數(shù)據(jù),那么就需要觸發(fā)500次觸發(fā)器,光是觸發(fā)器執(zhí)行的時(shí)間就花費(fèi)了500s
,而insert 500
條數(shù)據(jù)一共是1s,那么這個(gè)insert
的效率就非常低了。因此我們特別需要注意的一點(diǎn)是觸發(fā)器的begin end
;之間的語(yǔ)句的執(zhí)行效率一定要高,資源消耗要小。
觸發(fā)器盡量少的使用,因?yàn)椴还苋绾?,它還是很消耗資源,如果使用的話(huà)要謹(jǐn)慎的使用,確定它是非常高效的:觸發(fā)器是針對(duì)每一行的;對(duì)增刪改非常頻繁的表上切記不要使用觸發(fā)器,因?yàn)樗鼤?huì)非常消耗資源。
二、、創(chuàng)建觸發(fā)器
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
> BEFORE
和AFTER
參數(shù)指定了觸發(fā)執(zhí)行的時(shí)間,在事件之前或是之后。
> FOR EACH ROW
表示任何一條記錄上的操作滿(mǎn)足觸發(fā)事件都會(huì)觸發(fā)該觸發(fā)器,也就是說(shuō)觸發(fā)器的觸發(fā)頻率是針對(duì)每一行數(shù)據(jù)觸發(fā)一次。
> tigger_event
詳解:
①I(mǎi)NSERT型觸發(fā)器
:插入某一行時(shí)激活觸發(fā)器,可能通過(guò)INSERT
、LOAD DATA
、REPLACE
語(yǔ)句觸發(fā)(LOAD DAT
語(yǔ)句用于將一個(gè)文件裝入到一個(gè)數(shù)據(jù)表中,相當(dāng)與一系列的INSERT操作);
②UPDATE型觸發(fā)器
:更改某一行時(shí)激活觸發(fā)器,可能通過(guò)UPDATE
語(yǔ)句觸發(fā);
③DELETE型觸發(fā)器
:刪除某一行時(shí)激活觸發(fā)器,可能通過(guò)DELETE
、REPLACE
語(yǔ)句觸發(fā)。
> trigger_order
是MySQL5.7
之后的一個(gè)功能,用于定義多個(gè)觸發(fā)器,使用follows
(尾隨)或precedes
(在…之先)來(lái)選擇觸發(fā)器執(zhí)行的先后順序。
1、創(chuàng)建只有一個(gè)執(zhí)行語(yǔ)句的觸發(fā)器
CREATE TRIGGER
觸發(fā)器名BEFORE|AFTER
觸發(fā)事件ON
表名FOR EACH ROW
執(zhí)行語(yǔ)句;
例1:創(chuàng)建了一個(gè)名為trig1的觸發(fā)器,一旦在work表中有插入動(dòng)作,就會(huì)自動(dòng)往time表里插入當(dāng)前時(shí)間
mysql> CREATE TRIGGER trig1 AFTER INSERT -> ON work FOR EACH ROW -> INSERT INTO time VALUES(NOW());
2、創(chuàng)建有多個(gè)執(zhí)行語(yǔ)句的觸發(fā)器
CREATE TRIGGER
觸發(fā)器名 BEFORE|AFTER
觸發(fā)事件
ON 表名 FOR EACH ROW
BEGIN
執(zhí)行語(yǔ)句列表
END;
例2:定義一個(gè)觸發(fā)器,一旦有滿(mǎn)足條件的刪除操作,就會(huì)執(zhí)行BEGIN
和END
中的語(yǔ)句
mysql> DELIMITER || mysql> CREATE TRIGGER trig2 BEFORE DELETE -> ON work FOR EACH ROW -> BEGIN -> INSERT INTO time VALUES(NOW()); -> INSERT INTO time VALUES(NOW()); -> END|| mysql> DELIMITER ;
3、NEW與OLD詳解
MySQL
中定義了 NEW
和 OLD
,用來(lái)表示觸發(fā)器的所在表中,觸發(fā)了觸發(fā)器的那一行數(shù)據(jù),來(lái)引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容,具體地:
①在INSERT
型觸發(fā)器中,NEW用來(lái)表示將要(BEFORE
)或已經(jīng)(AFTER
)插入的新數(shù)據(jù);
②在UPDATE
型觸發(fā)器中,OLD
用來(lái)表示將要或已經(jīng)被修改的原數(shù)據(jù),NEW
用來(lái)表示將要或已經(jīng)修改為的新數(shù)據(jù);
③在DELETE
型觸發(fā)器中,OLD
用來(lái)表示將要或已經(jīng)被刪除的原數(shù)據(jù);
使用方法:
NEW.columnName (columnName為相應(yīng)數(shù)據(jù)表某一列名)
另外,OLD
是只讀的,而NEW
則可以在觸發(fā)器中使用 SET
賦值,這樣不會(huì)再次觸發(fā)觸發(fā)器,造成循環(huán)調(diào)用(如每插入一個(gè)學(xué)生前,都在其學(xué)號(hào)前加“2013”)。
例3:
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); mysql> delimiter $$ mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount < 0 THEN -> SET NEW.amount = 0; -> ELSEIF NEW.amount > 100 THEN -> SET NEW.amount = 100; -> END IF; -> END$$ mysql> delimiter ; mysql> update account set amount=-10 where acct_num=137; mysql> select * from account; +----------+---------+ | acct_num | amount | +----------+---------+ | 137 | 0.00 | | 141 | 1937.50 | | 97 | -100.00 | +----------+---------+ mysql> update account set amount=200 where acct_num=137; mysql> select * from account; +----------+---------+ | acct_num | amount | +----------+---------+ | 137 | 100.00 | | 141 | 1937.50 | | 97 | -100.00 | +----------+---------+
三、查看觸發(fā)器
1、SHOW TRIGGERS語(yǔ)句查看觸發(fā)器信息
mysql> SHOW TRIGGERS\G;
……
結(jié)果,顯示所有觸發(fā)器的基本信息;無(wú)法查詢(xún)指定的觸發(fā)器。
2、在information_schema.triggers表中查看觸發(fā)器信息
mysql> SELECT * FROM information_schema.triggers\G
……
結(jié)果,顯示所有觸發(fā)器的詳細(xì)信息;同時(shí),該方法可以查詢(xún)制定觸發(fā)器的詳細(xì)信息。
mysql> select * from information_schema.triggers -> where trigger_name='upd_check'\G; Tips:
所有觸發(fā)器信息都存儲(chǔ)在information_schema
數(shù)據(jù)庫(kù)下的triggers
表中,可以使用SELECT
語(yǔ)句查詢(xún),如果觸發(fā)器信息過(guò)多,最好通過(guò)TRIGGER_NAME
字段指定查詢(xún)。
四、刪除觸發(fā)器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
刪除觸發(fā)器之后最好使用上面的方法查看一遍;同時(shí),也可以使用database.trig
來(lái)指定某個(gè)數(shù)據(jù)庫(kù)中的觸發(fā)器。
Tips:
如果不需要某個(gè)觸發(fā)器時(shí)一定要將這個(gè)觸發(fā)器刪除,以免造成意外操作,這很關(guān)鍵。
到此這篇關(guān)于MySQL
觸發(fā)器trigger
的使用的文章就介紹到這了,更多相關(guān)MySQL
觸發(fā)器trigger
使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL創(chuàng)建數(shù)據(jù)表時(shí)設(shè)定引擎MyISAM/InnoDB操作
這篇文章主要介紹了MySQL創(chuàng)建數(shù)據(jù)表時(shí)設(shè)定引擎MyISAM/InnoDB操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-08-08MySQL入門(mén)完全指南及Linux系統(tǒng)下基本的安裝教程
這篇文章主要介紹了MySQL入門(mén)完全指南及Linux系統(tǒng)下基本的安裝教程,對(duì)MySQL的基本特性有一個(gè)較為詳細(xì)的整理,需要的朋友可以參考下2015-11-11數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化之子查詢(xún)優(yōu)化
今天小編就為大家分享一篇關(guān)于數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化之子查詢(xún)優(yōu)化,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-01-01MySQL UNION操作符基礎(chǔ)知識(shí)點(diǎn)
在本文里小編給大家整理了關(guān)于MySQL UNION操作符的相關(guān)知識(shí)點(diǎn)內(nèi)容,需要的朋友們跟著學(xué)習(xí)下。2019-02-02MySQL使用mysqldump+binlog完整恢復(fù)被刪除的數(shù)據(jù)庫(kù)原理解析
這篇文章主要介紹了MySQL使用mysqldump+binlog完整恢復(fù)被刪除的數(shù)據(jù)庫(kù),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-04-04bitronix 連接 MySQL 出現(xiàn)MySQLSyntaxErrorException 的解決方法
這篇文章主要介紹了bitronix 連接 MySQL 出現(xiàn)MySQLSyntaxErrorException 的解決方法的相關(guān)資料,需要的朋友可以參考下2017-04-04