SQL補(bǔ)充觸發(fā)器臨時(shí)表和游標(biāo)

SQL補(bǔ)充觸發(fā)器臨時(shí)表和游標(biāo)回顧1存儲(chǔ)過程是什么？使用存儲(chǔ)過程有什么好處？存儲(chǔ)過程的參數(shù)和局部變量有什么不同？請(qǐng)寫出以下存儲(chǔ)過程的調(diào)用命令，要求@num1使用默認(rèn)值，@num2使用65：createprocedureproc_sample(@num1int=60,@num2int=70 )as…回顧2代碼閱讀，下列系統(tǒng)存儲(chǔ)過程的功能是：EXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_renamedb‘stuDB',‘studentDB‘EXECxp_cmdshell‘mkdird:\project’

根據(jù)您的理解，print語句和RAISERROR語句有什么區(qū)別回顧3代碼閱讀:指出下列語句的錯(cuò)誤

CREATEPROCEDUREproc_stuInfo

--定義存儲(chǔ)過程

@stuNameVARCHAR(10),

@mINTOUTPUT,

@stuAge=18INT

AS

.....代碼略

GO

--調(diào)用存儲(chǔ)過程

DECLARE@sINT

EXECproc_stuInfo'張三',@s,30

EXECproc_stuInfo'張三',@s

EXECproc_stuInfo

@stuAge=22,@stuName='李四',@m=@sOUTPUT@stuAge

INT=18EXECproc_stuInfo'張三',@sOUTPUT,30EXECproc_stuInfo'張三',@sOUTPUT正確

本章目標(biāo)掌握觸發(fā)器的用法理解觸發(fā)器的使用場合掌握創(chuàng)建Insert/update/delete觸發(fā)器的語法掌握創(chuàng)建列觸發(fā)器的語法掌握臨時(shí)表的用法全局臨時(shí)表和本地臨時(shí)表的用法全局臨時(shí)表和本地臨時(shí)表的區(qū)別掌握游標(biāo)的用法為什么需要觸發(fā)器(TRIGGER)呢？典型的應(yīng)用就是銀行的取款機(jī)系統(tǒng)一、觸發(fā)器

演示：為什么需要觸發(fā)器.sql帳戶信息表bank

交易信息表transInfo

張三取錢200

問題：沒有自動(dòng)修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲(chǔ)過程也具備事務(wù)的功能它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則張三開戶1000元,李四開戶1元

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

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

ONtable_name[WITHENCRYPTION]FOR[DELETE,INSERT,UPDATE]AST-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問題：解決上述的銀行取款問題：當(dāng)向交易信息表(transInfo)中插入一條交易信息時(shí)，我們應(yīng)自動(dòng)更新對(duì)應(yīng)帳戶的余額。

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

