1

管理觸發(fā)器2觸發(fā)器是一種特殊類型的存儲過程，它不同于普通的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱而被直接調(diào)用。當(dāng)往某一個表格中插入記錄、修改記錄或者刪除記錄時，SQLSERVER就會自動執(zhí)行觸發(fā)器所定義的SQL語句，以確保數(shù)據(jù)的完整性。觸發(fā)器的基本概念3SQLSERVER可以檢查數(shù)據(jù)完整性的還有什么情況？比如前面講過的CHECK約束。但是，與CHECK約束相比，觸發(fā)器可以強制實現(xiàn)更加復(fù)雜的數(shù)據(jù)完整性，而且可以參考其他表的字段。注意：觸發(fā)器可以擴展SQLServer約束、默認(rèn)值和規(guī)則的完整性檢查邏輯，但只要約束和默認(rèn)值提供了全部所需的功能，就應(yīng)使用約束和默認(rèn)值。4觸發(fā)器的作用觸發(fā)器可以使用T-SQL語句進行復(fù)雜的邏輯處理，它基于一個表創(chuàng)建，但是可以對多個表進行操作，因此常常用于復(fù)雜的業(yè)務(wù)規(guī)則。一般可以使用觸發(fā)器完成如下功能：5觸發(fā)器的作用1、級聯(lián)修改數(shù)據(jù)庫中相關(guān)的表。例如：在數(shù)據(jù)庫company中有兩個表project和customer，表project中包含項目信息和項目的合作客戶名，而customer表中則存儲所有客戶的信息。如果刪除了customer表中的某一客戶，如張三，那么在project表中所有和該客戶有關(guān)的項目記錄都應(yīng)該做相應(yīng)的調(diào)整。如在project表中將所有合作客戶為張三的字段改為null或者其它值，或者刪除整條和張三有關(guān)的記錄。而使用觸發(fā)器就可以實現(xiàn)這樣的功能。6觸發(fā)器的作用2、執(zhí)行比核查約束更為復(fù)雜的約束操作。在觸發(fā)器中可以書寫更復(fù)雜的T-SQL語句，例如可以引用多個表，并使用if…else等語句做更復(fù)雜的檢查。3、拒絕或回滾違反引用完整性的操作。檢查對數(shù)據(jù)表的操作是否違反引用完整性，并選擇相應(yīng)的操作。7觸發(fā)器的作用4、比較表修改前后數(shù)據(jù)之間的差別，并根據(jù)差別采取相應(yīng)的操作。例如：若想規(guī)定每次工資的變動幅度不能超過40％，使用觸發(fā)器可以將修改后的表數(shù)據(jù)和修改前的表數(shù)據(jù)進行比較，若超出40%，可以回滾該修改操作。8觸發(fā)器的類型觸發(fā)器可以分為五種類型：Update觸發(fā)器：在表上進行更新操作時觸發(fā)。Insert觸發(fā)器：在表上進行更新操作時觸發(fā)Delete觸發(fā)器：在表上進行更新操作時觸發(fā)Insteadof觸發(fā)器：不執(zhí)行插入、更新或刪除操作時，將觸發(fā)Insteadof觸發(fā)器。After觸發(fā)器：在一個觸發(fā)動作發(fā)生之后激發(fā)，并提供一種機制以便控制多個觸發(fā)器的執(zhí)行順序。9SQLServer2000新增的觸發(fā)器SQLServer2000新增了兩種類型的觸發(fā)器AFTER

觸發(fā)器和INSTEADOF

觸發(fā)器。其中AFTER要求只有執(zhí)行某一操作（INSERT、UPDATE、DELETE）之后觸發(fā)器才被觸發(fā)，且只能在表上定義。可以為針對表的同一操作定義多個觸發(fā)器。對于AFTER

觸發(fā)器，可以定義哪一個觸發(fā)器被最先觸發(fā)，哪一個被最后觸發(fā)，通常使用系統(tǒng)過程sp_settriggerorder

來完成此任務(wù)。10INSTEADOF

觸發(fā)器表示并不執(zhí)行其所定義的操作（INSERT、UPDATE、DELETE），而僅是執(zhí)行觸發(fā)器本身。既可在表上定義INSTEADOF

