SQLServer編程之觸發(fā)器

SQLServer2000編程－－觸發(fā)器一、了解觸發(fā)器--創(chuàng)建帳戶信息表bank和交易表transInfoCREATETABLEbank--帳戶信息表(customerNameCHAR(8)NOTNULL,--顧客姓名

cardIDCHAR(10)NOTNULL,--卡號(hào)

currentMoneyMONEYNOTNULL--當(dāng)前余額)CREATETABLEtransInfo--交易信息表(cardIDCHAR(10)NOTNULL,--卡號(hào)

transTypeCHAR(4)NOTNULL,--交易類(lèi)型（存入/支?。?/p>

transMoneyMONEYNOTNULL,--交易金額

transDateDATETIMENOTNULL,--交易日期)一個(gè)典型的應(yīng)用：銀行的取款系統(tǒng)

/*---添加約束：帳戶余額不能少于元，交易日期默認(rèn)為當(dāng)天日期----*/ALTERTABLEbankADDCONSTRAINTCK_currentMoneyCHECK(currentMoney>=1)ALTERTABLEtransInfoADDCONSTRAINTDF_transDateDEFAULT(getDate())FORtransDateGO/*--插入測(cè)試數(shù)據(jù)：張三開(kāi)戶，開(kāi)戶金額為；李四開(kāi)戶，開(kāi)戶金額---*/INSERTINTObank(customerName,cardID,currentMoney)VALUES('張三','10010001',1000)INSERTINTObank(customerName,cardID,currentMoney)VALUES('李四','10010002',1)/*--插入測(cè)試數(shù)據(jù)：張三取錢(qián)---*/INSERTINTOtransInfo(cardID,transType,transMoney)VALUES('10010001','支取',200)--查看結(jié)果SELECT*FROMbankSELECT*FROMtransInfo一個(gè)典型的應(yīng)用：銀行的取款系統(tǒng)帳戶信息表bank

交易信息表transInfo

張三取錢(qián)200

問(wèn)題：沒(méi)有自動(dòng)修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲(chǔ)過(guò)程也具備事務(wù)的功能它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則張三開(kāi)戶1000元,李四開(kāi)戶1元

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

趙二員工表退休員工表觸發(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ā)器2.觸發(fā)器主要提供的功能在數(shù)據(jù)庫(kù)中的相關(guān)表上實(shí)現(xiàn)級(jí)聯(lián)更改事務(wù)功能，撤銷(xiāo)或回滾違反引用完整性的操作，防止非法修改操作執(zhí)行比CHECK約束更復(fù)雜的約束操作比較數(shù)據(jù)修改前后的狀態(tài)大部分觸發(fā)器提供了引用被修改數(shù)據(jù)的能力，這樣就允許用戶在觸發(fā)器中引用正被修改語(yǔ)句所影響的行在一張表的同一類(lèi)型操作（update,insert,delete）上設(shè)置多個(gè)觸發(fā)器，從而可以對(duì)同樣的修改語(yǔ)句執(zhí)行不同的多種操作。3.使用觸發(fā)器的說(shuō)明和特定表關(guān)聯(lián)，自動(dòng)調(diào)用。當(dāng)試圖在某個(gè)表插入、更新或刪除數(shù)據(jù)，而在那個(gè)表上定義了針對(duì)所做動(dòng)作的觸發(fā)器，那么觸發(fā)器會(huì)自動(dòng)執(zhí)行。只有表的擁有者才可以在表上創(chuàng)建或刪除觸發(fā)器，這種權(quán)限不許轉(zhuǎn)授。不能再在視圖或臨時(shí)表上創(chuàng)建觸發(fā)器，但可以在觸發(fā)器中引用視圖或臨時(shí)表。是一個(gè)事務(wù)的部分。如果觸發(fā)器執(zhí)行不成功，則整個(gè)修改事務(wù)回滾。不像普通的存儲(chǔ)過(guò)程，觸發(fā)器不能被直接調(diào)用，也不傳遞或接受參數(shù)當(dāng)使用約束、規(guī)則、默認(rèn)值就可以實(shí)現(xiàn)預(yù)定的數(shù)據(jù)完整性時(shí)，應(yīng)該優(yōu)先使用前3種措施。4.觸發(fā)器的類(lèi)型DELETE觸發(fā)器INSERT觸發(fā)器UPDATE觸發(fā)器觸發(fā)器觸發(fā)發(fā)時(shí)：系統(tǒng)自動(dòng)在在內(nèi)存中創(chuàng)創(chuàng)建deleted表或inserted表只讀，不允允許修改；；觸發(fā)器執(zhí)執(zhí)行完成后后，自動(dòng)刪刪除inserted表表臨時(shí)保存了了插入或更新新后的記錄行可以從inserted表中中檢查插入入的數(shù)據(jù)是是否滿足業(yè)業(yè)務(wù)需求如果不滿足足，則向用用戶報(bào)告錯(cuò)錯(cuò)誤消息，，并回滾操操作deleted表表臨時(shí)保存了了刪除或更新新前的記錄行可以從deleted表中檢檢查被刪除除的數(shù)據(jù)是是否滿足業(yè)業(yè)務(wù)需求如果不滿足足，則向用用戶報(bào)告錯(cuò)錯(cuò)誤消息，，并回滾操操作5.inserted和deletedinserted和和deleted表修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表表和deleted表存放的的信息創(chuàng)建觸發(fā)器器的語(yǔ)法：：二、創(chuàng)建觸觸發(fā)器CREATETRIGGERtrigger_nameONtable_name[WITHENCRYPTION]FOR/AFTER/INSTEADOF[DELETE,INSERT,UPDATE]AST-SQL語(yǔ)句GOWITHENCRYPTION表示示加密觸發(fā)發(fā)器定義的的SQL文文本DELETE,INSERT,UPDATE指定觸觸發(fā)器的類(lèi)類(lèi)型創(chuàng)建觸發(fā)器器FOR和和AFTER是是完全相等等的，創(chuàng)建建相同類(lèi)型型的觸發(fā)器器，在INSERT、UPDATE或或DELETE語(yǔ)句執(zhí)執(zhí)行后觸發(fā)發(fā)INSTEADOF觸發(fā)發(fā)器取消觸觸發(fā)動(dòng)作，，執(zhí)行替代代操作創(chuàng)建觸發(fā)器器后，其信信息插入sysobjects和和syscomments系統(tǒng)表表中SQLServer不允允許在觸發(fā)發(fā)器中使用用下列語(yǔ)句句：ALTERDATABASE、CREATEDATABASE、DISKINIT、、DISKRESIZE、、DROPDATABASE、LOADDATABASE、、LOADLOG、RECONFIGURE、RESTOREDATABASE、RESTORELOG1.INSERT觸發(fā)器transInfocardIDtransTypetransMoney1001000210010002存入

