數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)

學(xué)習(xí)情景6事務(wù)和觸發(fā)器本章任務(wù)任務(wù)一事務(wù)的規(guī)劃與設(shè)計(jì)模擬實(shí)現(xiàn)銀行轉(zhuǎn)帳功能批量插入學(xué)生考試成績(jī)辦理畢業(yè)學(xué)生離校手續(xù)任務(wù)二客戶(hù)回訪管理系統(tǒng)觸發(fā)器的規(guī)劃與設(shè)計(jì)本章目標(biāo)學(xué)習(xí)觸發(fā)器的基本用法掌握觸發(fā)器的基本結(jié)構(gòu)學(xué)習(xí)事務(wù)的基本用法掌握事務(wù)的基本結(jié)構(gòu)為什么需要事務(wù)5-1銀行轉(zhuǎn)賬銀行轉(zhuǎn)賬問(wèn)題：假定資金從賬戶(hù)A轉(zhuǎn)到賬戶(hù)B，至少需要兩步：賬戶(hù)A的資金減少然后賬戶(hù)B的資金相應(yīng)增加賬戶(hù)A賬戶(hù)B假定張三的賬戶(hù)直接轉(zhuǎn)賬1000元到李四的賬戶(hù)為什么需要事務(wù)5-2CREATETABLEbank(customerNameCHAR(10),--顧客姓名

currentMoneyMONEY--當(dāng)前余額)GOALTERTABLEbank

ADDCONSTRAINTCK_currentMoneyCHECK(currentMoney>=1)GOINSERTINTObank(customerName,currentMoney)VALUES('張三',1000)INSERTINTObank(customerName,currentMoney)VALUES('李四',1)創(chuàng)建賬戶(hù)表，存放用戶(hù)的賬戶(hù)信息添加約束：根據(jù)銀行規(guī)定，賬戶(hù)余額不能少于1元，否則視為銷(xiāo)戶(hù)張三開(kāi)戶(hù)，開(kāi)戶(hù)金額為1000元；李四開(kāi)戶(hù)，開(kāi)戶(hù)金額1元為什么需要事務(wù)5-3目前兩個(gè)賬戶(hù)的余額總和為：1000+1=1001元為什么需要事務(wù)5-4模擬實(shí)現(xiàn)轉(zhuǎn)賬從張三的賬戶(hù)轉(zhuǎn)賬1000元到李四的賬戶(hù)/*--轉(zhuǎn)賬測(cè)試：張三轉(zhuǎn)賬1000元給李四--*/--我們可能會(huì)這樣編寫(xiě)語(yǔ)句--張三的賬戶(hù)少1000元，李四的賬戶(hù)多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='張三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO--再次查看轉(zhuǎn)賬后的結(jié)果SELECT*FROMbankGO請(qǐng)問(wèn)：

執(zhí)行轉(zhuǎn)賬語(yǔ)句后，張三、李四的賬戶(hù)余額分別是多少？張三的賬戶(hù)沒(méi)有減少但李四的賬戶(hù)卻多了1000元1000＋1001＝2001元

總額多出了1000元！為什么需要事務(wù)5-5--張三的賬戶(hù)減少1000元，李四的賬戶(hù)增加1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='張三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO發(fā)生錯(cuò)誤的原因執(zhí)行失敗，張三賬戶(hù)余額還是1000元繼續(xù)往下執(zhí)行：執(zhí)行成功，李四賬戶(hù)余額變?yōu)?001元如何解決呢？使用事務(wù)UPDATE語(yǔ)句違反約束：

