./實(shí)驗(yàn)六存儲(chǔ)過(guò)程和觸發(fā)器一、目的與要求掌握編寫(xiě)數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的方法。掌握建立數(shù)據(jù)庫(kù)觸發(fā)器的方法,通過(guò)實(shí)驗(yàn)觀察觸發(fā)器的作用和觸發(fā)條件設(shè)置等相關(guān)操作。二、實(shí)驗(yàn)準(zhǔn)備了解編寫(xiě)存儲(chǔ)過(guò)程和調(diào)用的T-SQL語(yǔ)法；了解觸發(fā)器的作用；了解編寫(xiě)觸發(fā)器的T-SQL語(yǔ)法。三、實(shí)驗(yàn)容存儲(chǔ)過(guò)程在studentdb數(shù)據(jù)庫(kù)中建立存儲(chǔ)過(guò)程getPractice,查詢指定院系<名稱><作為存儲(chǔ)過(guò)程的輸入?yún)?shù)>中參與"實(shí)踐"課程學(xué)習(xí)的所有學(xué)生學(xué)號(hào)、、所學(xué)課程編號(hào)和課程名稱,若院系不存在,返回提示信息。提示：D_Info表中存儲(chǔ)了院系代碼D_ID,而St_Info表中學(xué)號(hào)字段St_ID的前兩位與之對(duì)應(yīng),則D_Info表與St_Info表之間的聯(lián)系通過(guò)這兩個(gè)字段的運(yùn)算構(gòu)成連接條件。分別執(zhí)行存儲(chǔ)過(guò)程getPractice,查詢"法學(xué)院"和"材料科學(xué)與工程學(xué)院"的學(xué)生中參與"實(shí)踐"課程的所有學(xué)生學(xué)號(hào)、、所學(xué)課程編號(hào)和課程名稱。createproceduregetPracticeD_Namevarchar<30>outputasbeginifnotexists<select*fromD_InfowhereD_Name=D_Name>print'對(duì)不起,該院系不存在'elseselectst_info.St_ID,C_Info.C_No,C_Namefroms_c_infoinnerjoinst_infoonst_info.St_ID=s_c_info.st_idinnerjoinC_Infoons_c_info.c_no=C_Info.C_Nowherest_info.St_IDin<selectSt_IDfromst_infojoinD_InfoonD_Info.D_ID=left<st_info.St_ID,2>whereC_Info.C_Type='實(shí)踐'andD_Info.D_Name=D_Name>endgo利用系統(tǒng)存儲(chǔ)過(guò)程sp_rename將getPractice更名為getPctStuexecutesp_renamegetPractice,getPctStu修改存儲(chǔ)過(guò)程getPctStu,返回指定院系中參與實(shí)踐課程的學(xué)生人次數(shù),并利用該存儲(chǔ)過(guò)程以"法學(xué)院"為輸入?yún)?shù)驗(yàn)證執(zhí)行的結(jié)果alterproceduregetPctStuD_Namevarchar<30>asbeginifnotexists<select*fromD_InfowhereD_Name=D_Name>print'對(duì)不起,該院系不存在'elseselectst_info.St_ID,C_Info.C_No,C_Name,count<st_info.St_ID>as人次數(shù)froms_c_infoinnerjoinst_infoonst_info.St_ID=s_c_info.st_idinnerjoinC_Infoons_c_info.c_no=C_Info.C_Nowherest_info.St_IDin<selectSt_IDfromst_infojoinD_InfoonD_Info.D_ID=left<st_info.St_ID,2>whereC_Info.C_Type='實(shí)踐'andD_Info.D_Name=D_Name>groupbyst_info.St_ID,C_Info.C_No,C_NameendGoexecgetPctStu'法學(xué)院'Go再修改存儲(chǔ)過(guò)程getPctStu,返回指定院系中參與實(shí)踐課程的學(xué)生人數(shù)。注："人數(shù)"和"人次數(shù)"是不同的,對(duì)某一學(xué)生而言,如果參與了多門(mén)實(shí)踐課程,則"人次數(shù)"是指其參與的課程門(mén)數(shù),而"人數(shù)"仍為1。觸發(fā)器1.在studentdb數(shù)據(jù)庫(kù)中建立一個(gè)具有審計(jì)功能的觸發(fā)器：觸發(fā)器名為tr_sc,功能要求：審計(jì)在s_c_info表中對(duì)score字段的更新和插入操作,將這些操作記錄到sc_log表中,sc_log表中有如下字段：操作類型type,學(xué)號(hào)st_id,課程號(hào)c_no,舊成績(jī)oldscore,新成績(jī)newscore,操作員uname,操作時(shí)間udate,其中操作員設(shè)定默認(rèn)值為user,操作時(shí)間默認(rèn)值為系統(tǒng)時(shí)間。createtablesc_log<typevarchar<4>,st_idvarchar<10>,c_novarchar<10>,oldscoreint,newscoreint,unamevarchar<10>defaultuser,udatadatetimedefaultgetdate<>>gocreatetriggertr_scons_c_infoforinsert,updateasifupdate<score>beginif<selectcount<*>fromdeleted><>0insertintosc_log<type,st_id,c_no,oldscore,newscore>select'update',s_c_info.st_id,s_c_info.c_no,i.score,d.scorefroms_c_info,insertedi,deleteddwheres_c_info.st_id=i.st_idandi.st_id=d.st_idelseinsertintosc_log<type,st_id,c_no,newscore>select'insert',st_id,c_no,scorefrominsertedendgo在s_c_info表上建立一個(gè)觸發(fā)器tr_updasc,用于監(jiān)控對(duì)成績(jī)的更新,要求更新后的成績(jī)不能比更新前低,如果新成績(jī)低則取消操作,給出提示信息,否則允許更新。createtriggertr_updascons_c_infoafterupdateasdeclarecj1int,cj2intselectcj1=deleted.scorefromdeletedselectcj2=inserted.scorefrominsertedif<cj2<cj1>beginraiserror<'新成績(jī)比老成績(jī)低,取消操作',16,1>rollbacktransactionendgo<三>查看存儲(chǔ)過(guò)程和觸發(fā)器的信息1.用sp_help查看以上建立的存儲(chǔ)過(guò)程和觸發(fā)器的基本信息sp_helptr_scgosp_helptr_updascgo2.用sp_helptext查看存儲(chǔ)過(guò)程和觸發(fā)器的代碼sp_helptexttr_scgosp_helptexttr_updascgo四、思考與練習(xí)1．存儲(chǔ)過(guò)程如何加密？Createprocedureencrypt_thisWithencryption--加密,在存儲(chǔ)過(guò)程添加這一語(yǔ)句即可ASSelect*fromuser_tbGO查看存儲(chǔ)過(guò)程儲(chǔ)存的文本：Execsp_helptextencrypt_this下面是結(jié)果集：Theproject’scommentshavebeenencrypted.--對(duì)象已經(jīng)被加密2．觸發(fā)器有什么好處和壞處？使用觸發(fā)器的好處：1.自動(dòng)執(zhí)行。觸發(fā)器在對(duì)表的數(shù)據(jù)作了任何修改〔比如手工輸入或者應(yīng)用程序的操作之后立即被激活。2.級(jí)聯(lián)更新。觸發(fā)器可以通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表進(jìn)行層疊更改,這比直接把代碼寫(xiě)在前臺(tái)的做法更安全合理。3.強(qiáng)化約束。觸發(fā)器可以引用其它表中的列,能夠?qū)崿F(xiàn)比CHECK約束更為復(fù)雜的約束。4.跟蹤變化。觸發(fā)器可