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

根據您的理解，print語句和RAISERROR語句有什么區別回顧3代碼閱讀:指出下列語句的錯誤

CREATEPROCEDUREproc_stuInfo

--定義存儲過程

@stuNameVARCHAR(10),

@mINTOUTPUT,

@stuAge=18INT

AS

.....代碼略

GO

--調用存儲過程

DECLARE@sINT

EXECproc_stuInfo'張三',@s,30

EXECproc_stuInfo'張三',@s

EXECproc_stuInfo

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

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

本章目標掌握觸發器的用法理解觸發器的使用場合掌握創建Insert/update/delete觸發器的語法掌握創建列觸發器的語法掌握臨時表的用法全局臨時表和本地臨時表的用法全局臨時表和本地臨時表的區別掌握游標的用法為什么需要觸發器(TRIGGER)呢？典型的應用就是銀行的取款機系統一、觸發器

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

交易信息表transInfo

張三取錢200

問題：沒有自動修改張三的余額最優的解決方案就是采用觸發器：它是一種特殊的存儲過程也具備事務的功能它能在多表之間執行特殊的業務規則張三開戶1000元,李四開戶1元

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

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

ONtable_name[WITHENCRYPTION]FOR[DELETE,INSERT,UPDATE]AST-SQL語句GOWITHENCRYPTION表示加密觸發器定義的SQL文本DELETE,INSERT,UPDATE指定觸發器的類型INSERT觸發器transInfocardIDtransTypetransMoney1001000210010002存入

300存入

500insertedcardIDtransTypetransMoney10010001

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

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

-------關鍵代碼------CREATETRIGGERtrig_transInfoONtransInfoFORINSERTASDECLARE@typechar(4),@outMoneyMONEYDECLARE@myCardIDchar(10),@balanceMONEYSELECT@type=transType,@outMoney=transMoney，@myCardID=cardIDFROMinsertedIF(@type='支取')UPDATEbankSETcurrentMoney=currentMoney-@outMoneyWHEREcardID=@myCardIDELSEUPDATEbankSETcurrentMoney=currentMoney+@outMoneyWHEREcardID=@myCardID…..GOINSERT觸發器示例3-2從inserted表中獲取交易類型、教員金額等根據交易類型，減少或增加對應卡號的余額INSERT觸發器示例3-3transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200DELETE觸發器transInfocardIDtransTypetransMoney1001000210010002存入300存入500deletedcardIDtransTypetransMoney10010001支取200刪除記錄行觸發delete觸發器向deleted表中插入被刪除的副本觸發器檢查deleted表中被刪除的數據，決定是否需要回滾或執行其他操作DELETE觸發器的工作原理：問題：當刪除交易信息表時，要求自動備份被刪除的數據到表backupTable中。分析：在交易信息表上創建DELETE觸發器被刪除的數據可以從deleted表中獲取DELETE觸發器示例3-1-------關鍵代碼------CREATETRIGGERtrig_delete_transInfoONtransInfoFORDELETEASprint'開始備份數據，請稍后......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT*FROMdeletedprint'備份數據成功，備份表中的數據為:'SELECT*FROMbackupTableGO從deleted表中獲取被刪除的交易記錄DELETE觸發器示例3-2DELETE觸發器示例3-3Deleted（更新前的數據）customerNamecardIDcurrentMoney李四100000021bankcustomerName

cardID

currentMoney張三

100100011000

李四100000021UPDATE觸發器刪除記錄行向deleted表中插入被刪除的副本檢查deleted和inserted表中的數據，確定是否需要回滾或執行其他操作UPDATE觸發器的工作原理：李四1000000220001向inserted表中插入被添加的副本Inserted（更新后的數據）customerNamecardIDcurrentMoney李四1000000220001插入記錄行問題：跟蹤用戶的交易，交易金額超過20000元，則取消交易，并給出錯誤提示。分析：在bank表上創建UPDATE觸發器修改前的數據可以從deleted表中獲取修改后的數據可以從inserted表中獲取UPDATE觸發器示例3-1-------關鍵代碼------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觸發器3-2交易金額是否>2萬回滾事務，撤銷交易UPDATE觸發器3-3列級UPDATE觸發器3-1UPDATE觸發器除了跟蹤數據的變化（修改）外，還可以檢查是否修改了某列的數據使用UPDATE（列）函數檢測是否修改了某列問題：交易日期一般由系統自動產生，默認為當前日期。為了安全起見，一般禁止修改，以防舞弊。

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

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

INSERT觸發器

UPDATE觸發器

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