300存入

500insertedcardIDtransTypetransMoney10010001

支取200transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200插入記錄行行觸發(fā)insert觸觸發(fā)器。向向inserted表中插入入新行的副副本觸發(fā)器檢查查inserted表中插入入的新行數(shù)數(shù)據(jù)，確定定是否需要要回滾或執(zhí)執(zhí)行其他操操作INSERT觸發(fā)器的工工作原理：：INSERT觸發(fā)發(fā)器示例問(wèn)題：解決上述的的銀行取款款問(wèn)題：當(dāng)當(dāng)向交易信信息表(transInfo)中插入入一條交易易信息時(shí)，，我們應(yīng)自自動(dòng)更新對(duì)對(duì)應(yīng)帳戶的的余額。帳戶信息表表bank交易信息表表transInfo張三取錢(qián)200問(wèn)題：：沒(méi)有自動(dòng)修修改張三的的余額額張三開(kāi)開(kāi)戶1000元,李四開(kāi)開(kāi)戶1元分析：：在交易易信息息表上上創(chuàng)建建INSERT觸發(fā)器器從inserted臨時(shí)表表中獲獲取插插入的的數(shù)據(jù)據(jù)行根據(jù)交交易類(lèi)類(lèi)型(transType)字段段的值值是存存入/支取取，增加/減少少對(duì)應(yīng)應(yīng)帳戶戶的余余額。。INSERT觸觸發(fā)發(fā)器示示例-------關(guān)鍵鍵代碼碼------CREATETRIGGERtrig_transInfoONtransInfoFORINSERTASDECLARE@typechar(4),@outMoneyMONEYDECLARE@myCardIDchar(10),@balanceMONEYSELECT@type=transType,@outMoney=transMoney，@myCardID=cardIDFROMinsertedIF(@type='支取取')UPDATEbankSETcurrentMoney=currentMoney-@outMoneyWHEREcardID=@myCardIDELSEUPDATEbankSETcurrentMoney=currentMoney+@outMoneyWHEREcardID=@myCardID…..GO從inserted表表中中獲獲取取交交易易類(lèi)類(lèi)型型、、交交易易金金額額等等根據(jù)據(jù)交交易易類(lèi)類(lèi)型型，，減減少少或或增增加加對(duì)對(duì)應(yīng)應(yīng)卡卡號(hào)號(hào)的的余余額額INSERT觸觸發(fā)發(fā)器器示示例例transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取2002.DELETE觸觸發(fā)發(fā)器器transInfocardIDtransTypetransMoney1001000210010002存入300存入500deletedcardIDtransTypetransMoney10010001支取200刪除除記記錄錄行行觸發(fā)發(fā)delete觸觸發(fā)發(fā)器器向向deleted表表中中插插入入被被刪刪除除的的副副本本觸發(fā)發(fā)器器檢檢查查deleted表表中中被被刪刪除除的的數(shù)數(shù)據(jù)據(jù)，，決決定定是是否否需需要要回回滾滾或或執(zhí)執(zhí)行行其其他他操操作作DELETE觸發(fā)發(fā)器器的的工工作作原原理理：：?jiǎn)栴}題：：當(dāng)刪刪除除交交易易信信息息表表時(shí)時(shí)，，要要求求自自動(dòng)動(dòng)備備份份被被刪刪除除的的數(shù)數(shù)據(jù)據(jù)到到表表backupTable中中。。分析析：：在交交易易信信息息表表上上創(chuàng)創(chuàng)建建DELETE觸發(fā)發(fā)器器被刪刪除除的的數(shù)數(shù)據(jù)據(jù)可可以以從從deleted表中中獲獲取取DELETE觸觸發(fā)發(fā)器器示示例例從deleted表表中中獲獲取取被被刪刪除除的的交交易易記記錄錄DELETE觸觸發(fā)發(fā)器器示示例例-------關(guān)關(guān)鍵鍵代代碼碼------CREATETRIGGERtrig_delete_transInfoONtransInfoFORDELETEASprint'開(kāi)開(kāi)始始備備份份數(shù)數(shù)據(jù)據(jù)，，請(qǐng)請(qǐng)稍稍后后......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT*FROMdeletedprint'備備份份數(shù)數(shù)據(jù)據(jù)成成功功，，備備份份表表中中的的數(shù)數(shù)據(jù)據(jù)為為:'SELECT*FROMbackupTableGODELETE觸觸發(fā)發(fā)器器示示例例Deleted（更新前的數(shù)據(jù)）customerNamecardIDcurrentMoney李四100000021bankcustomerNamecardIDcurrentMoney張三

