MySQL數(shù)據(jù)庫應(yīng)用與開發(fā)技術(shù) 課件 第08章-觸發(fā)器

MySQL數(shù)據(jù)庫應(yīng)用與開發(fā)技術(shù)第8章觸發(fā)器本單元教學(xué)目標(biāo)認(rèn)識、了解觸發(fā)器的作用理解觸發(fā)器的原理、過程掌握觸發(fā)器的開發(fā)語法理解觸發(fā)器的概念3觸發(fā)器概念觸發(fā)器是一種特殊的存儲過程在插入、刪除、修改特定表中數(shù)據(jù)時觸發(fā)執(zhí)行擁有比數(shù)據(jù)庫本身更強(qiáng)大的數(shù)據(jù)控制能力4觸發(fā)器作用安全性可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利#可以基于時間限制用戶的操作例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫數(shù)據(jù)#可以基于數(shù)據(jù)庫中的數(shù)據(jù)限制用戶的操作例如不允許股票的價格的升幅一次超過10%數(shù)據(jù)審計可以跟蹤用戶對數(shù)據(jù)庫的操作#審計用戶操作數(shù)據(jù)庫的語句#把用戶對數(shù)據(jù)庫的更新寫入審計表5觸發(fā)器作用數(shù)據(jù)約束實(shí)現(xiàn)數(shù)據(jù)完整性檢查和約束例如回退任何企圖買進(jìn)超過自己資金的貨物提供可變的缺省值連環(huán)更新修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行修改或刪除時把其它表中與之匹配的行設(shè)成NULL值修改或刪除時把其它表中與之匹配的行級聯(lián)設(shè)成缺省值6觸發(fā)器原理使用臨時表使用臨時表備份之前的數(shù)據(jù)逐行檢查對每一行數(shù)據(jù)逐一進(jìn)行檢查以上兩點(diǎn)大大增加了系統(tǒng)開銷一般不能隨意使用觸發(fā)器7創(chuàng)建觸發(fā)器創(chuàng)建語法CREATETRIGGER+觸發(fā)器名稱

+觸發(fā)時間點(diǎn)

+觸發(fā)事件

+ON+表名

+FOREACHROWBEGIN…END觸發(fā)時間點(diǎn)：BEFORE或AFTER指明是在觸發(fā)事件之前還是之后執(zhí)行觸發(fā)事件：INSERT、UPDATE、DELETE事件如：CREATETRIGGERupd_check

BEFOREUPDATEONaccountFOREACHROWBEGIN…END8刪除觸發(fā)器刪除語法DROPTRIGGER+觸發(fā)器名稱如：DROPTRIGGER

upd_check9觸發(fā)器案例｜建表--創(chuàng)建表格：tab1DROPTABLEIFEXISTStab1;CREATETABLEtab1(tab1_idvarchar(11));--創(chuàng)建表格：tab2DROPTABLEIFEXISTStab2;CREATETABLEtab2(tab2_idvarchar(11));

10第一個觸發(fā)器--功能：往tab1表添加記錄后自動將此記錄增加到tab2表中DELIMITER//DROPTRIGGERIFEXISTSt_afterinsert_on_tab1;CREATETRIGGER

t_afterinsert_on_tab1

AFTER

INSERT

ON

tab1

FOREACHROW

BEGIN

insertintotab2(tab2_id)values(new.tab1_id);

END;//DELIMITER;

