實(shí)驗(yàn)七觸發(fā)器與事務(wù)設(shè)計(jì)

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

2、現(xiàn)級(jí)聯(lián)更改可強(qiáng)制比用CHECK約束定義的約束更為復(fù)雜的約束可引用其他表中的列可執(zhí)行其它操作，如修改數(shù)據(jù)或顯示用戶定義的錯(cuò)誤信息可包含使用Transact-SQL代碼的復(fù)雜處理邏輯可評(píng)估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對(duì)策,【知識(shí)要點(diǎn)】,觸發(fā)器的類型UPDATEINSERTDELETE,【知識(shí)要點(diǎn)】,創(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]},【知識(shí)要點(diǎn)】,使用INSERTED和DELETED表由SQL Server自動(dòng)創(chuàng)建和管理，并臨時(shí)駐留在內(nèi)存中。DELETED表用于存儲(chǔ)D

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

5、x_Stu創(chuàng)建一個(gè)級(jí)聯(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,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_Stu創(chuàng)建一個(gè)級(jí)聯(lián)更新觸發(fā)器Trigger_Stu驗(yàn)證觸發(fā)器的作用查看表jx_Stu和jx_SelCourse之間是否已創(chuàng)建外鍵參照關(guān)系，若已創(chuàng)建，則刪除。修改表jx_Stu中的記錄值，如將S1改為S0查看表jx_SelCourse，可以

7、看到其中的兩個(gè)S1學(xué)號(hào)同時(shí)自動(dòng)變成S0,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個(gè)限制更新觸發(fā)器Trigger_SC要求：若修改jx_SelCourse表中一條記錄的學(xué)號(hào)，則要檢查表jx_Stu中是否存在與該學(xué)號(hào)相同的記錄，若存在則不許修改，若不存在則可修改。,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個(gè)限制更新觸發(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,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,交互式為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個(gè)限制更新觸發(fā)器Trigger_SC驗(yàn)證觸發(fā)器的作用修改表jx_Stu中的記錄值，如將S2改為S0點(diǎn)擊“!”按鈕，注意觀察，修改后的學(xué)號(hào)又自動(dòng)從S1改回S9,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個(gè)觸發(fā)器Trigg

10、er_SC_Score要求：當(dāng)插入一條記錄或修改成績時(shí)，確保此記錄的成績?cè)?—100分之間,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個(gè)觸發(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之間！請(qǐng)重新輸入?！疪OLLBACK TRANSACTION,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一個(gè)觸發(fā)器Trigger_SC_S

12、core驗(yàn)證觸發(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,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_Course創(chuàng)建一個(gè)級(jí)聯(lián)刪除觸發(fā)器Trigger_DC要求：通過課程名從jx_Course表中刪除某課程信息，同時(shí)刪除jx_SelCourse表中與此課程相關(guān)的選課記錄。,實(shí)驗(yàn)6.1 創(chuàng)建和執(zhí)行觸發(fā)器,用SQL

14、為數(shù)據(jù)庫表jx_Course創(chuàng)建一個(gè)級(jí)聯(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,實(shí)驗(yàn)6.1 創(chuàng)建和

15、執(zhí)行觸發(fā)器,用SQL為數(shù)據(jù)庫表jx_Course創(chuàng)建一個(gè)級(jí)聯(lián)刪除觸發(fā)器Trigger_DC驗(yàn)證觸發(fā)器的作用DELETE FROM jx_Course WHERE Cno=‘C1’GO,【實(shí)驗(yàn)題】,為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一觸發(fā)器，當(dāng)插入或修改一個(gè)記錄時(shí)，確保此記錄的成績?cè)?—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í)驗(yàn)題】,為數(shù)據(jù)庫表jx_Tea創(chuàng)建一觸發(fā)器，當(dāng)職稱從“講師”晉升到“副教授”時(shí)，崗位津貼自動(dòng)增加500元；從“副教授”晉升為“教授”時(shí)，崗位津貼自動(dòng)增加900元。為數(shù)據(jù)庫表jx_SelCourse創(chuàng)建一觸發(fā)器：將成績按

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

20、事務(wù),設(shè)計(jì)事務(wù)1要求：將課程“數(shù)據(jù)結(jié)構(gòu)”的課號(hào)與“微機(jī)原理”的課號(hào)互換,實(shí)驗(yàn)6.2 設(shè)計(jì)并執(zhí)行事務(wù),設(shè)計(jì)事務(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,實(shí)驗(yàn)6.2 設(shè)計(jì)并執(zhí)行事務(wù),設(shè)計(jì)事務(wù)2要求：教師“許永軍”退休，由他講授的2門課程中，課程“微機(jī)原理”轉(zhuǎn)給教師“張明”講授；“數(shù)據(jù)庫”轉(zhuǎn)給“李英”講授,實(shí)驗(yàn)6.2 設(shè)計(jì)并執(zhí)行事務(wù),

22、設(shè)計(jì)事務(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,實(shí)驗(yàn)6.2 設(shè)計(jì)并執(zhí)行事務(wù),設(shè)計(jì)事務(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í)驗(yàn)題】,設(shè)計(jì)并執(zhí)行事務(wù)1要求：學(xué)生“王一山”打算選修“計(jì)算機(jī)網(wǎng)絡(luò)”課程，根據(jù)規(guī)定，此門課程選修人數(shù)最多為30人，該生是否可以選修此門課程，給出結(jié)果提示。,【實(shí)驗(yàn)題】,設(shè)計(jì)并執(zhí)行事務(wù)2要求：有一位姓名叫“程前”的男性副教授到計(jì)算機(jī)系應(yīng)聘“數(shù)據(jù)結(jié)構(gòu)”課程的任教工作。學(xué)校招聘原則是：若