實驗七觸發(fā)器與事務(wù)設(shè)計

1、實驗七,觸發(fā)器與事務(wù)設(shè)計,【實驗?zāi)康摹?掌握觸發(fā)器的創(chuàng)建、修改、刪除及其使用方法掌握觸發(fā)器的功能理解和掌握事務(wù)的概念、特性以及事務(wù)的設(shè)計思想學(xué)習(xí)和掌握事務(wù)創(chuàng)建、執(zhí)行的方法,【知識要點】,觸發(fā)器是一種特殊類型的存儲過程，它也是用Transact-SQL語言編寫的程序。觸發(fā)器是在用戶要對某一表內(nèi)的數(shù)據(jù)做插入(INSERT)、更新(UPDATE)、刪除(DELETE)時被觸發(fā)執(zhí)行。,【知識要點】,觸發(fā)器的作用可通過DB中的相關(guān)表實

2、現(xiàn)級聯(lián)更改可強(qiáng)制比用CHECK約束定義的約束更為復(fù)雜的約束可引用其他表中的列可執(zhí)行其它操作，如修改數(shù)據(jù)或顯示用戶定義的錯誤信息可包含使用Transact-SQL代碼的復(fù)雜處理邏輯可評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策,【知識要點】,觸發(fā)器的類型UPDATEINSERTDELETE,【知識要點】,創(chuàng)建觸發(fā)器的SQL語句CREATE TRIGGER 觸發(fā)器名 ON { 表名 | 視圖名 }{ {{

3、FOR | AFTER | INSTEAD OF} {[INSERT] [, UPDATE] [, DELETE]} AS [{IF UPDATE (列名) [ {AND | OR} UPDATE(列名) ] [ …n] }]SQL語句 […n]},【知識要點】,使用INSERTED和DELETED表由SQL Server自動創(chuàng)建和管理，并臨時駐留在內(nèi)存中。DELETED表用于存儲D

4、ELETE和UPDATE語句所影響的行的副本INSERTED表用于存儲INSERT和UPDATE語句所影響的行的副本,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_Stu創(chuàng)建一個級聯(lián)更新觸發(fā)器Trigger_Stu要求：若修改jx_Stu表中一名學(xué)生的學(xué)號，則jx_SelCourse表中與該學(xué)生相關(guān)的學(xué)號自動修改。在表jx_Stu上點右鍵，選擇“所有任務(wù)”→“管理觸發(fā)器…”,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表j

5、x_Stu創(chuàng)建一個級聯(lián)更新觸發(fā)器Trigger_Stu創(chuàng)建SQL語句CREATE TRIGGER Trigger_S ON [dbo].[jx_Stu] FOR UPDATE ASIF UPDATE(Sno)BEGINdeclare @sno_new char(2), @sno_old char(2)select @sno_new=sno from insertedselect @sno_old=sno fro

6、m deletedupdate jx_SelCourse set sno=@sno_new where sno=@sno_oldEND,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_Stu創(chuàng)建一個級聯(lián)更新觸發(fā)器Trigger_Stu驗證觸發(fā)器的作用查看表jx_Stu和jx_SelCourse之間是否已創(chuàng)建外鍵參照關(guān)系，若已創(chuàng)建，則刪除。修改表jx_Stu中的記錄值，如將S1改為S0查看表jx_SelCourse，可以

7、看到其中的兩個S1學(xué)號同時自動變成S0,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個限制更新觸發(fā)器Trigger_SC要求：若修改jx_SelCourse表中一條記錄的學(xué)號，則要檢查表jx_Stu中是否存在與該學(xué)號相同的記錄，若存在則不許修改，若不存在則可修改。,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個限制更新觸發(fā)器Trigger_SC創(chuàng)建SQL語句CREAT

8、E TRIGGER Trigger_SC ON [dbo].[jx_SelCourse] FOR UPDATE AS IF UPDATE(Sno)BEGINdeclare @sno_new char(2), @sno_old char(2), @sno_cnt intselect @sno_old=sno from deletedselect @sno_cnt=count(*) from jx_Stu where Sn

9、o=@sno_oldIF @sno_cnt0ROLLBACK TRANSACTIONEND,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個限制更新觸發(fā)器Trigger_SC驗證觸發(fā)器的作用修改表jx_Stu中的記錄值，如將S2改為S0點擊“!”按鈕，注意觀察，修改后的學(xué)號又自動從S1改回S9,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個觸發(fā)器Trigg

10、er_SC_Score要求：當(dāng)插入一條記錄或修改成績時，確保此記錄的成績在0—100分之間,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個觸發(fā)器Trigger_SC_ScoreCREATE TRIGGER Trigger_SC_Score ON [dbo].[jx_SelCourse] FOR INSERT, UPDATE ASdeclare @score_read tinyintSe

11、lect @score_read=Score from insertedif @score_read>=0 and @score_read<=100 BEGINPrint ‘操作完成！’ReturnENDPrint ‘成績超出0—100之間！請重新輸入?！疪OLLBACK TRANSACTION,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個觸發(fā)器Trigger_SC_S

12、core驗證觸發(fā)器的作用INSERT INTO jx_SelCourse VALUES('S1','C5',190)GOINSERT INTO jx_SelCourse VALUES('S1','C5',100)GOUPDATE jx_SelCourse SET Score=130WHERE Sno='S2' AND Cno='C5&#

13、39;GOUPDATE jx_SelCourse set Score=60WHERE Sno='S2' AND Cno='C5'GO,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_Course創(chuàng)建一個級聯(lián)刪除觸發(fā)器Trigger_DC要求：通過課程名從jx_Course表中刪除某課程信息，同時刪除jx_SelCourse表中與此課程相關(guān)的選課記錄。,實驗6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL

14、為數(shù)據(jù)庫表jx_Course創(chuàng)建一個級聯(lián)刪除觸發(fā)器Trigger_DCCREATE TRIGGER Trigger_DC_Score ON [dbo].[jx_Course] FOR DELETE ASdeclare @cno_del char(2)Select @cno_del=cno from deleteddelete from jx_SelCourse where Cno=@cno_del,實驗6.1 創(chuàng)建和

15、執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_Course創(chuàng)建一個級聯(lián)刪除觸發(fā)器Trigger_DC驗證觸發(fā)器的作用DELETE FROM jx_Course WHERE Cno=‘C1’GO,【實驗題】,為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一觸發(fā)器，當(dāng)插入或修改一個記錄時，確保此記錄的成績在0—100之間為教師表jx_Tea創(chuàng)建一觸發(fā)器：男職工年齡不能超過60歲，女職工職稱是“教授”的年齡不能超過60歲，其他女職工年齡不能超過55歲,

16、create trigger Trigger_SC_Score on [dbo].[jx_SelCourse]for insert,updateas declare @score_read tinyint select @score_read=Score from inserted if @score_read>=0 and @score_read=60 delete from

17、 jx_Tea where Tno=@Tno_del else if @Tgender_read='女' and @Tage_read>=60 and @Tprof_read='教授' delete from jx_Tea where Tno=@Tno_del else if @Tgender_read='

18、;女' and @Tage_read>=55 delete from jx_Tea where Tno=@Tno_del ROLLBACK TRANSACTION,【實驗題】,為數(shù)據(jù)庫表jx_Tea創(chuàng)建一觸發(fā)器，當(dāng)職稱從“講師”晉升到“副教授”時，崗位津貼自動增加500元；從“副教授”晉升為“教授”時，崗位津貼自動增加900元。為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一觸發(fā)器：將成績按

19、下列對應(yīng)關(guān)系有分?jǐn)?shù)轉(zhuǎn)換成等級：小于60分：不及格；60~70分：及格；70~80分：中；80~90分：良；90分以上：優(yōu),【知識要點】,事務(wù)模式自動提交事務(wù)每條單獨的SQL語句都是一個事務(wù)顯示事務(wù)每個事務(wù)均以BEGIN TRANSACTION語句顯式開始，以COMMIT或ROLLBACK語句顯式結(jié)束隱性事務(wù)在前一個事務(wù)完成時新事務(wù)隱式啟動，但每個事務(wù)仍以COMMIT或ROLLBACK語句顯式完成,實驗6.2 設(shè)計并執(zhí)行