11觸發(fā)器測試往tab1插入記錄INSERTINTOtab1(tab1_id)values('0001')檢索tab1、tab2數(shù)據(jù)SELECT*FROMtab1SELECT*FROMtab212課程練習(xí)1創(chuàng)建觸發(fā)器:t_afterdelete_on_tab1功能作用：刪除tab1表記錄后自動將tab2表中對應(yīng)的記錄刪去13參考代碼14課程練習(xí)1｜補(bǔ)充選做創(chuàng)建觸發(fā)器:t_afterupdate_on_tab1功能作用：更新tab1表記錄后自動將tab2表中對應(yīng)的記錄更新15參考代碼16new與oldold：代表操作執(zhí)行前的數(shù)據(jù)行new：代表操作執(zhí)行后的數(shù)據(jù)行若要執(zhí)行如下語句：updateUsersetscore=80whereuser_id=1則old表示未執(zhí)行update語句前user_id=1這行記錄則new表示執(zhí)行update語句后user_id=1這行記錄old.字段名=未執(zhí)行操作前的該行對應(yīng)的某字段值new.字段名=執(zhí)行操作后的該行對應(yīng)的某字段值old.score=60new.score=8017new與oldnew賦值：只能在before中使用，不能在after中使用更新操作前使用before先賦值，再插入到數(shù)據(jù)庫中如，以下語句是正確的：CREATETRIGGERupdateprice

BEFOREinsertONconsumeinfoFOREACHROWBEGINsetnew.金額=0;END;在after中：new賦值已經(jīng)結(jié)束，只能讀取內(nèi)容如，以下語句是錯誤的：CEATETRIGGERupdateprice

AFTERinsertONconsumeinfoFOREACHROWBEGINsetnew.金額=0;END;18new與old總結(jié)歸納：new可在before觸發(fā)器中賦值、取值可在after觸發(fā)器中取值old只能用于取值，因?yàn)橘x值沒意義INSERT語句，只有new合法DELETE語句，只有old才合法UPDATE語句，可以同時使用new和old19課程練習(xí)2創(chuàng)建觸發(fā)器:upd_check

功能作用：修改Account表中的記錄的amount字段時，作如下檢查：如果修改后的值大于100，則值為100如果修改后的值小于0，則值為0如果修改后的值為舊值2倍以上，則值為舊值2倍20建表腳本CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`account`;CREATETABLE`account`(`id`int(10)unsignedNOTNULLauto_increment,`user`varchar(45)NOTNULL,`amount`int(11)NOTNULLdefault'0',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8;INSERTINTO`account`(`id`,`user`,`amount`)VALUES(1,'李知龍',55),(2,'盧中明',80),(3,'喬大平',30);21參考代碼22After與beforeafter和before的區(qū)別：after是先完成業(yè)務(wù)數(shù)據(jù)的增刪改，再觸發(fā)觸發(fā)的語句晚于監(jiān)視的業(yè)務(wù)語句無法影響前面的增刪改動作也就是說先執(zhí)行業(yè)務(wù)操作，再做觸發(fā)操作before是先完成觸發(fā)操作，再執(zhí)行業(yè)務(wù)數(shù)據(jù)的增刪改觸發(fā)的語句先于監(jiān)視的業(yè)務(wù)語句我們就有機(jī)會影響即將發(fā)生的操作23單元作業(yè)1有商品(g)和訂單(o)兩張表：假設(shè)賣出某種商品若干數(shù)量，我們需要做兩件事1.往訂單表插入一條對應(yīng)記錄2.更新商品表中此種商品的剩余數(shù)量（原數(shù)量-對應(yīng)的銷售數(shù)量）寫一個觸發(fā)器實(shí)現(xiàn)自動級聯(lián)兩張表功能，即：當(dāng)往訂單表(o)插入某種商品的銷售記錄時，自動更新商品表(g)的此種商品剩余數(shù)量（原數(shù)量-對應(yīng)的銷售數(shù)量）24數(shù)據(jù)庫環(huán)境腳本--商品表DROPTABLEIFEXISTS`g`;CREATETABLE`g`(`id`int(10)unsignedNOTNULLauto_increment,`name`varchar(45)defaultNULL,`num`int(11)defaultNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;--訂單表DROPTABLEIFEXISTS`o`;CREATETABLE`o`(`oid`int(10)unsignedNOTNULLauto_increment,`gid`int(11)NOTNULL,`much`int(11)NOTNULL,PRIMARYKEY(`oid`))ENGINE=InnoDBDEFAULTCHARSET=utf8;insertintog(name,num)values