余額>=1元什么是事務(wù)事務(wù)(TRANSACTION)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作多個(gè)操作作為一個(gè)整體向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行事務(wù)是一個(gè)不可分割的工作邏輯單元轉(zhuǎn)賬過(guò)程就是一個(gè)事務(wù)它需要兩條UPDATE語(yǔ)句來(lái)完成，這兩條語(yǔ)句是一個(gè)整體如果其中任一條出現(xiàn)錯(cuò)誤，則整個(gè)轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個(gè)賬戶(hù)中的余額應(yīng)恢復(fù)到原來(lái)的數(shù)據(jù)，從而確保轉(zhuǎn)賬前和轉(zhuǎn)賬后的余額不變，即都是1001元事務(wù)的特性事務(wù)必須具備以下四個(gè)屬性，簡(jiǎn)稱(chēng)ACID屬性：原子性（Atomicity）一致性（Consistency）隔離性（Isolation）永久性（Durability）事務(wù)是一個(gè)完整的操作事務(wù)的各步操作是不可分的（原子的）；要么都執(zhí)行，要么都不執(zhí)行當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài)并發(fā)事務(wù)之間彼此隔離、獨(dú)立，它不應(yīng)以任何方式依賴(lài)于或影響其他事務(wù)事務(wù)完成后，它對(duì)數(shù)據(jù)庫(kù)的修改被永久保持如何創(chuàng)建事務(wù)2-1使用SQL語(yǔ)句管理事務(wù)開(kāi)始事務(wù)提交事務(wù)回滾（撤銷(xiāo)）事務(wù)ROLLBACKTRANSACTIONCOMMITTRANSACTIONBEGINTRANSACTION如何創(chuàng)建事務(wù)2-2一旦事務(wù)提交或回滾，則事務(wù)結(jié)束判斷某條語(yǔ)句執(zhí)行是否出錯(cuò)：使用全局變量@@ERROR@@ERROR只判斷當(dāng)前一條T-SQL語(yǔ)句執(zhí)行是否有錯(cuò)為了判斷事務(wù)中所有T-SQL語(yǔ)句是否有錯(cuò)，可以對(duì)錯(cuò)誤進(jìn)行累計(jì)如：事務(wù)可以嵌套SET@errorSum=@errorSum+@@ERROR事務(wù)模式分類(lèi)顯式事務(wù)用BEGINTRANSACTION明確指定事務(wù)的開(kāi)始最常用的事務(wù)類(lèi)型隱性事務(wù)通過(guò)設(shè)置SETIMPLICIT_TRANSACTIONSON語(yǔ)句，將隱性事務(wù)模式設(shè)置為打開(kāi)其后的T-SQL語(yǔ)句自動(dòng)啟動(dòng)一個(gè)新事務(wù)提交或回滾一個(gè)事務(wù)后，下一個(gè)T-SQL語(yǔ)句又將啟動(dòng)一個(gè)新事務(wù)自動(dòng)提交事務(wù)SQLServer的默認(rèn)模式每條單獨(dú)的T-SQL語(yǔ)句視為一個(gè)事務(wù)BEGINTRANSACTION