100100011000

李四1000000213.UPDATE觸觸發(fā)發(fā)器器刪除除記記錄錄行行向deleted表表中中插插入入被被刪刪除除的的副副本本檢查查deleted和和inserted表表中中的的數(shù)數(shù)據(jù)據(jù)，，確確定定是是否否需需要要回回滾滾或或執(zhí)執(zhí)行行其其他他操操作作UPDATE觸發(fā)發(fā)器器的的工工作作原原理理：：李四1000000220001向inserted表表中中插插入入被被添添加加的的副副本本Inserted（更新后的數(shù)據(jù)）customerNamecardIDcurrentMoney李四1000000220001插入入記記錄錄行行問(wèn)題題：：跟蹤蹤用用戶戶的的交交易易，，交交易易金金額額超超過(guò)過(guò)20000元元，，則則取取消消交交易易，，并并給給出出錯(cuò)錯(cuò)誤誤提提示示。。分析析：：在bank表表上上創(chuàng)創(chuàng)建建UPDATE觸觸發(fā)發(fā)器器修改改前前的的數(shù)數(shù)據(jù)據(jù)可可以以從從deleted表表中中獲獲取取修改改后后的的數(shù)數(shù)據(jù)據(jù)可可以以從從inserted表表中中獲獲取取UPDATE觸觸發(fā)發(fā)器器示示例例CREATETRIGGERtrig_update_bankONbankFORUPDATEASDECLARE@beforeMoneyMONEY,@afterMoneyMONEYSELECT@beforeMoney=currentMoneyFROMdeletedSELECT@afterMoney=currentMoneyFROMinsertedIFABS(@afterMoney-@beforeMoney)>20000BEGINprint'交易易金額額:'+convert(varchar(8),ABS(@afterMoney-@beforeMoney))RAISERROR('每每筆交交易不不能超超過(guò)2萬(wàn)元元，交交易失失敗',16,1)ROLLBACKTRANSACTIONENDGO從deleted表表中獲獲取交交易前前的余余額，，從inserted表表中獲獲取交交易后后的余余額交易金金額是是否>2萬(wàn)萬(wàn)回滾事事務(wù)，，撤銷(xiāo)銷(xiāo)交易易UPDATE觸觸發(fā)器器-------關(guān)鍵鍵代碼碼------UPDATE觸觸發(fā)器器列級(jí)UPDATE觸觸發(fā)器器UPDATE觸觸發(fā)器器除了了跟蹤蹤數(shù)據(jù)據(jù)的變變化（（修改改）外外，還還可以以檢查查是否否修改改了某某列的的數(shù)據(jù)據(jù)使用UPDATE（（列））函數(shù)數(shù)檢測(cè)測(cè)是否否修改改了某某列問(wèn)題：：交易日日期一一般由由系統(tǒng)統(tǒng)自動(dòng)動(dòng)產(chǎn)生生，默默認(rèn)為為當(dāng)前前日期期。為為了安安全起見(jiàn)，，一般般禁止止修改改，以以防舞舞弊。。分析：：UPDATE（（列名名）函函數(shù)可可以檢檢測(cè)是是否修修改了了某列列-------關(guān)鍵鍵代碼碼------CREATETRIGGERtrig_update_transInfoONtransInfoFORUPDATEASIFUPDATE(transDate)BEGINprint'交易易失敗敗.....'RAISERROR(‘安安全警警告：：交易易日期期不能能修改改，由系統(tǒng)統(tǒng)自動(dòng)動(dòng)產(chǎn)生生',16,1)ROLLBACKTRANSACTIONENDGO檢查是是否修修改了了交易易日期期列transDate回滾事事務(wù)，，撤銷(xiāo)銷(xiāo)交易易列級(jí)UPDATE觸觸發(fā)器器列級(jí)UPDATE觸觸發(fā)器器4.INSTEADOF觸觸發(fā)器器的工工作過(guò)過(guò)程INSTEADOF觸觸發(fā)發(fā)器可以在在表和和視圖圖上定定義INSTEADOF觸觸發(fā)器器。INSTEADOF觸觸發(fā)發(fā)器代代替原原觸發(fā)發(fā)動(dòng)作作執(zhí)行行，增增加了了視圖圖上所所能進(jìn)進(jìn)行的的更新新的種種類(lèi)每個(gè)表表上對(duì)對(duì)每個(gè)個(gè)觸發(fā)發(fā)動(dòng)作作（INSERT、、UPDATE或或DELETE））只能能定義義一個(gè)個(gè)INSTEADOF觸觸發(fā)發(fā)器不能在在具有有WITHCHECKOPTION選選項(xiàng)的的視圖圖上創(chuàng)創(chuàng)建INSTEADOF觸觸發(fā)器器INSTEADOF觸觸發(fā)發(fā)器可可使一一般不不支持持更新新的視視圖可可以被被更新新截獲對(duì)對(duì)視圖圖的操操作，，將其其重導(dǎo)導(dǎo)向底底層表表在INSTEADOFDELETE觸觸發(fā)器器中，，通過(guò)過(guò)deleted表表訪問(wèn)問(wèn)欲刪刪除的的行；；在INSTEADOFUPDATE或或INSTEADOFINSERT觸觸發(fā)發(fā)器中中，通通過(guò)inserted表表訪問(wèn)問(wèn)新增增加的的行INSTEADOF觸觸發(fā)發(fā)器的的工作作過(guò)程程（續(xù)續(xù)）創(chuàng)建結(jié)結(jié)合兩兩個(gè)或或多個(gè)個(gè)表的的視圖圖CREATEVIEWvw_CustomersASSELECT*FROMCustomersMexUNIONSELECT*FROMCustomersGerCustomersMexCustomerIDCompanyNameCountryPhone…ANATRANTONCENTCAnaTrujill…AntonioM…CentroCo…MexicoMexicoMexico(5)555-4729(5)555-3932(5)555-3392~~~~~~~~~CustomersCustomerIDCompanyNameCountryPhone…ALFKIANATRANTONAlfredsFu…AnaTrujill…AntonioM…Germany

MexicoMexic5)555-4729(5)555-3932~~~~~~~~~CustomersGerCustomerIDCompanyNameCountryPhone…ALFKIBLAUSDRACDAlfredsFu…BlauerSe…Drachenb…Germany

