數據庫應用基礎

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

currentMoneyMONEY--當前余額)GOALTERTABLEbank

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

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

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

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

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

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

COMMITTRANSACTION

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

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

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

強化約束

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

鼠標創(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ā)器，不需要顯式調用，當執(zhí)行insert操作后，將自動執(zhí)行。insertinto險種表values('0007','福祿滿堂保險','50','1',5000)觸發(fā)器被執(zhí)行一行已被插入表中創(chuàng)建AfterDelete觸發(fā)器

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

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

TRIGGERt_xzh_delete

ON

險種表

AFTER

DELETEAS

BEGIN

declare@險種編號char(20)

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

print

'開始查找并刪除合同表中的相關記錄...'

delete

from

合同表where

險種編號=@險種編號

print

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

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

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

TRIGGERt_xzh_update

ON

險種表

AFTER

UPDATEAS

if

update(險種編號)

Begin

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

rollback

transaction

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

ONALLSERVER或DATABASEAFTER

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

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

TRIGGERtr_safetyON

DATABASE

AFTER

DROP_TABLE,ALTER_TABLEAS

BEGIN

PRINT'對不起，您不能對數據表進行操作