測試-第7章、觸發(fā)器

第七章觸發(fā)器

/回顧2-1代碼閱讀，下列系統(tǒng)存儲過程的功能是：EXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_renamedb‘stuDB',‘studentDB‘EXECxp_cmdshell‘mkdird:\project’

存儲過程的參數(shù)分為哪兩類？根據(jù)您的理解，print語句和RAISERROR語句有什么區(qū)別/回顧2-2代碼閱讀:指出下列語句的錯誤CREATEPROCEDUREproc_stuInfo--定義存儲過程@stuNameVARCHAR(10),@mINTOUTPUT,@stuAge=18INTAS.....代碼略GO--調(diào)用存儲過程DECLARE@sINTEXECproc_stuInfo'張三',@s,30EXECproc_stuInfo'張三',@sEXECproc_stuInfo@stuAge=22,@stuName='李四',@m=@sOUTPUT@stuAgeINT=18EXECproc_stuInfo'張三',@sOUTPUT,30EXECproc_stuInfo'張三',@sOUTPUT正確

/學習目標了解為什么需要觸發(fā)器理解觸發(fā)器的工作原理掌握如何使用inserted表和deleted表掌握如何創(chuàng)建：INSERT觸發(fā)器UPDATE觸發(fā)器DELETE觸發(fā)器/為什么需要觸發(fā)器(TRIGGER)呢？典型的應用就是銀行的取款機系統(tǒng)為什么需要觸發(fā)器演示：為什么需要觸發(fā)器.sql帳戶信息表bank交易信息表transInfo張三取錢200問題：沒有自動修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲過程也具備事務的功能它能在多表之間執(zhí)行特殊的業(yè)務規(guī)則張三開戶1000元,李四開戶1元

/張三李四王五趙二王三宋二劉五插入什么是觸發(fā)器3-1刪除觸發(fā)器觸發(fā)趙二退休

趙二員工表退休員工表/觸發(fā)器是在對表進行插入、更新或刪除操作時自動執(zhí)行的存儲過程觸發(fā)器通常用于強制業(yè)務規(guī)則觸發(fā)器是一種高級約束，可以定義比用CHECK約束更為復雜的約束可執(zhí)行復雜的SQL語句（if/while/case）可引用其它表中的列什么是觸發(fā)器3-2/觸發(fā)器定義在特定的表上，與表相關自動觸發(fā)執(zhí)行不能直接調(diào)用是一個事務（可回滾）什么是觸發(fā)器3-3/觸發(fā)器的類型DELETE觸發(fā)器INSERT觸發(fā)器UPDATE觸發(fā)器/觸發(fā)器觸發(fā)時：系統(tǒng)自動在內(nèi)存中創(chuàng)建deleted表或inserted表只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動刪除inserted表臨時保存了插入或更新后的記錄行可以從inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務需求如果不滿足，則向用戶報告錯誤消息，并回滾插入操作deleted表臨時保存了刪除或更新前的記錄行可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務需求如果不滿足，則向用戶報告錯誤消息，并回滾插入操作inserted和deleted表2-1/inserted和deleted表2-2修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和deleted表存放的信息/創(chuàng)建觸發(fā)器的語法：如何創(chuàng)建觸發(fā)器CREATETRIGGER

trigger_name

ONtable_name

[WITHENCRYPTION]

FOR[DELETE,INSERT,UPDATE]

AS

T-SQL語句GOWITHENCRYPTION表示加密觸發(fā)器定義的SQL文本DELETE,INSERT,UPDATE指定觸發(fā)器的類型/INSERT觸發(fā)器transInfocardIDtransTypetransMoney1001000210010002存入

300存入

500insertedcardIDtransTypetransMoney10010001

支取200transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200插入記錄行觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作INSERT觸發(fā)器的工作原理：/INSERT觸發(fā)器示例3-1問題：解決上述的銀行取款問題：當向交易信息表(transInfo)中插入一條交易信息時，我們應自動更新對應帳戶的余額。

分析：在交易信息表上創(chuàng)建INSERT觸發(fā)器從inserted臨時表中獲取插入的數(shù)據(jù)行根據(jù)交易類型(transType)字段的值是存入/支取，增加/減少對應帳戶的余額。

/-------關鍵代碼------CREATETRIGGERtrig_transInfoONtransInfo

FORINSERT

ASDECLARE@typechar(4),@outMoneyMONEYDECLARE@myCardIDchar(10),@balanceMONEYSELECT@type=transType,@outMoney=transMoney，@myCardID=cardIDFROMinsertedIF(@type='支取')UPDATEbankSETcurrentMoney=currentMoney-@outMoneyWHEREcardID=@myCardIDELSEUPDATEbankSETcurrentMoney=currentMoney+@outMoneyWHEREcardID=@myCardID…..GOINSERT觸發(fā)器示例3-2

從inserted表中獲取交易類型、教員金額等根據(jù)交易類型，減少或增加對應卡號的余額/INSERT觸發(fā)器示例3-3/INSERT觸發(fā)器示例Ⅱ創(chuàng)建INSERT觸發(fā)器通常用來驗證被觸發(fā)器監(jiān)控的字段中的數(shù)據(jù)滿足要求的標準。有以下例子：在插入數(shù)據(jù)的時候自動檢查年齡是否正確，若輸入有誤，則給出警告。/INSERT觸發(fā)器示例Ⅱ-------關鍵代碼------CREATETRIGGERstu_insertONstuInfo

FORINSERT

