課程內(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ù)

示例