實驗六-存儲過程和觸發(fā)器

實驗六存儲過程和觸發(fā)器一、目的與要求掌握編寫數(shù)據(jù)庫存儲過程的方法。掌握建立數(shù)據(jù)庫觸發(fā)器的方法，通過實驗觀察觸發(fā)器的作用和觸發(fā)條件設置等相關操作。二、實驗準備了解編寫存儲過程和調用的T-SQL語法；了解觸發(fā)器的作用；了解編寫觸發(fā)器的T-SQL語法。三、實驗內容存儲過程在studentdb數(shù)據(jù)庫中建立存儲過程getPractice，查詢指定院系(名稱)(作為存儲過程的輸入?yún)?shù))中參與“實踐”課程學習的所有學生學號、姓名、所學課程編號和課程名稱，若院系不存在，返回提示信息。提示：D_Info表中存儲了院系代碼D_ID，而St_Info表中學號字段St_ID的前兩位與之對應，則D_Info表與St_Info表之間的聯(lián)系通過這兩個字段的運算構成連接條件。分別執(zhí)行存儲過程getPractice，查詢“法學院”和“材料科學與工程學院”的學生中參與“實踐”課程的所有學生學號、姓名、所學課程編號和課程名稱。createproceduregetPractice@D_Namevarchar(30)outputasbeginifnotexists(select*fromD_InfowhereD_Name=@D_Name)print'對不起，該院系不存在'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='實踐'andD_Info.D_Name=@D_Name)endgooldscoreint,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表上建立一個觸發(fā)器tr_updasc，用于監(jiān)控對成績的更新，要求更新后的成績不能比更新前低，如果新成績低則取消操作，給出提示信息，否則允許更新。createtriggertr_updascons_c_infoafterupdateasdeclare@cj1int,@cj2intselect@cj1=deleted.scorefromdeletedselect@cj2=inserted.scorefrominsertedif(@cj2<@cj1)beginraiserror('新成績比老成績低，取消操作',16,1)rollbacktransactionendgo(三)查看存儲過程和觸發(fā)器的信息1.用sp_help查看以上建立的存儲過程和觸發(fā)器的基本信息sp_helptr_scgosp_helptr_updascgo2.用sp_helptext查看存儲過程和觸發(fā)器的代碼sp_helptexttr_scgosp_helptexttr_updascgo四、思考與練習1．存儲過程如何加密？Createprocedureencrypt_thisWithencryption--加密,在存儲過程添加這一語句即可ASSelect*fromuser_tbGO查看存儲過程儲存的文本：Execsp_helptextencrypt_this下面是結果集：Theproject’scommentshavebeenencrypted.--對象已經(jīng)被加密2．觸發(fā)器有什么好處和壞處？使用觸發(fā)器的好處：1.自動執(zhí)行。觸發(fā)器在對表的數(shù)據(jù)作了任何修改（比如手工輸入或者應用程序的操作）之后立即被激活。2.級聯(lián)更新。觸發(fā)器可以通過數(shù)據(jù)庫中的相關表進行層疊更改，這比直接把代碼寫在前臺的做法更安全合理。3.強化約束。觸發(fā)器可以引用其它表中的列，能夠實現(xiàn)比CHECK約束更為復雜的約束。4.跟蹤變化。觸發(fā)器可以阻止數(shù)據(jù)庫中未經(jīng)許可的指定更新和變化。5.強制業(yè)務邏輯。觸發(fā)器可用于執(zhí)行管理任務，并強制影響數(shù)據(jù)庫的