《MySQL數(shù)據(jù)庫(kù)應(yīng)用與管理項(xiàng)目化教程》課件-項(xiàng)目八-1 觸發(fā)器

項(xiàng)目八觸發(fā)器任務(wù)目標(biāo)任務(wù)一創(chuàng)建觸發(fā)器任務(wù)二應(yīng)用觸發(fā)器任務(wù)三 查看觸發(fā)器任務(wù)四 刪除觸發(fā)器教學(xué)目標(biāo)能力目標(biāo)◎能熟練查看觸發(fā)器信息，按需建立和修改觸發(fā)器；◎能應(yīng)用觸發(fā)器解決復(fù)雜的業(yè)務(wù)邏輯關(guān)聯(lián)。知識(shí)目標(biāo)◎了解觸發(fā)器的用途和類型，熟悉觸發(fā)器創(chuàng)建語(yǔ)法；◎掌握觸發(fā)器中的NEW和OLD的應(yīng)用。學(xué)習(xí)重點(diǎn)◎掌握觸發(fā)器的查看、建立和測(cè)試；◎掌握NEW和OLD的應(yīng)用。學(xué)習(xí)難點(diǎn)◎觸發(fā)器的級(jí)聯(lián)觸發(fā)與測(cè)試。任務(wù)一創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器

主要內(nèi)容:觸發(fā)器作用創(chuàng)建觸發(fā)器測(cè)試觸發(fā)器重點(diǎn)難點(diǎn):觸發(fā)器應(yīng)用；觸發(fā)器作用引入：商品銷售會(huì)使商品銷售量增加，而庫(kù)存量必須相應(yīng)地減少相同數(shù)量，通常為了實(shí)現(xiàn)這樣的強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，我們把這種業(yè)務(wù)規(guī)則編寫成觸發(fā)器，通過(guò)觸發(fā)器來(lái)實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則的觸發(fā)動(dòng)作。觸發(fā)器觸發(fā)器是一種與數(shù)據(jù)表緊密結(jié)合的特殊的存儲(chǔ)過(guò)程，用于保護(hù)表中的數(shù)據(jù)。當(dāng)該數(shù)據(jù)表有插入、更改或刪除事件發(fā)生時(shí)，所設(shè)置的相應(yīng)觸發(fā)器的觸發(fā)操作也會(huì)自動(dòng)執(zhí)行。檢查數(shù)據(jù)變動(dòng)的一致性:在銷售圖書時(shí)，為保證圖書資料表和銷售銷售表的圖書信息變動(dòng)一致，可通過(guò)建立一個(gè)銷售銷售表上建立UPDATE和INSERT觸發(fā)器，可以使用關(guān)鍵字old和new來(lái)引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容。創(chuàng)建觸發(fā)器查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/create-trigger.html語(yǔ)法：CREATETRIGGERtrigger_name

{BEFORE|AFTER}

{INSERT|UPDATE|DELETE}ONtbl_nameFOREACHROW[{FOLLOWS|PRECEDES}other_trigger_name]trigger_body創(chuàng)建觸發(fā)器關(guān)鍵字：NEW和OLD觸發(fā)器中使用的NEW和OLD，用來(lái)表示觸發(fā)器的所在表中，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)。使用方法：NEW.col_nameOLD.col_nameOLD是只讀的，而NEW則可以在觸發(fā)器中使用SET賦值，這樣不會(huì)再次觸發(fā)觸發(fā)器，造成循環(huán)調(diào)用。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ù)；AFTER觸發(fā)器應(yīng)用例1：建立一個(gè)item表的插入觸發(fā)器mysql>DROPTRIGGERIFEXISTStg_goods_ins;mysql>delimiter//mysql>CREATETRIGGERtg_goods_ins

AFTERINSERTONitem

FOREACHROW

BEGIN

UPDATEgoods

SETamount=amount-NEW.quantity