20、事務(wù),設(shè)計事務(wù)1要求：將課程“數(shù)據(jù)結(jié)構(gòu)”的課號與“微機(jī)原理”的課號互換,實驗6.2 設(shè)計并執(zhí)行事務(wù),設(shè)計事務(wù)1begin transactiondeclare @cno1 char(2), @cno2 char(2)select @cno1=cno from jx_Course where Cname='數(shù)據(jù)結(jié)構(gòu)'select @cno2=cno from jx_Course where Cname='

21、微機(jī)原理'update jx_Course set Cno=@cno1 where Cname='微機(jī)原理'update jx_Course set Cno=@cno2 where Cname='數(shù)據(jù)結(jié)構(gòu)‘commit,實驗6.2 設(shè)計并執(zhí)行事務(wù),設(shè)計事務(wù)2要求：教師“許永軍”退休，由他講授的2門課程中，課程“微機(jī)原理”轉(zhuǎn)給教師“張明”講授；“數(shù)據(jù)庫”轉(zhuǎn)給“李英”講授,實驗6.2 設(shè)計并執(zhí)行事務(wù),

22、設(shè)計事務(wù)2begin transactiondeclare @cno char(2), @tno1 char(2), @tno2 char(2)select @tno1=Tno from jx_Tea where Tname=‘許永軍’select @tno2=Tno from jx_Tea where Tname=‘張朋’Select @cno=Cno from jx_Course where Cname=‘微機(jī)原理’up

23、date jx_TeaCourse set Tno=@tno2 where Tno=@tno1 and Cno=@cno,實驗6.2 設(shè)計并執(zhí)行事務(wù),設(shè)計事務(wù)2select @tno1=Tno from jx_Tea where Tname=‘許永軍’select @tno2=Tno from jx_Tea where Tname=‘李英’Select @cno=Cno from jx_Course where Cname=‘?dāng)?shù)據(jù)

24、庫’update jx_TeaCourse set Tno=@tno2 where Tno=@tno1 and Cno=@cnocommitgo,【實驗題】,設(shè)計并執(zhí)行事務(wù)1要求：學(xué)生“王一山”打算選修“計算機(jī)網(wǎng)絡(luò)”課程，根據(jù)規(guī)定，此門課程選修人數(shù)最多為30人，該生是否可以選修此門課程，給出結(jié)果提示。,【實驗題】,設(shè)計并執(zhí)行事務(wù)2要求：有一位姓名叫“程前”的男性副教授到計算機(jī)系應(yīng)聘“數(shù)據(jù)結(jié)構(gòu)”課程的任教工作。學(xué)校招聘原則是：若