1、SQL Server 2000編程觸發器一、了了解觸觸發器-創建帳戶戶信息表表bank和交易表表transInfoCREATETABLEbank-帳戶信息息表(customerNameCHAR(8)NOTNULL,-顧客姓名名cardIDCHAR(10) NOTNULL ,-卡號currentMoneyMONEYNOTNULL-當前余額額)CREATETABLEtransInfo-交易信息息表(cardIDCHAR(10) NOTNULL,-卡號transTypeCHAR(4)NOT NULL,-交易類型型（存入入/支取）transMoneyMONEYNOTNULL,-交易金額額transDa

2、teDATETIME NOTNULL,-交易日期期)一個典型型的應用用：銀行行的取款款系統/*-添加約束束：帳戶戶余額不不能少于于元，交交易日期期默認為為當天日日期-*/ALTERTABLEbankADDCONSTRAINTCK_currentMoneyCHECK(currentMoney=1)ALTERTABLEtransInfoADDCONSTRAINTDF_transDateDEFAULT(getDate()FORtransDateGO/*-插入測試試數據：張三開開戶，開開戶金額額為；李李四開戶戶，開戶戶金額-*/INSERTINTObank(customerName,cardID,cu

3、rrentMoney)VALUES(張三,1001 0001,1000)INSERTINTObank(customerName,cardID,currentMoney)VALUES(李四,1001 0002,1)/*-插入測試試數據：張三取取錢-*/INSERTINTOtransInfo(cardID,transType,transMoney)VALUES(10010001,支取,200)-查看結果果SELECT*FROM bankSELECT*FROM transInfo一個典型型的應用用：銀行行的取款款系統帳戶信息息表bank交易信息息表transInfo張三取錢錢200問題：沒有自動修改

4、改張三的余余額最優的解解決方案案就是采采用觸發器：它是一種種特殊的的存儲過過程也具備事事務的功功能它能在多表之間執行行特殊的的業務規規則張三開戶戶1000元,李四開戶戶1元張三李四王五趙二王三宋二劉五插入1.什么么是觸發發器刪除觸發器觸觸發趙二退休休趙二員工表退休員工工表觸發器是是在對表表進行插插入、更更新或刪刪除操作作時自動執行行的存儲儲過程觸發器通通常用于于強制業業務規則則觸發器是是一種高級約束束，可以定定義比用用CHECK約約束更更為復雜雜的約束束可執行復復雜的SQL語語句（if/while/case）可引用其其它表中中的列什么是觸觸發器2.觸發發器主要要提供的的功能在數據庫庫中的相相關

5、表上上實現級級聯更改改事務功能能，撤銷銷或回滾滾違反引引用完整整性的操操作，防防止非法法修改操操作執行比CHECK約約束更更復雜的的約束操操作比較數據據修改前前后的狀狀態大部分觸觸發器提提供了引引用被修修改數據據的能力力，這樣樣就允許許用戶在在觸發器器中引用用正被修修改語句句所影響響的行在一張表表的同一一類型操操作（update,insert,delete）上上設置多多個觸發發器，從從而可以以對同樣樣的修改改語句執執行不同同的多種種操作。3.使用用觸發器器的說明明和特定表表關聯，自動調調用。當試圖在在某個表表插入、更新或或刪除數數據，而而在那個個表上定定義了針針對所做做動作的的觸發器器，那么么

6、觸發器器會自動動執行。只有表的的擁有者者才可以以在表上上創建或或刪除觸觸發器，這種權權限不許許轉授。不能再在在視圖或或臨時表表上創建建觸發器器，但可可以在觸觸發器中中引用視視圖或臨臨時表。是一個事事務的部部分。如如果觸發發器執行行不成功功，則整整個修改改事務回回滾。不像普通通的存儲儲過程，觸發器器不能被被直接調調用，也也不傳遞遞或接受受參數當使用約約束、規規則、默默認值就就可以實實現預定定的數據據完整性性時，應應該優先先使用前前3種措措施。4.觸發發器的類類型DELETE觸觸發器器INSERT觸觸發器器UPDATE觸觸發器器觸發器觸觸發時：系統自動動在內存存中創建建deleted表或或inse

7、rted表表只讀，不不允許修修改；觸觸發器執執行完成成后，自自動刪除除inserted表表臨時保存存了插入或更更新后的記錄行行可以從inserted表中中檢查插插入的數數據是否否滿足業業務需求求如果不滿滿足，則則向用戶戶報告錯錯誤消息息，并回回滾操作作deleted 表臨時保存存了刪除或更更新前的記錄行行可以從deleted表中檢檢查被刪刪除的數數據是否否滿足業業務需求求如果不滿滿足，則則向用戶戶報告錯錯誤消息息，并回回滾操作作5.inserted 和deletedinserted和和deleted表表修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄-刪除(DE

8、LETE)記錄-存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和和deleted表存存放的信信息創建觸發發器的語語法：二、創建建觸發器器CREATETRIGGERtrigger_nameONtable_nameWITHENCRYPTIONFOR/AFTER/INSTEADOFDELETE, INSERT,UPDATEAST-SQL語句句GOWITH ENCRYPTION表表示加密密觸發器器定義的的SQL文本DELETE, INSERT,UPDATE指定觸觸發器的的類型創建觸發發器FOR和和AFTER是是完全相相等的，創建相相同類型型的觸發發器，在在I

9、NSERT、UPDATE或或 DELETE語語句執行行后觸發發INSTEAD OF 觸發發器取消消觸發動動作，執執行替代代操作創建觸發發器后，其信息息插入sysobjects和和 syscomments 系統統表中SQLServer 不允允許在觸觸發器中中使用下下列語句句：ALTERDATABASE、CREATEDATABASE、DISKINIT、DISKRESIZE、DROPDATABASE、LOADDATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORELOG1.INSERT觸觸發器transInfocardIDtransType tra

10、nsMoney1001 00021001 0002存入 300存入 500insertedcardIDtransType transMoney1001 0001 支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 5001001 0001支取 200插入記錄錄行觸發insert觸發發器。向向inserted表表中插入入新行的的副本觸發器檢檢查inserted表中插插入的新新行數據據，確定定是否需需要回滾滾或執行行其他操操作INSERT觸發器的的工作原原理：INSERT觸觸發器器示例問題：解決上述述的銀行行取款問

11、問題：當當向交易易信息表表(transInfo)中中插入一一條交易易信息時時，我們們應自動動更新對對應帳戶戶的余額額。帳戶信息息表bank交易信息息表transInfo張三取錢錢200問題：沒有自動修改改張三的余余額張三開戶戶1000元,李四開戶戶1元分析：在交易信信息表上上創建INSERT觸發器從inserted臨時表中中獲取插插入的數數據行根據交易易類型(transType)字段的的值是存存入/支支取，增加/減減少對應應帳戶的的余額。INSERT觸觸發器器示例-關鍵代代碼-CREATETRIGGERtrig_transInfoONtransInfoFORINSERTASDECLARE ty

12、pe char(4),outMoney MONEYDECLARE myCardIDchar(10),balanceMONEYSELECTtype=transType,outMoney=transMoney，myCardID=cardID FROMinsertedIF(type=支取取)UPDATEbankSETcurrentMoney=currentMoney-outMoneyWHERE cardID=myCardIDELSEUPDATEbankSETcurrentMoney =currentMoney +outMoneyWHEREcardID=myCardID.GO 從inserted表表中

13、獲取取交易類類型、交交易金額額等根據交易易類型，減少或或增加對對應卡號號的余額額INSERT觸觸發器器示例transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 5001001 0001支取 2002.DELETE觸觸發器transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500deletedcardIDtransType transMoney1001 0001支取 200刪除記錄錄行觸發delete觸發發器向deleted表中插插入被刪刪除的副副本觸發器

14、檢檢查deleted表表中被刪刪除的數數據，決決定是否否需要回回滾或執執行其他他操作DELETE觸發器的的工作原原理：問題：當刪除交交易信息息表時，要求自自動備份份被刪除除的數據據到表backupTable中。分析：在交易信信息表上上創建DELETE觸發器被刪除的的數據可可以從deleted表中獲取取DELETE觸觸發器示示例從deleted表中中獲取被被刪除的的交易記記錄DELETE觸觸發器示示例-關鍵代代碼-CREATETRIGGERtrig_delete_transInfoONtransInfoFORDELETEASprint開始備備份數據據，請稍稍后.IFNOTEXISTS(SELEC

15、T* FROMsysobjectsWHEREname=backupTable)SELECT*INTO backupTable FROMdeletedELSEINSERTINTObackupTableSELECT*FROMdeletedprint備份數數據成功功，備份份表中的的數據為為:SELECT*FROM backupTableGODELETE觸觸發器示示例Deleted（更新前的數據）customerNamecardID currentMoney李四1000 0002 1bankcustomerName cardID currentMoney張三 10010001 1000 李四10000

16、002 13.UPDATE觸觸發器刪除記錄錄行向deleted表中中插入被被刪除的的副本檢查deleted和和inserted表表中的數數據，確確定是否否需要回回滾或執執行其他他操作UPDATE觸發器的的工作原原理：李四 10000002 20001向inserted表表中插入入被添加加的副本本Inserted（更新后的數據）customerNamecardID currentMoney李四1000 0002 20001插入記錄錄行問題：跟蹤用戶戶的交易易，交易易金額超超過20000元，則則取消交交易，并并給出錯錯誤提示示。分析：在bank表上上創建UPDATE觸觸發器修改前的的數據可可以從d

17、eleted表中獲獲取修改后的的數據可可以從inserted表中中獲取UPDATE觸觸發器示示例CREATETRIGGERtrig_update_bankONbankFORUPDATEASDECLARE beforeMoneyMONEY,afterMoneyMONEYSELECTbeforeMoney=currentMoneyFROMdeletedSELECTafterMoney=currentMoney FROMinsertedIFABS(afterMoney-beforeMoney)20000BEGINprint交易金金額:+convert(varchar(8),ABS(afterMone

18、y-beforeMoney)RAISERROR(每每筆交易易不能超超過2萬萬元，交交易失敗敗,16,1)ROLLBACKTRANSACTIONENDGO從deleted表中中獲取交交易前的的余額，從inserted表中獲獲取交易易后的余余額交易金額額是否2萬回滾事務務，撤銷銷交易UPDATE觸觸發器-關鍵代代碼-UPDATE觸觸發器列級UPDATE觸觸發器器UPDATE觸觸發器除除了跟蹤蹤數據的的變化（修改）外，還還可以檢檢查是否否修改了了某列的的數據使用UPDATE（列列）函數數檢測是是否修改改了某列列問題：交易日期期一般由由系統自自動產生生，默認認為當前前日期。為了安安全起見，一一般禁止止

19、修改，以防舞舞弊。分析：UPDATE（列名）函數可可以檢測測是否修修改了某某列-關鍵代代碼-CREATETRIGGERtrig_update_transInfoONtransInfoFORUPDATEASIFUPDATE(transDate)BEGINprint交易失失敗.RAISERROR(安安全警告告：交易易日期不不能修改改，由系統自自動產生生,16,1)ROLLBACKTRANSACTIONENDGO 檢查是否否修改了了交易日日期列transDate回滾事務務，撤銷銷交易列級UPDATE觸觸發器器列級UPDATE觸觸發器器4.INSTEAD OF 觸發發器的工工作過程程INSTEAD O

20、F 觸發發器可以在表表和視圖圖上定義義 INSTEADOF觸觸發器器。INSTEADOF觸觸發器器代替原原觸發動動作執行行，增加加了視圖圖上所能能進行的的更新的的種類每個表上上對每個個觸發動動作（INSERT、UPDATE 或DELETE）只能能定義一一個INSTEAD OF 觸發發器不能在具具有WITH CHECK OPTION選選項的視視圖上創創建INSTEAD OF 觸發發器INSTEAD OF 觸發發器可使使一般不不支持更更新的視視圖可以以被更新新截獲對視視圖的操操作，將將其重導導向底層層表在 INSTEADOFDELETE 觸發發器中，通過deleted表表訪問欲欲刪除的的行；在在

21、INSTEADOFUPDATE 或INSTEADOFINSERT觸觸發器器中，通通過inserted表表訪問新新增加的的行INSTEAD OF 觸發發器的工工作過程程（續）創建結合合兩個或或多個表表的視圖圖CREATEVIEWvw_CustomersASSELECT*FROM CustomersMexUNIONSELECT*FROM CustomersGerCustomersMexCustomerIDCompanyNameCountryPhoneANATRANTONCENTCAna TrujillAntonio MCentro CoMexicoMexicoMexico(5) 555-4729(

22、5) 555-3932(5) 555-3392CustomersCustomerIDCompanyNameCountryPhoneALFKIANATRANTONAlfreds FuAna TrujillAntonio MGermanyMexicoMexic5) 555-4729(5) 555-3932CustomersGerCustomerIDCompanyNameCountryPhoneALFKIBLAUSDRACDAlfreds FuBlauer SeDrachenbGermanyGermanyGermany030-00743210621-084600241-03

23、9123-創建建觸發器器，將對對視圖的的插入引引導到對對基本表表的插入入CREATETRIGGERtr_CustomersONvw_CustomersINSTEAD OF insertASBEGINinsertintocustomersger(customerid,companyname,country,phone)SELECTi.customerid,panyname,i.country,i.phoneFROM INSERTED iENDINSTEAD OF 觸發發器的工工作過程程（續）CustomersMexCustomerIDCompanyNameCountryPhoneANATRANT

24、ONCENTCAna TrujillAntonio MCentro CoMexicoMexicoMexico(5) 555-4729(5) 555-3932(5) 555-3392CustomersGerCustomerIDCompanyNameCountryPhoneALFKIBLAUSDRACDAlfreds FuBlauer SeDrachenbGermanyGermanyGermany030-00743210621-084600241-039123INSTEAD OF 觸發發器將插插入導向向基表CustomersCustomerIDCompanyNameCountryPhoneALFK

25、IANATRANTONAlfreds FuAna TrujillAntonio MGermanyMexicoMexic5) 555-4729(5) 555-3932Insertinto-對視視圖執行行 insert操操作vw_customers(customerid,comanyname,country,phone)Values(ALFKI,AlfredsFu,GermanyALFKI Alfreds FuGermanLFKIAlfreds FuGerman、更改改和刪除除觸發器器更改觸發發

26、器語法：ALTERTRIGGER觸發器名名 使用新的的定義代代替觸發發器原有有的定義義，觸發發動作也也可以更更改由于延遲遲名稱解解析，觸觸發器可可以引用用觸發器器定義時時不存在在的表，但是創創建觸發發器時會會收到一一條警告告信息禁用或啟啟用觸發發器語法：ALTERTABLE表名 ENABLE| DISABLETRIGGER ALL|觸發器名名,n 更改和刪刪除觸發發器（續續）刪除觸發發器語法：DROP TRIGGER觸發器名名若關聯表表被刪除除，則觸觸發器自自動刪除除觸發器是是在對表表進行插插入、更更新或刪刪除操作作時自動動執行的的存儲過過程，觸觸發器通通常用于于強制業業務規則則觸發器還還是一

27、個個特殊的的事務單單元，當當出現錯錯誤時，可以執執行ROLLBACK TRANSACTION回滾撤撤銷操作作觸發器一一般都需需要使用用臨時表表：deleted表表和inserted表，它它們存放放了被刪刪除或插插入的記記錄行副副本觸發器類類型：INSERT觸觸發器UPDATE觸觸發器DELETE觸觸發器INSTEAD OF總結使用觸發發器的考考慮事項項大部分觸觸發器在在動作后后執行，約束和和 INSTEADOF觸觸發器器是在動動作前執執行的大部分觸觸發器在在所定義義的表上上執行INSERT、UPDATE或或 DELETE語語句之后后執行，而約束束是在語語句執行行前檢查查的約束最先先被檢查查如果觸發發器表上上存在約約束，則則它們在在觸發器器執行之之前被檢檢查。如如果違反反了約束束，則觸觸發器不不執行表對同一一動作可