《SQL Server 數(shù)據(jù)庫(kù)案例教程》課件第12章觸發(fā)器_第1頁(yè)
《SQL Server 數(shù)據(jù)庫(kù)案例教程》課件第12章觸發(fā)器_第2頁(yè)
《SQL Server 數(shù)據(jù)庫(kù)案例教程》課件第12章觸發(fā)器_第3頁(yè)
《SQL Server 數(shù)據(jù)庫(kù)案例教程》課件第12章觸發(fā)器_第4頁(yè)
《SQL Server 數(shù)據(jù)庫(kù)案例教程》課件第12章觸發(fā)器_第5頁(yè)
已閱讀5頁(yè),還剩22頁(yè)未讀 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說(shuō)明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎn)介

課程內(nèi)容回顧存儲(chǔ)過(guò)程是一組預(yù)編譯的SQL語(yǔ)句,可以包含數(shù)據(jù)操縱語(yǔ)句、邏輯控制語(yǔ)句或數(shù)據(jù)檢索語(yǔ)句。數(shù)據(jù)庫(kù)開發(fā)人員或管理員可以通過(guò)編寫存儲(chǔ)過(guò)程來(lái)運(yùn)行經(jīng)常執(zhí)行的任務(wù)、或者應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則。存儲(chǔ)過(guò)程可提高應(yīng)用程序訪問(wèn)數(shù)據(jù)的速度,幫助實(shí)現(xiàn)模塊化編程,提高數(shù)據(jù)庫(kù)性能和數(shù)據(jù)安全性存儲(chǔ)過(guò)程可分為:系統(tǒng)存儲(chǔ)過(guò)程用戶定義的存儲(chǔ)過(guò)程CREATEPROCEDURE語(yǔ)句用于創(chuàng)建用戶定義的存儲(chǔ)過(guò)程。EXECUTE語(yǔ)句用于運(yùn)行存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù),輸入?yún)?shù)用來(lái)向存儲(chǔ)過(guò)程傳入值,輸出參數(shù)從存儲(chǔ)過(guò)程中返回(輸出)值。RAISERROR語(yǔ)句用來(lái)向用戶報(bào)告錯(cuò)誤本章目標(biāo)了解觸發(fā)器的用途理解觸發(fā)器的工作原理掌握如何使用inserted表和deleted表掌握如何創(chuàng)建INSERT、UPDATE、DELETE觸發(fā)器觸發(fā)器介紹觸發(fā)器是在數(shù)據(jù)庫(kù)中發(fā)生事件時(shí)自動(dòng)執(zhí)行的特殊存儲(chǔ)過(guò)程,這些事件主要是發(fā)生在表上的DML(INSERT,UPDATE,DELETE)操作觸發(fā)器與數(shù)據(jù)操作有關(guān)在數(shù)據(jù)庫(kù)服務(wù)器端實(shí)現(xiàn)業(yè)務(wù)規(guī)則和強(qiáng)制業(yè)務(wù)規(guī)則存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)規(guī)則約束強(qiáng)制業(yè)務(wù)規(guī)則強(qiáng)制業(yè)務(wù)規(guī)則約束觸發(fā)器觸發(fā)器可以實(shí)現(xiàn)比約束更復(fù)雜的數(shù)據(jù)完整性觸發(fā)器的特點(diǎn)觸發(fā)器是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程觸發(fā)器通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行的特點(diǎn):與表相關(guān)聯(lián)自動(dòng)觸發(fā)不能直接調(diào)用是事務(wù)的一部分觸發(fā)器的種類AFTER觸發(fā)器AFTER觸發(fā)器要求只有執(zhí)行某一操作INSERT、UPDATE、DELETE之后觸發(fā)器才被觸發(fā)且只能在表上定義INSTEADOF觸發(fā)器可在表上定義INSTEADOF觸發(fā)器,也可以在視圖上定義INSTEADOF觸發(fā)器AFTER觸發(fā)器AFTER觸發(fā)器包括:INSERT觸發(fā)器:當(dāng)向表中插入數(shù)據(jù)時(shí)觸發(fā),自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句。UPDATE觸發(fā)器:當(dāng)更新表中某列、多列時(shí)觸發(fā),自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句。DELETE觸發(fā)器:當(dāng)刪除表中記錄時(shí)觸發(fā),自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句。觸發(fā)器的兩個(gè)特殊表插入表(inserted表)刪除表(deleted表):