GermanyGermany030-00743210621-084600241-039123~~~~~~~~~--創(chuàng)創(chuàng)建觸觸發(fā)器器，將將對(duì)視視圖的的插入入引導(dǎo)導(dǎo)到對(duì)對(duì)基本本表的的插入入CREATETRIGGERtr_CustomersONvw_CustomersINSTEADOFinsertASBEGINinsertintocustomersger(customerid,companyname,country,phone)SELECTi.customerid,panyname,i.country,i.phoneFROMINSERTEDiENDINSTEADOF觸觸發(fā)發(fā)器的的工作作過(guò)程程（續(xù)續(xù)）CustomersMexCustomerIDCompanyNameCountryPhone…ANATRANTONCENTCAnaTrujill…AntonioM…CentroCo…MexicoMexicoMexico(5)555-4729(5)555-3932(5)555-3392~~~~~~~~~CustomersGerCustomerIDCompanyNameCountryPhone…ALFKIBLAUSDRACDAlfredsFu…BlauerSe…Drachenb…Germany

GermanyGermany030-00743210621-084600241-039123~~~~~~~~~INSTEADOF觸觸發(fā)發(fā)器將將插入入導(dǎo)向向基表表CustomersCustomerIDCompanyNameCountryPhone…ALFKIANATRANTONAlfredsFu…AnaTrujill…AntonioM…Germany