觸發(fā)器，也可以在視圖上定義INSTEADOF

觸發(fā)器，但對同一操作只能定義一個INSTEADOF

觸發(fā)器。11觸發(fā)器的主要優(yōu)點觸發(fā)器是自動的：當(dāng)對表中的數(shù)據(jù)作了任何修改（比如手工輸入或者應(yīng)用程序采取的操作）之后立即被激活。觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進行層疊更改。觸發(fā)器可以強制限制，這些限制比用CHECK約束所定義的更復(fù)雜。而且比使用核查約束更為方便的時，觸發(fā)器可以引用其它數(shù)據(jù)表中的列。12創(chuàng)建觸發(fā)器的規(guī)則和限制在創(chuàng)建和使用觸發(fā)器時，需要遵循下列規(guī)則：CreateTrigger語句必須是批處理中的第一個語句，且該批處理中隨后出現(xiàn)的其它所有語句都將被解釋為CreateTrigger語句定義的一部分。在默認(rèn)情況下，創(chuàng)建觸發(fā)器的權(quán)限將分配給數(shù)據(jù)表的所有者，且不能將該權(quán)限轉(zhuǎn)給其它用戶。觸發(fā)器是數(shù)據(jù)庫對象，其名稱必須遵循標(biāo)識符的命名規(guī)則。雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。13雖然不能在臨時數(shù)據(jù)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時數(shù)據(jù)表。既不能在系統(tǒng)數(shù)據(jù)表創(chuàng)建觸發(fā)器，也不可以引用系統(tǒng)數(shù)據(jù)表在包含使用delete或update操作定義的外鍵的表中，不能定義insteadof和insteadofupdate觸發(fā)器。雖然TRUNCATETABLE語句類似于沒有WHERE子句（用于刪除行）的DELETE語句，但它并不會引發(fā)DELETE觸發(fā)器，因為TRUNCATETABLE語句沒有被記入日志。Writetext語句不會引發(fā)insert或update觸發(fā)器。14下面的語句不可以用于創(chuàng)建觸發(fā)器：AlterDatabase、CreateDatabase、DiskInit、DiskResize、DropDatabase、LoadDatabase、LoadLog、Reconfigure、RestoreDatabase、RestoreLog。當(dāng)創(chuàng)建一個觸發(fā)器時必須指定：⑴名稱；⑵在其上定義觸發(fā)器的表；⑶觸發(fā)器將何時激發(fā)；⑷激活觸發(fā)器的數(shù)據(jù)修改語句。15使用企業(yè)管理器管理觸發(fā)器創(chuàng)建觸發(fā)器修改觸發(fā)器刪除觸發(fā)器161、打開企業(yè)管理器中，然后展開要在其上創(chuàng)建觸發(fā)器的表。2、在表上單擊鼠標(biāo)右鍵，然后在彈出菜單中選擇命令“所有任務(wù)－管理觸發(fā)器”。3、在“觸發(fā)器屬性”對話框內(nèi)的名稱欄中選擇“新建”4、在“文本”文本框中輸入創(chuàng)建觸發(fā)器的T-SQL語句。5、按下窗口中的“語法檢查”按鈕，若有錯誤，修改文本框中的SQL語句，若沒有錯誤，則彈出“語法檢查成功！”對話框。6、單擊“語法檢查成功”對話框中的“確定”按鈕，關(guān)閉“觸發(fā)器屬性”對話框，即完成。創(chuàng)建觸發(fā)器17修改觸發(fā)器1、打開企業(yè)管理器，并展開需要修改觸發(fā)器的表所在的數(shù)據(jù)庫節(jié)點，然后選擇將要修改的觸發(fā)器的表。2、在表上單擊鼠標(biāo)右鍵，然后在彈出菜單中選擇命令“所有認(rèn)為－管理觸發(fā)器”3、在“觸發(fā)器屬性”對話框中選擇需要修改的觸發(fā)器名4、在“文本”文本框中對觸發(fā)器定義語句進行修改。直到語法無錯誤為止。5、關(guān)閉“觸發(fā)器屬性”對話框，修改完畢。18刪除數(shù)據(jù)庫1、打開企業(yè)管理器，并展開需要修改觸發(fā)器的表所在的數(shù)據(jù)庫節(jié)點，然后選擇將要修改的觸發(fā)器的表。2、在表上單擊鼠標(biāo)右鍵，然后在彈出菜單中選擇命令“所有認(rèn)為－管理觸發(fā)器”3、在“觸發(fā)器屬性”對話框中選擇需要刪除的觸發(fā)器名4、單擊對話框中的“刪除”按鈕。5、在“確定要刪除觸發(fā)器嗎？”對話框中單擊“確定”按鈕，確認(rèn)刪除。19使用T-SQL管理觸發(fā)器創(chuàng)建觸發(fā)器查看觸發(fā)器相關(guān)數(shù)據(jù)修改觸發(fā)器刪除觸發(fā)器20創(chuàng)建觸發(fā)器其語法形式如下：CREATETRIGGERtrigger_name

