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

學(xué)習(xí)情景6事務(wù)和觸發(fā)器本章任務(wù)任務(wù)一事務(wù)的規(guī)劃與設(shè)計模擬實現(xiàn)銀行轉(zhuǎn)帳功能批量插入學(xué)生考試成績辦理畢業(yè)學(xué)生離校手續(xù)任務(wù)二客戶回訪管理系統(tǒng)觸發(fā)器的規(guī)劃與設(shè)計本章目標(biāo)學(xué)習(xí)觸發(fā)器的基本用法掌握觸發(fā)器的基本結(jié)構(gòu)學(xué)習(xí)事務(wù)的基本用法掌握事務(wù)的基本結(jié)構(gòu)為什么需要事務(wù)5-1銀行轉(zhuǎn)賬銀行轉(zhuǎn)賬問題：假定資金從賬戶A轉(zhuǎn)到賬戶B，至少需要兩步：賬戶A的資金減少然后賬戶B的資金相應(yīng)增加賬戶A賬戶B假定張三的賬戶直接轉(zhuǎn)賬1000元到李四的賬戶為什么需要事務(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)建賬戶表，存放用戶的賬戶信息添加約束：根據(jù)銀行規(guī)定，賬戶余額不能少于1元，否則視為銷戶張三開戶，開戶金額為1000元；李四開戶，開戶金額1元為什么需要事務(wù)5-3目前兩個賬戶的余額總和為：1000+1=1001元為什么需要事務(wù)5-4模擬實現(xiàn)轉(zhuǎn)賬從張三的賬戶轉(zhuǎn)賬1000元到李四的賬戶/*--轉(zhuǎn)賬測試：張三轉(zhuǎn)賬1000元給李四--*/--我們可能會這樣編寫語句--張三的賬戶少1000元，李四的賬戶多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='張三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO--再次查看轉(zhuǎn)賬后的結(jié)果SELECT*FROMbankGO請問：

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

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

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

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

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

COMMITTRANSACTION

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

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

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

強(qiáng)化約束

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

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

語法CREATETRIGGER

觸發(fā)器名

ON

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

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

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

觸發(fā)器名

ON

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

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

TRIGGERtr_c_Insert

ON

險種表AFTER

INSERTAS

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

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

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

TRIGGERt_xzh_delete

ON

險種表

AFTER

DELETEAS

BEGIN

declare@險種編號char(20)

select@險種編號=險種編號fromdeleted

print

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

delete

from

合同表where

險種編號=@險種編號

print

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

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

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

TRIGGERt_xzh_update

ON

險種表

AFTER

UPDATEAS

if

update(險種編號)

Begin

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

rollback

transaction

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

ONALLSERVER或DATABASEAFTER

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

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

TRIGGERtr_safetyON

DATABASE

AFTER

DROP_TABLE,ALTER_TABLEAS

BEGIN

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