MySQL觸發(fā)器語法詳解:
觸發(fā)器 trigger是一種特殊的存儲(chǔ)過程,他在插入(inset)、刪除(delete)或修改(update)特定表中的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行,它比數(shù)據(jù)本身標(biāo)準(zhǔn)的功能更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。觸發(fā)器不是由程序調(diào)用,而是由某個(gè)事件來觸發(fā)的。在有數(shù)據(jù)修改時(shí)自動(dòng)強(qiáng)制執(zhí)行其業(yè)務(wù)規(guī)則,經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。觸發(fā)器可以查詢其他表,而且包含復(fù)制的sql語句。觸發(fā)器也可用于強(qiáng)制引用完整性。觸發(fā)器可以強(qiáng)制比用check約束定義的約束更為復(fù)雜的約束。
(一).CREATE TRIGGER語法
CREATE TRIGGER trigger_nametrigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt;
觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對(duì)象,當(dāng)表上出現(xiàn)特定事件時(shí),將激活該對(duì)象。
觸發(fā)程序與命名為tbl_name的表相關(guān)。tbl_name必須引用永久性表。不能將觸發(fā)程序與TEMPORARY表或視圖關(guān)聯(lián)起來。
trigger_time是觸發(fā)程序的動(dòng)作時(shí)間。它可以是BEFORE或AFTER,以指明觸發(fā)程序是在激活它的語句之前或之后觸發(fā)。
trigger_event指明了激活觸發(fā)程序的語句的類型。trigger_event可以是下述值之一:
(1).INSERT:將新行插入表時(shí)激活觸發(fā)程序,例如,通過INSERT、LOAD DATA和REPLACE
語句。
(2).UPDATE:更改某一行時(shí)激活觸發(fā)程序,例如,通過UPDATE語句。
(3).DELETE:從表中刪除某一行時(shí)激活觸發(fā)程序,例如,通過DELETE和REPLACE語句。
請(qǐng)注意,trigger_event與以表操作方式激活觸發(fā)程序的SQL語句并不很類似,這點(diǎn)很重要。例如,關(guān)于INSERT的BEFORE觸發(fā)程序不僅能被INSERT語句激活,也能被LOAD DATA語句激活??赡軙?huì)造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE ...語法:BEFORE INSERT觸發(fā)程序?qū)τ诿恳恍袑⒓せ?,后跟AFTER INSERT觸發(fā)程序,或BEFORE UPDATE和AFTER UPDATE觸發(fā)程序,具體情況取決于行上是否有重復(fù)鍵。
對(duì)于具有相同觸發(fā)程序動(dòng)作時(shí)間和事件的給定表,不能有兩個(gè)觸發(fā)程序。例如,對(duì)于某一表,不能有兩個(gè)BEFORE UPDATE觸發(fā)程序。但可以有1個(gè)BEFORE UPDATE觸發(fā)程序和1個(gè)BEFORE INSERT觸發(fā)程序,或1個(gè)BEFOREUPDATE觸發(fā)程序和1個(gè)AFTER UPDATE觸發(fā)程序。trigger_stmt是當(dāng)觸發(fā)程序激活時(shí)執(zhí)行的語句。如果你打算執(zhí)行多個(gè)語句,可使用BEGIN ... END復(fù)合語句結(jié)構(gòu)。這樣,就能使用存儲(chǔ)子程序中允許的相同語句
(二).DROP TRIGGER語法
DROP TRIGGER[schema_name.]trigger_name舍棄觸發(fā)程序。方案名稱(schema_name)是可選的。如果省略了schema(方案),將從當(dāng)前方案中舍棄觸發(fā)程序。
注釋:從MySQL 5.0.10之前的MySQL版本升級(jí)到5.0.10或更高版本時(shí)(包括所有的MySQL5.1版本),必須在升級(jí)之前舍棄所有的觸發(fā)程序,并在隨后重新創(chuàng)建它們,否則,在升級(jí)之后DROP TRIGGER不工作。DROP TRIGGER語句需要SUPER權(quán)限。
(三).使用觸發(fā)程序
在本節(jié)中,介紹了在MySQL 5.1中使用觸發(fā)程序的方法,并介紹了在使用觸發(fā)程序方面的限制。
觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對(duì)象,當(dāng)表上出現(xiàn)特定事件時(shí),將激活該對(duì)象。在某些觸發(fā)程序的用法中,可用于檢查插入到表中的值,或?qū)Ω律婕暗闹颠M(jìn)行計(jì)算。
觸發(fā)程序與表相關(guān),當(dāng)對(duì)表執(zhí)行INSERT、DELETE或UPDATE語句時(shí),將激活觸發(fā)程序。可以將觸發(fā)程序設(shè)置為在執(zhí)行語句之前或之后激活。例如,可以在從表中刪除每一行之前,或在更新了,每一行后激活觸發(fā)程序。要想創(chuàng)建觸發(fā)程序或舍棄觸發(fā)程序,可使用CREATE TRIGGER或DROP TRIGGER語句.觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲(chǔ)程序,也不能使用采用CALL語句的動(dòng)態(tài)SQL(允許存儲(chǔ)程序通過參數(shù)將數(shù)據(jù)返回觸發(fā)程序)。
觸發(fā)程序不能使用以顯式或隱式方式開始或結(jié)束事務(wù)的語句,如START TRANSACTION、
COMMIT或ROLLBACK。
使用OLD和NEW關(guān)鍵字,能夠訪問受觸發(fā)程序影響的行中的列(OLD和NEW不區(qū)分大小寫)。
在INSERT觸發(fā)程序中,僅能使用NEW.col_name,沒有舊行。在DELETE觸發(fā)程序中,僅能使用OLD.col_name,沒有新行。在UPDATE觸發(fā)程序中,可以使用OLD.col_name來引用更新前的某一行的列,也能使用NEW.col_name來引用更新后的行中的列。
用OLD命名的列是只讀的。你可以引用它,但不能更改它。對(duì)于用NEW命名的列,如果具有SELECT權(quán)限,可引用它。在BEFORE觸發(fā)程序中,如果你具有UPDATE權(quán)限,可使用“SET NEW.col_name = value”更改它的值。這意味著,你可以使用觸發(fā)程序來更改將要插入到新行中的值,或用于更新行的值。在BEFORE觸發(fā)程序中,AUTO_INCREMENT列的NEW值為0,不是實(shí)際插入新記錄時(shí)將自動(dòng)生成的序列號(hào)。
通過使用BEGIN ...END結(jié)構(gòu),能夠定義執(zhí)行多條語句的觸發(fā)程序。在BEGIN塊中,還能使用存儲(chǔ)子程序中允許的其他語法,如條件和循環(huán)等。但是,正如存儲(chǔ)子程序那樣,定義執(zhí)行多條語句的觸發(fā)程序時(shí),如果使用mysql程序來輸入觸發(fā)程序,需要重新定義語句分隔符,以便能夠在觸發(fā)程序定義中使用字符“;”。在下面的示例中,演示了這些要點(diǎn)。在該示例中,定義了1個(gè)UPDATE觸發(fā)程序,用于檢查更新每一行時(shí)將使用的新值,并更改值,使之位于0~100的范圍內(nèi)。它必須是BEFORE觸發(fā)程序,這是因?yàn)椋枰趯⒅涤糜诟滦兄皩?duì)其進(jìn)行檢查:
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 ;
較為簡(jiǎn)單的方法是,單獨(dú)定義存儲(chǔ)程序,然后使用簡(jiǎn)單的CALL語句從觸發(fā)程序調(diào)用存儲(chǔ)程序。如果你打算從數(shù)個(gè)觸發(fā)程序內(nèi)部調(diào)用相同的子程序,該方法也很有幫助。在觸發(fā)程序的執(zhí)行過程中,MySQL處理錯(cuò)誤的方式如下:
(1)如果BEFORE觸發(fā)程序失敗,不執(zhí)行相應(yīng)行上的操作。
(2)僅當(dāng)BEFORE觸發(fā)程序(如果有的話)和行操作均已成功執(zhí)行,才執(zhí)行AFTER觸發(fā)程序。
(3) 如果在BEFORE或AFTER觸發(fā)程序的執(zhí)行過程中出現(xiàn)錯(cuò)誤,將導(dǎo)致調(diào)用觸發(fā)程序的整個(gè)語句的失敗。
(4)對(duì)于事務(wù)性表,如果觸發(fā)程序失?。ㄒ约坝纱藢?dǎo)致的整個(gè)語句的失?。?,該語句所執(zhí)行的所有更改將回滾。對(duì)于非事務(wù)性表,不能執(zhí)行這類回滾,因而,即使語句失敗,失敗之前所作的任何更改依然有效。
例一:
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
感謝閱讀,希望能幫助到大家,謝謝大家對(duì)本站的支持!
您可能感興趣的文章:- MySQL觸發(fā)器的使用場(chǎng)景及方法實(shí)例
- mysql觸發(fā)器原理與用法實(shí)例分析
- mysql觸發(fā)器之創(chuàng)建多個(gè)觸發(fā)器操作實(shí)例分析
- MySQL 觸發(fā)器定義與用法簡(jiǎn)單實(shí)例
- MySQL通過觸發(fā)器解決數(shù)據(jù)庫中表的行數(shù)限制詳解及實(shí)例
- MySQL 觸發(fā)器詳解及簡(jiǎn)單實(shí)例
- MySQL觸發(fā)器運(yùn)用于遷移和同步數(shù)據(jù)的實(shí)例教程
- mysql觸發(fā)器(Trigger)簡(jiǎn)明總結(jié)和使用實(shí)例
- MySQL中觸發(fā)器入門簡(jiǎn)單實(shí)例與介紹
- mysql觸發(fā)器trigger實(shí)例詳解