這兩個(gè)表是邏輯表,并且是由系統(tǒng)管理的,存儲(chǔ)在內(nèi)存中,不是存儲(chǔ)在數(shù)據(jù)庫(kù)中,因此,不允許用戶直接對(duì)其修改。

inserted和deleted表觸發(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表修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和deleted表存放的信息創(chuàng)建觸發(fā)器語(yǔ)法:CREATETRIGGER

trigger_name

ONtable_name

[WITHENCRYPTION]

FOR[DELETE,INSERT,UPDATE]

AS

T-SQL語(yǔ)句GOWITHENCRYPTION表示加密觸發(fā)器定義的SQL文本DELETE,INSERT,UPDATE指定觸發(fā)器的類型觸發(fā)器示例問(wèn)題:在emp表上創(chuàng)建一個(gè)觸發(fā)器,該觸發(fā)器保證每次最多只能刪除一個(gè)雇員

USEempDBGO/*--創(chuàng)建觸發(fā)器--*/CREATETRIGGERemp_deleteONempFORDELETEASIF(SELECTCOUNT(*)FROMDeleted)>1BEGINRAISERROR(‘不能刪除多于一個(gè)雇員’,16,1)ROLLBACKTRANSACTIONEND測(cè)試:刪除多于一條記錄,看結(jié)果如何?禁用或啟用觸發(fā)器用戶可以禁用、啟用一個(gè)指定的觸發(fā)器或一個(gè)表的所有觸發(fā)器

語(yǔ)法示例:禁用觸發(fā)器emp_delete示例:啟用觸發(fā)器emp_deleteALTERTABLEemp{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,…n]}

ALTERTABLEempDISABLETRIGGERemp_delete

ALTERTABLEempENABLETRIGGERemp_delete

更改和刪除觸發(fā)器修改觸發(fā)器語(yǔ)法:刪除觸發(fā)器語(yǔ)法:示例:刪除觸發(fā)器emp_deleteALTERTRIGGERTrigger_nameONtable_name[WITHENCRYPTION]FOR{[DELETE,INSERT,UPDATE]}ASSQL語(yǔ)句DROPTRIGGERtrigger_name

DROPTRIGGERemp_deleteINSERT觸發(fā)器INSERT觸發(fā)器的工作原理:insertedempnoenamesal002

JAK3000empempnoEnamesal001003JONE2000smiths

1500002jAK3000插入記錄行觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù),確定是否需要回滾或執(zhí)行其他操作INSERT觸發(fā)器示例-1問(wèn)題:當(dāng)在員工表(emp)中輸入數(shù)據(jù)時(shí),確保輸入的員工工資不超過(guò)5000元人民幣分析:在emp表上創(chuàng)建一個(gè)INSERT觸發(fā)器CheckSal從inserted臨時(shí)表中獲取插入的數(shù)據(jù)行判斷工資的值是否超過(guò)5000INSERT觸發(fā)器示例-1----主要代碼-----CREATETRIGGERCheckSalONempFORINSERTASDECLARE@salmoneySELECT@sal=salFROMinsertedIF@sal>5000BEGINPRINT'工資不能超過(guò)5000'PRINT'請(qǐng)將工資修改為小于5000的值'ROLLBACKTRANSACTIONEND

…GODELETE觸發(fā)器DELETE觸發(fā)器的工作原理:deletedempnoenamesal002

JAK3000empempnoEnamesal001003JONE2000smiths

1500002jAK3000刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù),決定是否需要回滾或執(zhí)行其他操作DELETE觸發(fā)器示例-1問(wèn)題:當(dāng)刪除雇員時(shí),自動(dòng)備份被刪除的數(shù)據(jù)到表backupTable中分析:應(yīng)在雇員表上創(chuàng)建DELETE觸發(fā)器被刪除的數(shù)據(jù)可以從deleted表中獲取DELETE觸發(fā)器示例-1USEempDBGO/*---檢測(cè)是否存在,觸發(fā)器存放在系統(tǒng)表sysobjects中--------*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname=’trig_delete_emp’)DROPTRIGGERtrig_delete_empGO/*----創(chuàng)建DELETE觸發(fā)器:在emp上創(chuàng)建刪除觸發(fā)器-----*/CREATETRIGGERtrig_delete_empONempFORDELETEASprint'開始數(shù)據(jù)備份,請(qǐng)稍侯......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeleted--從deleted表中獲取被刪除的數(shù)據(jù)