ONtable|view

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS

sql_statements[…n]

各參數(shù)說明如下：

21各參數(shù)的說明如下：trigger_name：是用戶要創(chuàng)建的觸發(fā)器的名字。觸發(fā)器的名字必須符合MSSQLServer的命名規(guī)則，且其名字在當(dāng)前數(shù)據(jù)庫中必須是惟一的Table：是與用戶創(chuàng)建的觸發(fā)器相關(guān)聯(lián)的表的名字，并且該表已經(jīng)存在。WITHENCRYPTION：表示對包含有CREATETRIGGER文本的syscomments表進行加密[DELETE][,][INSERT][,][UPDATE]：關(guān)鍵字用來指明哪種數(shù)據(jù)操作將激活觸發(fā)器，至少要指明一個選項，在觸發(fā)器的定義中三者的順序不受限制，且各選項要用逗號隔開。22AFTER：表示只有在執(zhí)行了指定的操作INSERT、DELETE、UPDATE之后觸發(fā)器才被激活，執(zhí)行觸發(fā)器中的SQL語句。若使用關(guān)鍵字FOR，則表示為AFTER觸發(fā)器，且該類型觸發(fā)器僅能在表上創(chuàng)建。INSTEADOF：當(dāng)為表或視圖定義了針對某一操作INSERT、DELETE、UPDATE的INSTEADOF類型觸發(fā)器，且執(zhí)行了相應(yīng)的操作時，盡管觸發(fā)器被觸發(fā)，但相應(yīng)的操作并不被執(zhí)行，而運行的僅是觸發(fā)器SQL語句本身。23例子UsecompanyGo--創(chuàng)建觸發(fā)器Createtrigger觸發(fā)器_歡迎新員工OnemployeeAfterinsertAsPrint‘新員工加入本公司，歡迎歡迎！’go24--創(chuàng)建觸發(fā)器Createtrigger觸發(fā)器_員工走了OnemployeeAfterdeleteAsPrint‘真遺憾你離開公司！’go25Setnocounton--插入一條記錄試試觸發(fā)器操作Insertintoemployee(員工id,姓名，性別，工資)Values(20068,’趙一’,‘男’,1000)--刪除一條記錄試試DeletefromemployeeWhere員工id=‘20067’go26查看觸發(fā)器相關(guān)數(shù)據(jù)1、查看表中存在哪些相關(guān)觸發(fā)器使用企業(yè)管理器查看：打開企業(yè)管理器，在表上單擊鼠標(biāo)右鍵，然后選擇命令“所有任務(wù)－管理觸發(fā)器”，則在“觸發(fā)器屬性”窗口可以看到新建的觸發(fā)器。27查看觸發(fā)器相關(guān)數(shù)據(jù)使用系統(tǒng)存儲過程sp_helptrigger：語法如下：execsp_helptrigger‘table’[,’type’]其中：table：觸發(fā)器所在的表名Type：指定列出的操作類型的觸發(fā)器。若不指定，則列出所有的觸發(fā)器。例如：execsp_helptrigger‘employee’28查看觸發(fā)器的定義文本使用企業(yè)管理器查看：在企業(yè)管理器的“觸發(fā)器屬性”窗口查看，也可以打開系統(tǒng)表syscomments查看。29查看觸發(fā)器的定義文本觸發(fā)器的定義文本存儲在系統(tǒng)表syscomments中，但是可以通過執(zhí)行系統(tǒng)存儲過程sp_helptext進行查看，執(zhí)行該存儲過程的語法格式如下：Sp_helptext‘trigger_name’例如：execsp_helptext‘觸發(fā)器_歡迎新員工’注意：若在定義觸發(fā)器時對定義文本進行加密處理，則不能使用任何方法查看觸發(fā)器的定義文本。30查看觸發(fā)器的所有者和創(chuàng)建日期可以使用系統(tǒng)存儲過程sp_help，其格式如下：Execsp_help‘trigger_name’例如：execsp_help‘觸發(fā)器_歡迎新員工’31修改觸發(fā)器若希望對現(xiàn)有觸發(fā)器進行修改，可以使用altertrigger語句，其語法格式如下：ALTERTRIGGERtrigger_name