WHEREid=NEW.goods_id;END//mysql>delimiter;mysql>SELECT*FROMitem;mysql>INSERTINTOitem(order_id,goods_id,supplier_id,quantity)VALUES(2,2,1,10);#測(cè)試插入觸發(fā)器mysql>SELECT*FROMitem;mysql>SELECT*FROMgoods;AFTER觸發(fā)器應(yīng)用例2：建立一個(gè)item表的修改觸發(fā)器mysql>DROPTRIGGERIFEXISTStg_goods_mod;mysql>delimiter//mysql>CREATETRIGGERtg_goods_mod AFTERUPDATEONitemFOREACHROWBEGINUPDATEgoodsSETamount=amount+(old.quantity-NEW.quantity)WHEREgoods_id=NEW.goods_id;END//mysql>delimiter;mysql>UPDATEitemSETquantity=quantity-5#測(cè)試修改觸發(fā)器WHEREitem_id=6;mysql>SELECT*FROMitem;mysql>SELECT*FROMgoods;AFTER觸發(fā)器應(yīng)用例3：建立一個(gè)item表的刪除觸發(fā)器mysql>DROPTRIGGERIFEXISTStg_goods_del;mysql>delimiter//mysql>CREATETRIGGERtg_goods_delAFTERDELETEONitemFOREACHROWBEGINUPDATEgoodsSETamount=amount+old.quantityWHEREgoods_id=old.goods_id;END//mysql>delimiter;mysql>SELECT*FROMitem;mysql>SELECT*FROMgoods;mysql>DELETEFROMitemWHEREitem_id=6;BEFORE觸發(fā)器應(yīng)用例4：建立一個(gè)item表的BEFORE觸發(fā)器mysql>SELECT*FROMorders;mysql>DROPTRIGGERIFEXISTStg_goods_ins2;mysql>delimiter//mysql>CREATETRIGGERtg_goods_ins2BEFOREINSERTONitemFOREACHROWBEGINIFNOTEXISTS(SELECT*FROMgoodsWHEREgoods_id=NEW.goods_idANDamount>NEW.quantity)THENBEGIN SIGNALSQLSTATE'H1001'SETMESSAGE_TEXT='庫(kù)存不足';END;ELSEUPDATEgoodsSETamount=amount-NEW.quantityWHEREgoods_id=NEW.goods_id;ENDIF;END//mysql>delimiter;mysql>SELECT*FROMgoods;mysql>INSERTINTOitem(order_id,goods_id,supplier_id,quantity)VALUES(2,2,1,150);#測(cè)試插入觸發(fā)器mysql>SELECT*FROMitem;任務(wù)實(shí)施按下列操作步驟完成db_shopping數(shù)據(jù)庫(kù)觸發(fā)器的建立和測(cè)試。1.選擇db_shop數(shù)據(jù)庫(kù)，執(zhí)行語(yǔ)句如下：mysql>USEdb_shop;2.修改前面建立的tg_customer，當(dāng)在訂購(gòu)表修改一條訂購(gòu)單時(shí)，若訂購(gòu)額大于0，則自動(dòng)返還訂購(gòu)增加金額整除100的金額數(shù)到會(huì)員的余額中，執(zhí)行語(yǔ)句如下：mysql>delimiter//mysql>CREATETRIGGertg_customerAFTERUPDATEONordersFOREACHROWBEGINDECLARENmdecimal(11,2);DECLAREOmdecimal(11,2);SETNm=NEW.amount;SETOm=OLD.amount;IFNm-Om>0THENUPDATEcustomerSETmenber_balance=menber_balance+(Nm-Om)/100WHEREid=NEW.customer_id;ENDIF;END//mysql>delimiter;3、測(cè)試觸發(fā)器，執(zhí)行語(yǔ)句如下：mysql>UPDATEordersSETamount=(SELECTSUM(total_price)FROMitemWHEREorder_id=4)WHEREid=4;查看觸發(fā)器查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/show.html使用定義和狀態(tài):SHOWTRIGGERS語(yǔ)句語(yǔ)法：SHOWTRIGGERS[{FROM|IN}db_name][LIKE'pattern'|WHEREexpr]mysql>SHOWTRIGGERSmysql>SHOWTRIGGERSFROMdb_shopLIKE'item%';查看觸發(fā)器查看定義：SHOWCREATE語(yǔ)句mysql>SHOWCREATETRIGGERtrigger_name；查看tringgers表中的觸發(fā)器信息語(yǔ)法：SELECT*FROMinformation_schema.triggers[WHEREtrigger_name='tri_name'];mysql>SELECT*FROMinformation_schema.triggers任務(wù)實(shí)施按下列操作完成db_shop數(shù)據(jù)庫(kù)中觸發(fā)器查看，從而了解觸發(fā)器作用和定義。1.選擇db_shop數(shù)據(jù)庫(kù)，執(zhí)行語(yǔ)句如下：mysql>USEdb_shop;2.查看系統(tǒng)中所有觸發(fā)器，執(zhí)行語(yǔ)句如下：mysql>SELECT*FROMinformation_schema.triggers\G3.查看當(dāng)前數(shù)據(jù)庫(kù)指定觸發(fā)器的定義腳本，執(zhí)行語(yǔ)句如下：mysql>SHOWCREATETRIGGERtg_goods_del;刪除觸發(fā)器語(yǔ)法：DROPTRIGGER[IFEXISTS][schema_name.]trigger_namemysql>DROPTRIGGERtest_db.tg3;考考你對(duì)item表，分別建立相應(yīng)的插入、刪除、修改觸發(fā)器，聯(lián)動(dòng)修改goods中的商品數(shù)量（注：goods中沒有商品數(shù)量，請(qǐng)?zhí)砑哟俗侄危?duì)上面的觸發(fā)器進(jìn)行測(cè)試；任務(wù)實(shí)施按下列操作完成db_shopping數(shù)據(jù)庫(kù)觸發(fā)器的刪除修改。1.選擇db_shopping數(shù)據(jù)庫(kù)，執(zhí)行語(yǔ)句如下：mysql>USEdb_shopping;2.修改前面建立的tg_customer，當(dāng)在訂購(gòu)表修改一條訂購(gòu)單時(shí)，按增減金額整除100的金額數(shù)更新到會(huì)員的余額，執(zhí)行語(yǔ)句如下：mysql>delimiter//mysql>DROPTRIGGERIFEXISTStg_customer;mysql>CREATETRIGGertg_customerAFTERUPDATEONordersFOREACHROWBEGINDECLARENmdecimal(11,2);DECLAREOmdecimal(11,2);SETNm=NEW.amount;SE