ELSEINSERTINTObackupTableSELECT*FROMdeletedprint'備份數(shù)據(jù)成功,備份表中的數(shù)據(jù)為:'SELECT*FROMbackupTableGO/*--測(cè)試觸發(fā)器:刪除數(shù)據(jù)--*/SETNOCOUNTON--不顯示T-SQL語(yǔ)句影響的記錄行數(shù)DELETEFROMemp--查看結(jié)果print‘雇員表中的數(shù)據(jù):'SELECT*FROMempUPDATE觸發(fā)器UPDATE觸發(fā)器的工作原理Deleted(更新前的數(shù)據(jù))empnoenamesal002JAK3000empempnoename

sal001

Jone2000

向deleted表中插入被刪除的副本檢查deleted和inserted表中的數(shù)據(jù),確定是否需要回滾或執(zhí)行其他操作向inserted表中插入被添加的副本Inserted(更新后的數(shù)據(jù))empnoenamesal002JAK3500插入記錄行002JAK3500UPDATE觸發(fā)器示例-1問(wèn)題:檢測(cè)員工加薪比例,如果加薪額度超過(guò)其基本工資的20%,則停止加薪,并給出錯(cuò)誤提示。

分析:在員工表上創(chuàng)建UPDATE觸發(fā)器,刪除更改前原有的數(shù)據(jù)行:刪除的數(shù)據(jù)轉(zhuǎn)移到了deleted表中。再插入更改后的新行:插入的數(shù)據(jù)同時(shí)也保存在inserted表中。UPDATE觸發(fā)器示例-1--關(guān)鍵代碼--CREATETRIGGERtrig_update_empONempFORUPDATEASDECLARE@beforeSalMoney,@afterSalMoney--定義變量

SELECT@beforeSal=salFROMdeleted--獲取加薪前的工資

SELECT@afterSal=salFROMinserted--獲取加薪后的工資

select@afterSal,@beforeSalIFABS(@afterSal-@beforeSal)>=@beforeSal*0.2--判斷加薪額度是否超過(guò)%BEGINprint'加薪額度:'+convert(varchar(8),ABS(@afterSal-@beforeSal))RAISERROR('加薪額度不能超過(guò)其工資的百分之二十,加薪失敗',16,1)ROLLBACKTRANSACTION--回滾事務(wù),撤消加薪

ENDGO列級(jí)UPDATE觸發(fā)器UPDATE觸發(fā)器除了跟蹤數(shù)據(jù)的變化(修改)外,還可以檢查是否修改了某列的數(shù)據(jù)使用UPDATE(列)函數(shù)檢測(cè)是否修改了某列問(wèn)題:在雇員表(emp)中,雇員號(hào)作為主鍵,一般不允許修改。

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

列級(jí)UPDATE觸發(fā)器示例USEempDBGO/*--創(chuàng)建UPDATE觸發(fā)器:在雇員表emp上創(chuàng)建更新(列)觸發(fā)器--*/CREATETRIGGERemp_updateONempFORUPDATEASIFUPDATE(empno)BEGINprint'修改失敗....'RAISERROR('****不允許修改雇員號(hào)!',16,1)ROLLBACKTRANSACTION--回滾事務(wù),撤消操作ENDGO/*--測(cè)試觸發(fā)器:修改雇員編號(hào)*/--SETNOCOUNTONUPDATEEMPSETEMPNO=‘111’WHEREEMPNO='101'基于視圖的INSTEADOF觸發(fā)器可以在表或視圖上定義INSTEADOF觸發(fā)器,執(zhí)行這種觸發(fā)器就能夠替代原始的觸發(fā)動(dòng)作INSTEADOF觸發(fā)器被用于更新那些沒(méi)有辦法通過(guò)正常方式更新的視圖INSTEADOF觸發(fā)器包含代替原始數(shù)據(jù)操作語(yǔ)句的代碼。USEempDBGO/*--創(chuàng)建基于兩個(gè)表emp和dept聯(lián)接的視圖--*/CREATEVIEWemp_deptASSELECTempno,ename,sal,dnameFROMempe,deptdWHEREe.deptno=d.deptno/*--查看視圖數(shù)據(jù)--*/SELECT*FROMemp_dept/*--測(cè)試:試圖從視圖中刪除數(shù)據(jù)--*/DELETEFROMemp_deptWHEREempno='101'基于視圖的INSTEADOF觸發(fā)器要使得能夠?qū)mp_dept視圖進(jìn)行更新,必須通過(guò)INSTEADOF觸發(fā)器來(lái)刪除數(shù)據(jù)

示例

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

評(píng)論

0/150

提交評(píng)論