ASdeclare@stuAgeintselect@stuAge=stuInfo.stuAgefromstuInfo,insertedwherestuInfo.stuName=inserted.stuName if(@stuAge<=0) begin raiserror('年齡插入出錯！',16,10) endGO/INSERT觸發(fā)器示例Ⅱ那么我們在插入這樣一條數(shù)據(jù)的時候會出現(xiàn)什么樣子的后果？insertintostuInfovalues('張三','s25317','男',0,2,'海南三亞')/INSERT觸發(fā)器示例Ⅱ在進行插入數(shù)據(jù)的時候,在輸入錯誤信息的時候,數(shù)據(jù)庫也提示信息錯誤,但是仍然插入的一行數(shù)據(jù).這時候我們該怎么辦呢?可以使用Rollback來控制回滾/現(xiàn)場演練向?qū)W員成績表中添加一條記錄記錄,筆試成績和機試成績都要在0到100之間,要求使用觸發(fā)器來完成？添加一名學員，同時默認該學員所有成績?yōu)?0分？/transInfocardIDtransTypetransMoney1001000210010002存入

300存入

50010010001支取200DELETE觸發(fā)器transInfocardIDtransTypetransMoney1001000210010002存入300存入500deletedcardIDtransTypetransMoney10010001支取

200刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作DELETE觸發(fā)器的工作原理：/問題：當刪除交易信息表時，要求自動備份被刪除的數(shù)據(jù)到表backupTable中。分析：在交易信息表上創(chuàng)建DELETE觸發(fā)器被刪除的數(shù)據(jù)可以從deleted表中獲取DELETE觸發(fā)器示例3-1/-------關鍵代碼------CREATETRIGGERtrig_delete_transInfoONtransInfoFORDELETEASprint'開始備份數(shù)據(jù)，請稍后......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeleted

ELSEINSERTINTObackupTableSELECT*FROMdeletedprint'備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為:'SELECT*FROMbackupTableGO從deleted表中獲取被刪除的交易記錄DELETE觸發(fā)器示例3-2/DELETE觸發(fā)器示例3-3/現(xiàn)場演練一名學員畢業(yè)了,需要將這名學員的信息從學員信息表中刪除,并且將他存放在畢業(yè)學員信息表中去我們知道在兩張表有主外鍵關系的時候，不能刪除被引用的數(shù)據(jù)，那么現(xiàn)在學習了觸發(fā)器之后能去刪除那個被引用的數(shù)據(jù)么？提示:我們可以保存前一條的刪除數(shù)據(jù)到新表中去/Deleted（更新前的數(shù)據(jù)）customerNamecardIDcurrentMoney李四100000021bankcustomerName

cardID

currentMoney張三

100100011000

李四100000021UPDATE觸發(fā)器刪除記錄行向deleted表中插入被刪除的副本檢查deleted和inserted表中的數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作UPDATE觸發(fā)器的工作原理：李四1000000220001向inserted表中插入被添加的副本Inserted（更新后的數(shù)據(jù)）customerNamecardIDcurrentMoney李四1000000220001插入記錄行/問題：跟蹤用戶的交易，交易金額超過20000元，則取消交易，并給出錯誤提示。分析：在bank表上創(chuàng)建UPDATE觸發(fā)器修改前的數(shù)據(jù)可以從deleted表中獲取修改后的數(shù)據(jù)可以從inserted表中獲取UPDATE觸發(fā)器示例3-1/-------關鍵代碼------CREATETRIGGERtrig_update_bank

ONbank

FORUPDATE

ASDECLARE@beforeMoneyMONEY,@afterMoneyMONEYSELECT@beforeMoney=currentMoneyFROMdeleted

SELECT@afterMoney=currentMoneyFROMinserted

IFABS(@afterMoney-@beforeMoney)>20000BEGINprint'交易金額:'+convert(varchar(8),ABS(@afterMoney-@beforeMoney))RAISERROR('每筆交易不能超過2萬元，交易失敗',16,1)

ROLLBACKTRANSACTIONENDGO

從deleted表中獲取交易前的余額，從inserted表中獲取交易后的余額UPDATE觸發(fā)器3-2交易金額是否>2萬回滾事務，撤銷交易/UPDATE觸發(fā)器3-3/列級UPDATE觸發(fā)器3-1UPDATE觸發(fā)器除了跟蹤數(shù)據(jù)的變化（修改）外，還可以檢查是否修改了某列的數(shù)據(jù)使用UPDATE（列）函數(shù)檢測是否修改了某列問題：交易日期一般由系統(tǒng)自動產(chǎn)生，默認為當前日期。為了安全起見，一般禁止修改，以防舞弊。

分析：UPDATE（列名）函數(shù)可以檢測是否修改了某列

/-------關鍵代碼------CREATETRIGGERtrig_update_transInfoONtransInfoFORUPDATEASIFUPDATE(transDate)BEGINprint'交易失敗.....'RAISERROR(‘安全警告：交易日期不能修改，由系統(tǒng)自動產(chǎn)生',16,1)ROLLBACKTRANSACTIONENDGO檢查是否修改了交易日期列transDate回滾事務，撤銷交易列級UPDATE觸發(fā)器3-2/列級UPDATE觸發(fā)器3-3/刪除觸發(fā)器我們可以通過以下語句來刪除觸發(fā)器DROPTRIGGER觸發(fā)器名稱/查看觸發(fā)器我們可以通過語句來查看觸發(fā)器select*fromsysobjectswherextype='tr'/觸發(fā)器的優(yōu)點和缺點優(yōu)點保證數(shù)據(jù)的正確性和邏輯，比如訂單表中新增一條數(shù)據(jù)，對應在庫存表中會減少一個產(chǎn)品一樣。還有保證數(shù)據(jù)的安全性，比如當用戶刪除表A，我們可以判斷他是否為Ad