-------關(guān)鍵代碼------CREATETRIGGERtrig_transInfoONtransInfoFORINSERTASDECLARE@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ù)交易類型，減少或增加對(duì)應(yīng)卡號(hào)的余額INSERT觸發(fā)器示例3-3transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200DELETE觸發(fā)器transInfocardIDtransTypetransMoney1001000210010002存入300存入500deletedcardIDtransTypetransMoney10010001支取200刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作DELETE觸發(fā)器的工作原理：問題：當(dāng)刪除交易信息表時(shí)，要求自動(dòng)備份被刪除的數(shù)據(jù)到表backupTable中。分析：在交易信息表上創(chuàng)建DELETE觸發(fā)器被刪除的數(shù)據(jù)可以從deleted表中獲取DELETE觸發(fā)器示例3-1-------關(guān)鍵代碼------CREATETRIGGERtrig_delete_transInfoONtransInfoFORDELETEASprint'開始備份數(shù)據(jù)，請(qǐng)稍后......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT*FROMdeletedprint'備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為:'SELECT*FROMbackupTableGO從deleted表中獲取被刪除的交易記錄DELETE觸發(fā)器示例3-2DELETE觸發(fā)器示例3-3Deleted（更新前的數(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元，則取消交易，并給出錯(cuò)誤提示。分析：在bank表上創(chuàng)建UPDATE觸發(fā)器修改前的數(shù)據(jù)可以從deleted表中獲取修改后的數(shù)據(jù)可以從inserted表中獲取UPDATE觸發(fā)器示例3-1-------關(guān)鍵代碼------CREATETRIGGERtrig_update_bankONbankFORUPDATEASDECLARE@beforeMoneyMONEY,@afterMoneyMONEYSELECT@beforeMoney=currentMoneyFROMdeletedSELECT@afterMoney=currentMoneyFROMinsertedIFABS(@afterMoney-@beforeMoney)>20000BEGINprint'交易金額:'+convert(varchar(8),ABS(@afterMoney-@beforeMoney))RAISERROR('每筆交易不能超過2萬元，交易失敗',16,1)ROLLBACKTRANSACTIONENDGO從deleted表中獲取交易前的余額，從inserted表中獲取交易后的余額UPDATE觸發(fā)器3-2交易金額是否>2萬回滾事務(wù)，撤銷交易UPDATE觸發(fā)器3-3列級(jí)UPDATE觸發(fā)器3-1UPDATE觸發(fā)器除了跟蹤數(shù)據(jù)的變化（修改）外，還可以檢查是否修改了某列的數(shù)據(jù)使用UPDATE（列）函數(shù)檢測(cè)是否修改了某列問題：交易日期一般由系統(tǒng)自動(dòng)產(chǎn)生，默認(rèn)為當(dāng)前日期。為了安全起見，一般禁止修改，以防舞弊。

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

-------關(guān)鍵代碼------CREATETRIGGERtrig_update_transInfoONtransInfoFORUPDATEASIFUPDATE(transDate)BEGINprint'交易失敗.....'RAISERROR(‘安全警告：交易日期不能修改，由系統(tǒng)自動(dòng)產(chǎn)生',16,1)ROLLBACKTRANSACTIONENDGO檢查是否修改了交易日期列transDate回滾事務(wù)，撤銷交易列級(jí)UPDATE觸發(fā)器3-2列級(jí)UPDATE觸發(fā)器3-3觸發(fā)器是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程，觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則觸發(fā)器還是一個(gè)特殊的事務(wù)單元，當(dāng)出現(xiàn)錯(cuò)誤時(shí)，可以執(zhí)行ROLLBACKTRANSACTION回滾撤銷操作觸發(fā)器一般都需要使用臨時(shí)表：deleted表和inserted表，它們存放了被刪除或插入的記錄行副本觸發(fā)器類型：

INSERT觸發(fā)器

UPDATE觸發(fā)器

DELETE觸發(fā)器小結(jié)臨時(shí)表工作于內(nèi)存中，臨時(shí)存儲(chǔ)數(shù)據(jù)行具有普通表的結(jié)構(gòu)和訪問特點(diǎn)訪問結(jié)束后，臨時(shí)表將會(huì)被自動(dòng)釋放SQL中沒有數(shù)組的概念，臨時(shí)表可用作替代，但功能更強(qiáng)大，結(jié)構(gòu)可以更復(fù)雜二、臨時(shí)表什么是臨時(shí)表臨時(shí)表的分類及區(qū)別本地臨時(shí)表表名以“#”開頭在與首次創(chuàng)建或引用表時(shí)相同的SQLServer實(shí)例連接期間，本地臨時(shí)表只對(duì)于創(chuàng)建者是可見的當(dāng)用戶與SQLServer實(shí)例斷開連接后，將刪除本地臨時(shí)表只能在一個(gè)存儲(chǔ)過程內(nèi)部起作用。全局臨時(shí)表表名以“##”開頭在創(chuàng)建后對(duì)任何用戶和任何連接都是可見的當(dāng)引用該表的所有用戶都與SQLServer實(shí)例斷開連接后，將刪除全局臨時(shí)表。可以在相互調(diào)用的存儲(chǔ)過程間起傳遞數(shù)據(jù)的作用。createtable#stuInfo( stuNochar(4)primarykey, namenchar(4), sexbit)go創(chuàng)建臨時(shí)表創(chuàng)建本地臨時(shí)表createtab