ONtable|view

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}

AS

sql_statement[...n]注意：就是創(chuàng)建觸發(fā)器的語句中的create換為alter，其余部分完全相同。但是在altertrigger中引用的觸發(fā)器名必須是已經(jīng)存在的觸發(fā)器名32觸發(fā)器更名使用系統(tǒng)存儲過程sp_rename完成。其語法格式如下：sp_renameoldname,newname例如：將觸發(fā)器“觸發(fā)器_歡迎新員工”更名為welcome_Trigger:Execsp_rename‘觸發(fā)器_歡迎新員工’,‘welcom_trigger’33刪除觸發(fā)器使用droptrigger語句，語法如下：Droptriggertrigger_name[,…n]其中trigger_name是觸發(fā)器名稱，可以同時刪除多個觸發(fā)器。例如：droptrigger觸發(fā)器_員工走了注意：當(dāng)刪除數(shù)據(jù)表時，也將同時刪除所有與該數(shù)據(jù)表相關(guān)聯(lián)的觸發(fā)器。而且當(dāng)刪除觸發(fā)器時，系統(tǒng)也將從sysobjects和syscomments系統(tǒng)表中刪除有關(guān)該觸發(fā)器的信息。34確保數(shù)據(jù)完整性Sp_dboptionpubs,’selectinto’,trueGoSelect*intotblstoresfrompubs..storesSelect*intotblsalesfrompubs..salesgo35selectsa.stor_id,st.stor_namefromtblstoresst,tblsalessawherest.stor_id=sa.stor_idgocreatetriggertrDelAuthorsontblsalesfordeleteasraiserror('%drowsaregoingtobedeletedfromthesalestable!',0,1,@@rowcount)36CreatetriggertrDelSalesOntblStoresForDeleteAsDeletetblsalesfromdeletedwheredeleted.stor_id=tblSales.stor_id最后，繼續(xù)刪除stor_id7067,即“news&brews”商店DeletefromtblstoresWheretblstores.stor_id=‘7067’37封裝事務(wù)規(guī)則CreateTriggertrDelSalesOntblSalesForDeleteAsIf(SelectCount(*)fromDeletedWhereDeleted.qty>=20)>0BeginPrint'youcannotdeleteanyofthesestores'Print'somestoreshavemorethan20sales!'Print'Rollingbackyourtransaction!'RollbackTransactionend38DeleteFromtblSalesWherestor_id=‘7066’至少有一個商店（stor_id=‘7066’）有不少于20條的銷售記錄，為了驗證是否如此，運行select語句：selectstor_id,qtyfromtblsales現(xiàn)在用stor_id=6380再次運行delete語句，正如預(yù)計的那樣，這會刪除qty域不多于20條的商店。Deletefromtblsaleswherestor_id=‘6380’39確保引用完整性CreatetriggertrInsUpdSalesOntblSalesForinsert,UpdateAsIf(selectcount(*)fromtblstores,insertedWheretblstores.stor_id=inserted.stor_id)=0BeginPrint'thestor_idyouhaveentereddoesnotexist'Print'inthestorestable!'RollbacktransactionEnd

觸發(fā)器將會對任何單個的Update和Insert行為發(fā)生作用，確保在tblstores表中的都是有效的stor_id。然而，如果運行selectinto，觸發(fā)器將不能正確觸發(fā)