/*--定義變量，用于累計(jì)事務(wù)執(zhí)行過(guò)程中的錯(cuò)誤--*/DECLARE@errorSumINTSET@errorSum=0--初始化為0，即無(wú)錯(cuò)誤/*--轉(zhuǎn)賬：張三的賬戶(hù)減少1000元，李四的賬戶(hù)增加1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='張三'SET@errorSum=@errorSum+@@ERROR--累計(jì)是否有錯(cuò)誤UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'SET@errorSum=@errorSum+@@ERROR使用事務(wù)解決銀行轉(zhuǎn)賬問(wèn)題4-1從張三的賬戶(hù)轉(zhuǎn)出1000元，存入李四的賬戶(hù)中開(kāi)始事務(wù)（指定事務(wù)從此處開(kāi)始，后續(xù)的T-SQL語(yǔ)句都是一個(gè)整體）累計(jì)是否有錯(cuò)誤IF@errorSum<>0--如果SQL語(yǔ)句執(zhí)行出錯(cuò)

BEGINPRINT'交易失敗，回滾事務(wù)'ROLLBACKTRANSACTIONENDELSEBEGINPRINT'交易成功，提交事務(wù)，寫(xiě)入硬盤(pán)，永久的保存'

COMMITTRANSACTION

ENDGOPRINT'查看轉(zhuǎn)賬事務(wù)后的余額'SELECT*FROMbankGO根據(jù)執(zhí)行是否有錯(cuò)誤，決定提交事務(wù)，或撤銷(xiāo)事務(wù)如果有錯(cuò)，則回滾操作，事務(wù)結(jié)束如果成功，則提交操作，事務(wù)結(jié)束從張三的賬戶(hù)轉(zhuǎn)出1000元，存入李四的賬戶(hù)中使用事務(wù)解決銀行轉(zhuǎn)賬問(wèn)題4-2使用事務(wù)解決銀行轉(zhuǎn)賬問(wèn)題4-3轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)過(guò)程中轉(zhuǎn)賬事務(wù)結(jié)束后轉(zhuǎn)賬1000元，轉(zhuǎn)賬失敗的情況演示案例1：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬1000元使用事務(wù)解決銀行轉(zhuǎn)賬問(wèn)題4-4轉(zhuǎn)賬事務(wù)前轉(zhuǎn)賬事務(wù)過(guò)程中轉(zhuǎn)賬事務(wù)結(jié)束后轉(zhuǎn)賬800元，轉(zhuǎn)賬成功的情況演示案例2：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬800元指導(dǎo)——批量插入學(xué)生考試成績(jī)2-1訓(xùn)練要點(diǎn)：使用事務(wù)向表中插入多條記錄需求說(shuō)明：批量插入?yún)⒓咏裉臁癑avaLogic”課程考試的十名學(xué)生成績(jī)?nèi)绻斎氲某煽?jī)大于100分，將違反約束講解需求說(shuō)明指導(dǎo)——批量插入學(xué)生考試成績(jī)2-2實(shí)現(xiàn)思路：使用顯式事務(wù)完成批量插入10個(gè)學(xué)生考試成績(jī)的操作使用全局變量@@ERROR判斷插入操作是否成功使用IF語(yǔ)句判斷@@ERROR值。如果插入成功，提交事務(wù)；否則回滾事務(wù)完成時(shí)間：20分鐘指導(dǎo)——批量插入學(xué)生考試成績(jī)2-3實(shí)現(xiàn)代碼：BEGINTRANSACTIONDECLARE@errorSumINTSET@errorSum=0/*--插入數(shù)據(jù)--*/INSERTINTOResult(StudentNo,SubjectNo,ExamDate,StudentResult)VALUES(10012,1,'2009-5-20',102)--分?jǐn)?shù)違反約束SET@errorSum=@errorSum+@@ERROR……IF(@errorSum<>0)--如果有錯(cuò)誤BEGINPRINT'插入失敗，回滾事務(wù)'ROLLBACKTRANSACTIONENDELSEBEGINPRINT'插入成功，提交事務(wù)'COMMITTRANSACTIONEND練習(xí)——辦理畢業(yè)學(xué)生離校手續(xù)2-1需求說(shuō)明：將畢業(yè)學(xué)生的基本信息和考試成績(jī)分別保存到歷史表中提示：使用顯式事務(wù)查詢(xún)Result表中所有Y2學(xué)生的考試成績(jī)，保存到表HistoreResult中刪除Result表中所有Y2學(xué)生的考試成績(jī)查詢(xún)Student表中所有Y2的學(xué)生記錄，保存到表HistoryStudent中刪除Studet表中所有Y2學(xué)生記錄完成時(shí)間：20分鐘練習(xí)——辦理畢業(yè)學(xué)生離校手續(xù)2-2實(shí)現(xiàn)代碼：BEGINTRANSACTIONDECLARE@errorSumINTSET@errorSum=0/*--查詢(xún)Result表中所有Y2學(xué)生的考試成績(jī)，保存到新表HistoreResult*/SELECTResult.*INTOHistoreResultFROMResultINNERJOINStudentONResult.StudentNo=Student.StudentNoINNERJOINGradeONGrade.GradeId=Student.GradeIdWHEREGradeName='Y2'SET@errorSum=@errorSum+@@ERROR/*--刪除Result表中所有Y2學(xué)生的考試成績(jī)*/DELETEResultFROMResultJOINStudentONResult.StudentNo=Student.StudentNoINNERJOINGradeONGrade.GradeId=Student.GradeIdWHEREGradeName='Y2'SET@errorSum=@errorSum+@@ERROR/*--將Student表中所有Y2的學(xué)生記錄，保存到新表HistoryStudent*/……/*--刪除Studet表中所有Y2學(xué)生記錄*/……/*--根據(jù)是否有錯(cuò)誤，確定事務(wù)是提交還是撤銷(xiāo)--*/小結(jié)數(shù)據(jù)庫(kù)事務(wù)具有哪些特性？SQLServer中，事務(wù)有幾種類(lèi)型？Transact-SQL使用哪些語(yǔ)句來(lái)管理事務(wù)？客戶(hù)回訪管理系統(tǒng)觸發(fā)器的規(guī)劃與設(shè)計(jì)任務(wù)描述：任務(wù)目標(biāo)：學(xué)習(xí)觸發(fā)器的基本用法，完成客戶(hù)回訪管理系統(tǒng)觸發(fā)器的設(shè)計(jì)掌握觸發(fā)器的基本用法掌握觸發(fā)器的基本結(jié)構(gòu)張三李四王五趙二更新觸發(fā)器插入觸發(fā)器觸發(fā)新生注冊(cè)

