第十章觸發(fā)器

第十章觸發(fā)器

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

存儲(chǔ)過(guò)程的參數(shù)分為哪兩類(lèi)？根據(jù)您的理解，print語(yǔ)句和RAISERROR語(yǔ)句有什么區(qū)別3回顧2-2代碼閱讀:指出下列語(yǔ)句的錯(cuò)誤

CREATEPROCEDUREproc_stuInfo

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

@stuNameVARCHAR(10),

@mINTOUTPUT,

@stuAge=18INT

AS

.....代碼略

GO

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

DECLARE@sINT

EXECproc_stuInfo'張三',@s,30

EXECproc_stuInfo'張三',@s

EXECproc_stuInfo

@stuAge=22,@stuName='李四',@m=@sOUTPUT@stuAgeINT=18EXECproc_stuInfo'張三',@sOUTPUT,30EXECproc_stuInfo'張三',@sOUTPUT4本章目標(biāo)了解為什么需要觸發(fā)器理解觸發(fā)器的工作原理掌握如何使用inserted表和deleted表掌握如何創(chuàng)建：INSERT觸發(fā)器UPDATE觸發(fā)器DELETE觸發(fā)器5為什么需要觸發(fā)器(TRIGGER)呢？典型的應(yīng)用就是銀行的取款機(jī)系統(tǒng)為什么需要觸發(fā)器演示：為什么需要觸發(fā)器.sql帳戶(hù)信息表bank交易信息表transInfo張三取錢(qián)200問(wèn)題：沒(méi)有自動(dòng)修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲(chǔ)過(guò)程也具備事務(wù)的功能它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則張三開(kāi)戶(hù)1000元,李四開(kāi)戶(hù)1元

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

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

如何創(chuàng)建觸發(fā)器CREATETRIGGERtrigger_name

ONtable_name[WITHENCRYPTION]

FOR[DELETE,INSERT,UPDATE]

AS

T-SQL語(yǔ)句GOWITHENCRYPTION表示加密觸發(fā)器定義的SQL文本DELETE,INSERT,UPDATE指定觸發(fā)器的類(lèi)型13INSERT觸發(fā)器transInfocardIDtransTypetransMoney1001000210010002存入300存入500insertedcardIDtransTypetransMoney10010001

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

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

15-------關(guān)鍵代碼------CREATETRIGGERtrig_transInfoONtransInfo

FORINSERT

ASDECLARE@typechar(4),@outMoneyMONEY,@myCardIDchar(10),@balanceMONEYSELECT@type=transType,@outMoney=transMoney，

@myCardID=cardIDFROMinsertedIF(@type='支取')UPDATEbankSETcurrentMoney=currentMoney-@outMoneyWHEREcardID=@myCardIDELSEUPDATEbankSETcurrentMoney=currentMoney+@outMoneyWHEREcardID=@myCardID…..GOINSERT觸發(fā)器示例3-2從inserted表中獲取交易類(lèi)型、交易金額等根據(jù)交易類(lèi)型，減少或增加對(duì)應(yīng)卡號(hào)的余額16INSERT觸發(fā)器示例3-317transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200DELETE觸發(fā)器transInfocardIDtransTypetransMoney1001000210010002存入300存入500deletedcardIDtransTypetransMoney10010001支取200刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作DELETE觸發(fā)器的工作原理：18問(wèn)題：當(dāng)刪除交易信息表時(shí)，要求自動(dòng)備份被刪除的數(shù)據(jù)到表backupTable中。分析：在交易信息表上創(chuàng)建DELETE觸發(fā)器被刪除的數(shù)據(jù)可以從deleted表中獲取DELETE觸發(fā)器示例3-119-------關(guān)鍵代碼------CREATETRIGGERtrig_delete_transInfoONtransInfoFORDELETEASprint'開(kāi)始備份數(shù)據(jù)，請(qǐng)稍后......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeleted

ELSEINSERTINTObackupTableSELECT*FROMdeletedprint'備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為:'SELECT*FROMbackupTableGO從deleted表中獲取被刪除的交易記錄DELETE觸發(fā)器示例3-220DELETE觸發(fā)器示例3-321Deleted（更新前的數(shù)據(jù)）customerNamecardIDcurrentMoney李四100000021bankcustomerName

cardIDcurrentMoney張三

100100011000

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

ONbank

FORUPDATE

ASDECLARE@beforeMoneyMONEY,@afterMoneyMONEYSELECT@beforeMoney=currentMoneyFROMdeleted

SELECT@afterMoney=currentMoneyFROMinserted

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

ROLLBACKTRANSACTIONENDGO

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

分析：UPDATE（列名）函數(shù)可以檢測(cè)