MexicoMexic5)555-4729(5)555-3932~~~~~~~~~Insertinto--對(duì)對(duì)視圖圖執(zhí)行行insert操操作作vw_customers(customerid,comanyname,country,phone)Values(‘ALFKI’,’AlfredsFu’’,’’Germany’’,’’)ALFKIAlfredsFu…German~~ALFKIAlfredsFu…German~~三、更更改和和刪除除觸發(fā)發(fā)器更改觸觸發(fā)器器語(yǔ)法：：ALTERTRIGGER觸發(fā)器器名…………使用新新的定定義代代替觸觸發(fā)器器原有有的定定義，，觸發(fā)發(fā)動(dòng)作作也可可以更更改由于延遲名名稱解析，，觸發(fā)器可可以引用觸觸發(fā)器定義義時(shí)不存在在的表，但但是創(chuàng)建觸觸發(fā)器時(shí)會(huì)會(huì)收到一條條警告信息息禁用或啟用用觸發(fā)器語(yǔ)法：ALTERTABLE表名{ENABLE|DISABLE}TRIGGER{ALL|觸發(fā)器名[,…n]}更改和刪除除觸發(fā)器（（續(xù)）刪除觸發(fā)器器語(yǔ)法：DROPTRIGGER觸發(fā)器名若關(guān)聯(lián)表被被刪除，則則觸發(fā)器自自動(dòng)刪除觸發(fā)器是在在對(duì)表進(jìn)行行插入、更更新或刪除除操作時(shí)自自動(dòng)執(zhí)行的的存儲(chǔ)過(guò)程程，觸發(fā)器器通常用于于強(qiáng)制業(yè)務(wù)務(wù)規(guī)則觸發(fā)器還是是一個(gè)特殊殊的事務(wù)單單元，當(dāng)出出現(xiàn)錯(cuò)誤時(shí)時(shí)，可以執(zhí)執(zhí)行ROLLBACKTRANSACTION回滾撤撤銷(xiāo)操作觸發(fā)器一般般都需要使使用臨時(shí)表表：deleted表和inserted表，，它們存放放了被刪除除或插入的的記錄行副副本觸發(fā)器類(lèi)型型：INSERT觸發(fā)器器UPDATE觸發(fā)器器DELETE觸發(fā)器器INSTEADOF總結(jié)使用觸發(fā)器器的考慮事事項(xiàng)大部分觸發(fā)發(fā)器在動(dòng)作作后執(zhí)行，，約束和INSTEADOF觸觸發(fā)器是在在動(dòng)作前執(zhí)執(zhí)行的大部分觸發(fā)發(fā)器在所定定義的表上上執(zhí)行INSERT、UPDATE或DELETE語(yǔ)句句之后執(zhí)行行，而約束束是在語(yǔ)句句執(zhí)行前檢檢查的約束最先被被檢查如果觸發(fā)器器表上存在在約束，則則它們?cè)谟|觸發(fā)器執(zhí)行行之前被檢檢查。如果果違反了約約束，則觸觸發(fā)器不執(zhí)執(zhí)行表對(duì)同一動(dòng)動(dòng)作可以有有多個(gè)觸發(fā)發(fā)器SQLServer2000允允許在單個(gè)個(gè)表上多個(gè)個(gè)觸發(fā)器的的嵌套。一一個(gè)表上可可以有多個(gè)個(gè)觸發(fā)器，，每個(gè)觸發(fā)發(fā)器可以定定義為單個(gè)個(gè)動(dòng)作或多多個(gè)動(dòng)作表的擁有者者可以指定定表上第一一個(gè)和最后后一個(gè)執(zhí)行行的觸發(fā)器器當(dāng)表上有多多