學(xué)生表班級(jí)表班號(hào)班級(jí)名人數(shù)112信管64212軟開(kāi)50312網(wǎng)編35觸發(fā)器是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。觸發(fā)器的優(yōu)點(diǎn)：跟蹤變化

相關(guān)表自動(dòng)進(jìn)行級(jí)聯(lián)更改

強(qiáng)化約束

觸發(fā)器觸發(fā)器與存儲(chǔ)過(guò)程的區(qū)別觸發(fā)器由事件觸發(fā)而被執(zhí)行，例如對(duì)某個(gè)表執(zhí)行insert、update、delete等操作時(shí)存儲(chǔ)過(guò)程是通過(guò)調(diào)用存儲(chǔ)過(guò)程名直接執(zhí)行觸發(fā)器觸發(fā)器DML觸發(fā)器DDL觸發(fā)器SQLServer2008觸發(fā)器類(lèi)別：AFTERINSTEADOF服務(wù)器數(shù)據(jù)庫(kù)觸發(fā)器的類(lèi)別觸發(fā)器觸發(fā)時(shí)：系統(tǒng)自動(dòng)在內(nèi)存中創(chuàng)建deleted表或inserted表只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除inserted表臨時(shí)保存了插入或更新后的記錄行可以從inserted表中檢查插入的數(shù)據(jù)是否滿(mǎn)足業(yè)務(wù)需求如果不滿(mǎn)足，則向用戶(hù)報(bào)告錯(cuò)誤消息，并回滾插入操作deleted表臨時(shí)保存了刪除或更新前的記錄行可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿(mǎn)足業(yè)務(wù)需求如果不滿(mǎn)足，則向用戶(hù)報(bào)告錯(cuò)誤消息，并回滾插入操作inserted和deleted表修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和deleted表存放的信息

inserted和deleted表創(chuàng)建DML觸發(fā)器

語(yǔ)法CREATETRIGGER

觸發(fā)器名

ON

作用域[FOR/AFTER,INSTEADOF]觸發(fā)事件ASBEGIN

觸發(fā)器主體：SQL語(yǔ)句END作用域：可以是table或者viewFOR和AFTER含義一樣，表示觸發(fā)時(shí)機(jī)是AFTER觸發(fā)事件是DELETE，UPDATE，或者INSERT創(chuàng)建DML觸發(fā)器

鼠標(biāo)創(chuàng)建和查看觸發(fā)器創(chuàng)建AfterInsert觸發(fā)器語(yǔ)法【例6-1】增加一個(gè)險(xiǎn)種，并打印‘添加一個(gè)險(xiǎn)種’消息。CREATETRIGGER

觸發(fā)器名

ON

作用域[FOR/AFTER,INSTEADOF]觸發(fā)事件ASBEGIN

觸發(fā)器主體：SQL語(yǔ)句ENDCREATE

