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

這兩個表是邏輯表，并且是由系統管理的，存儲在內存中，不是存儲在數據庫中，因此，不允許用戶直接對其修改。

inserted和deleted表觸發器觸發時：系統自動在內存中創建deleted表或inserted表只讀，不允許修改；觸發器執行完成后，自動刪除inserted表臨時保存了插入或更新后的記錄行可以從inserted表中檢查插入的數據是否滿足業務需求如果不滿足，則向用戶報告錯誤消息，并回滾插入操作deleted表臨時保存了刪除或更新前的記錄行可以從deleted表中檢查被刪除的數據是否滿足業務需求如果不滿足，則向用戶報告錯誤消息，并回滾插入操作inserted和deleted表修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和deleted表存放的信息創建觸發器語法:CREATETRIGGER

trigger_name

ONtable_name

[WITHENCRYPTION]

FOR[DELETE,INSERT,UPDATE]

AS

T-SQL語句GOWITHENCRYPTION表示加密觸發器定義的SQL文本DELETE,INSERT,UPDATE指定觸發器的類型觸發器示例問題:在emp表上創建一個觸發器，該觸發器保證每次最多只能刪除一個雇員

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

語法示例:禁用觸發器emp_delete示例:啟用觸發器emp_deleteALTERTABLEemp{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[，…n]}

ALTERTABLEempDISABLETRIGGERemp_delete

ALTERTABLEempENABLETRIGGERemp_delete

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

DROPTRIGGERemp_deleteINSERT觸發器INSERT觸發器的工作原理：insertedempnoenamesal002

JAK3000empempnoEnamesal001003JONE2000smiths

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

…GODELETE觸發器DELETE觸發器的工作原理：deletedempnoenamesal002

JAK3000empempnoEnamesal001003JONE2000smiths

1500002jAK3000刪除記錄行觸發delete觸發器向deleted表中插入被刪除的副本觸發器檢查deleted表中被刪除的數據，決定是否需要回滾或執行其他操作DELETE觸發器示例-1問題:當刪除雇員時，自動備份被刪除的數據到表backupTable中分析:應在雇員表上創建DELETE觸發器被刪除的數據可以從deleted表中獲取DELETE觸發器示例-1USEempDBGO/*---檢測是否存在，觸發器存放在系統表sysobjects中--------*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname=’trig_delete_emp’)DROPTRIGGERtrig_delete_empGO/*----創建DELETE觸發器：在emp上創建刪除觸發器-----*/CREATETRIGGERtrig_delete_empONempFORDELETEASprint'開始數據備份，請稍侯......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeleted--從deleted表中獲取被刪除的數據

ELSEINSERTINTObackupTableSELECT*FROMdeletedprint'備份數據成功，備份表中的數據為：'SELECT*FROMbackupTableGO/*--測試觸發器：刪除數據--*/SETNOCOUNTON--不顯示T-SQL語句影響的記錄行數DELETEFROMemp--查看結果print‘雇員表中的數據：'SELECT*FROMempUPDATE觸發器UPDATE觸發器的工作原理Deleted（更新前的數據）empnoenamesal002JAK3000empempnoename

sal001

Jone2000

向deleted表中插入被刪除的副本檢查deleted和inserted表中的數據，確定是否需要回滾或執行其他操作向inserted表中插入被添加的副本Inserted（更新后的數據）empnoenamesal002JAK3500插入記錄行002JAK3500UPDATE觸發器示例-1問題:檢測員工加薪比例，如果加薪額度超過其基本工資的20%，則停止加薪，并給出錯誤提示。

分析:在員工表上創建UPDATE觸發器，刪除更改前原有的數據行：刪除的數據轉移到了deleted表中。再插入更改后的新行：插入的數據同時也保存在inserted表中。UPDATE觸發器示例-1--關鍵代碼--CREATETRIGGERtrig_update_empONempFORUPDATEASDECLARE@beforeSalMoney,@afterSalMoney--定義變量

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

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

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

ENDGO列級UPDATE觸發器UPDATE觸發器除了跟蹤數據的變化（修改）外，還可以檢查是否修改了某列的數據使用UPDATE（列）函數檢測是否修改了某列問題：在雇員表(emp)中,雇員號作為主鍵,一般不允許修改。

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

列級UPDATE觸發器示例USEempDBGO/*--創建UPDATE觸發器：在雇員表emp上創建更新（列）觸發器--*/CREATETRIGGERemp_updateONempFORUPDATEASIFUPDATE(empno)BEGINprint'修改失敗....'RAISERROR('****不允許修改雇員號！',16,1)ROLLBACKTRANSACTION--回滾事務，撤消操作ENDGO/*--測試觸發器：修改雇員編號*/--SETNOCOUNTONUPDATEEMPSETEMPNO=‘111’WHEREEMPNO='101'基于視圖的INSTEADOF觸發器可以在表或視圖上定義INSTEADOF觸發器，執行這種觸發器就能夠替代原始的觸發動作INSTEADOF觸發器被用于更新那些沒有辦法通過正常方式更新的視圖INSTEADOF觸發器包含代替原始數據操作語句的代碼。USEempDBGO/*--創建基于兩個表emp和dept聯接的視圖--*/CREATEVIEWemp_deptASSELECTempno,ename,sal,dnameFROMempe,deptdWHEREe.deptno=d.deptno/*--查看視圖數據--*/SELECT*FROMemp_dept/*--測試：試圖從視圖中刪除數據--*/DELETEFROMemp_deptWHEREempno='101'基于視圖的INSTEADOF觸發器要使得能夠對emp_dept視圖進行更新，必須通過INSTEADOF觸發器來刪除數據

示例