TRIGGERtr_c_Insert

ON

險(xiǎn)種表AFTER

INSERTAS

print'添加一個(gè)險(xiǎn)種'GO創(chuàng)建AfterInsert觸發(fā)器執(zhí)行該觸發(fā)器，不需要顯式調(diào)用，當(dāng)執(zhí)行insert操作后，將自動(dòng)執(zhí)行。insertinto險(xiǎn)種表values('0007','福祿滿(mǎn)堂保險(xiǎn)','50','1',5000)觸發(fā)器被執(zhí)行一行已被插入表中創(chuàng)建AfterDelete觸發(fā)器

【例6-2】創(chuàng)建AFTERDELETE類(lèi)型的觸發(fā)器，當(dāng)險(xiǎn)種表中刪除某個(gè)險(xiǎn)種時(shí)，將合同表中關(guān)聯(lián)的記錄一起刪除創(chuàng)建AfterDelete觸發(fā)器

【例6-2】創(chuàng)建AFTERDELETE類(lèi)型的觸發(fā)器，當(dāng)險(xiǎn)種表中刪除某個(gè)險(xiǎn)種時(shí)，將合同表中關(guān)聯(lián)的記錄一起刪除CREATE

TRIGGERt_xzh_delete

ON

險(xiǎn)種表

AFTER

DELETEAS

BEGIN

declare@險(xiǎn)種編號(hào)char(20)

select@險(xiǎn)種編號(hào)=險(xiǎn)種編號(hào)fromdeleted

print

'開(kāi)始查找并刪除合同表中的相關(guān)記錄...'

delete

from

合同表where

險(xiǎn)種編號(hào)=@險(xiǎn)種編號(hào)

print

'刪除合同表的相關(guān)記錄為：'+str(@@rowcount)+'條'ENDGO驗(yàn)證：deletefrom險(xiǎn)種表where險(xiǎn)種編號(hào)='0005'創(chuàng)建AfterDelete觸發(fā)器

【例6-2】創(chuàng)建AFTERDELETE類(lèi)型的觸發(fā)器，當(dāng)險(xiǎn)種表中刪除某個(gè)險(xiǎn)種時(shí)，將合同表中關(guān)聯(lián)的記錄一起刪除創(chuàng)建AfterUpdate觸發(fā)器

【例6-3】創(chuàng)建一個(gè)修改觸發(fā)器，防止用戶(hù)修改險(xiǎn)種表中的險(xiǎn)種號(hào)CREATE

TRIGGERt_xzh_update

ON

險(xiǎn)種表

AFTER

UPDATEAS

if

update(險(xiǎn)種編號(hào))

Begin

raiserror('不能在周末修改數(shù)據(jù)!',16,10)

rollback

transaction

endGO驗(yàn)證：update險(xiǎn)種表set險(xiǎn)種編號(hào)='0008'where險(xiǎn)種編號(hào)='0006'創(chuàng)建DDL觸發(fā)器DDL觸發(fā)器：針對(duì)各種數(shù)據(jù)定義語(yǔ)言事件而激發(fā)，不針對(duì)增、刪、改等激發(fā)。用途：要防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些更改希望數(shù)據(jù)庫(kù)中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改要記錄數(shù)據(jù)庫(kù)架構(gòu)中的更改或事件創(chuàng)建DDL觸發(fā)器語(yǔ)法CREATETRIGGER觸發(fā)器名

ONALLSERVER或DATABASEAFTER

激活DDL觸發(fā)器的事件ASBEGIN

觸發(fā)器主體：SQL語(yǔ)句END創(chuàng)建數(shù)據(jù)級(jí)觸發(fā)器【例6-4】保護(hù)數(shù)據(jù)庫(kù)中的數(shù)據(jù)表不被修改，不被刪除。CREATE

TRIGGERtr_safetyON

DATABASE

AFTER

DROP_TABLE,ALTER_TABLEAS

BEGIN

PRINT'對(duì)不起，您不能對(duì)數(shù)據(jù)表進(jìn)行操作