第8章 存儲過程和觸發(fā)器

第8章存儲過程和觸發(fā)器存儲過程概述8.1存儲過程的創(chuàng)建8.2執(zhí)行存儲過程8.3存儲過程的參數(shù)8.4存儲過程的返回值8.5存儲過程的查看、修改和刪除8.6觸發(fā)器的概念8.7

觸發(fā)器的創(chuàng)建8.8觸發(fā)器的使用8.9

觸發(fā)器的修改及刪除8.108.1存儲過程概述8.1.1存儲過程的基本概念存儲過程是獨(dú)立存在于表之外的數(shù)據(jù)庫對象，由被編譯在一起的一組Transact-SQL語句組成。它可以被客戶調(diào)用，也可以被另一個(gè)存儲過程或觸發(fā)器調(diào)用，它的參數(shù)可以被傳遞，它的出錯(cuò)代碼也可以被檢驗(yàn)。

在SQLServer中，使用存儲過程的優(yōu)點(diǎn)如下：①存儲過程在服務(wù)器端運(yùn)行，執(zhí)行速度快。②存儲過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲器，在以后的操作中，只需從高速緩沖存儲器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行，提高了系統(tǒng)性能。③確保數(shù)據(jù)庫的安全。使用存儲過程可以完成所有的數(shù)據(jù)庫操作，并可通過編程方式控制上述操作對數(shù)據(jù)庫信息訪問的權(quán)限。④自動完成需要預(yù)先執(zhí)行的任務(wù)。存儲過程可以在系統(tǒng)啟動時(shí)自動執(zhí)行，而不必在系統(tǒng)啟動后再進(jìn)行手工操作，大大方便了用戶的使用，可以自動完成一些需要預(yù)先執(zhí)行的任務(wù)。8.1.2存儲過程的類型SQLServer支持五種類型的存儲過程：系統(tǒng)存儲過程、本地存儲過程、臨時(shí)存儲過程、遠(yuǎn)程存儲過程和擴(kuò)展存儲過程。在不同情況下需要執(zhí)行不同的存儲過程。1．系統(tǒng)存儲過程

系統(tǒng)存儲過程是由系統(tǒng)提供的存儲過程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲過程定義在系統(tǒng)數(shù)據(jù)庫master中，其前綴是sp_。2．本地存儲過程

本地存儲過程是指在用戶數(shù)據(jù)庫中創(chuàng)建的存儲過程，這種存儲過程完成特定數(shù)據(jù)庫操作任務(wù)，其名稱不能以sp_為前綴。3．臨時(shí)存儲過程

臨時(shí)存儲過程屬于本地存儲過程。如果本地存儲過程的名稱前面有一個(gè)“#”，該存儲過程就稱為局部臨時(shí)存儲過程，這種存儲過程只能在一個(gè)用戶會話中使用；如果本地存儲過程的名稱前有兩個(gè)“##”，該過程就是全局臨時(shí)存儲過程，這種存儲過程可以在所有用戶會話中使用。4．遠(yuǎn)程存儲過程

遠(yuǎn)程存儲過程指從遠(yuǎn)程服務(wù)器上調(diào)用的存儲過程。5．?dāng)U展存儲過程

在SQLServer環(huán)境之外執(zhí)行的動態(tài)鏈接庫稱為擴(kuò)展存儲過程，其前綴是sp_。使用時(shí)需要先加載到SQLServer系統(tǒng)中，并且按照使用存儲過程的方法執(zhí)行。8.2存儲過程的創(chuàng)建

默認(rèn)情況下，用戶創(chuàng)建的存儲過程歸數(shù)據(jù)庫所有者擁有，數(shù)據(jù)庫的所有者可以把許可授權(quán)給其他用戶。

存儲過程由CREATEPROCEDURE語句創(chuàng)建，存儲過程的定義包括：過程名和參數(shù)的說明以及過程體，即包含執(zhí)行存儲過程操作的Transact-SQL語句。要使用存儲過程，首先要創(chuàng)建一個(gè)存儲過程。8.2.1使用CREATEPROCEDURE語句創(chuàng)建1．語法格式CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]2．參數(shù)說明procedure_name：新存儲過程的名稱。過程名必須符合標(biāo)識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須惟一。如果要創(chuàng)建局部臨時(shí)過程，可以在procedure_name前面加一個(gè)編號符#，要創(chuàng)建全局臨時(shí)過程，可以在procedure_name前面加兩個(gè)編號符##。完整的名稱包括（#或##）不能超過128個(gè)字符。指定過程所有者的名稱是可選的。number：是可選的整數(shù)，用來對同名的過程分組，以便用一條[DROPPROCEDURE]語句即可將同組的過程一起除去。@parameter：過程中的參數(shù)。在[CREATEPROCEDURE]語句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲過程最多可以有2100個(gè)參數(shù)。使用@符號作為第一個(gè)字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識符的規(guī)則。每個(gè)過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其他過程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。data_type：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括text、ntext和image）均可以用作存儲過程的參數(shù)。不過，cursor數(shù)據(jù)類型只能用于[OUTPUT]語句的參數(shù)。如果指定的數(shù)據(jù)類型為cursor，也必須同時(shí)指定[VARYING]和[OUTPUT]關(guān)鍵字。說明：對于可以是cursor數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。default：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或[NULL]。如果過程將對該參數(shù)使用[LIKE]關(guān)鍵字，那么默認(rèn)值中可以包含通配符（％、_、[]和^）。OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給[EXEC[UTE]。使用[OUTPUT]參數(shù)可將信息返回給調(diào)用過程。text、ntext和image類型數(shù)據(jù)可用作[OUTPUT]參數(shù)。使用[OUTPUT]關(guān)鍵字的輸出參數(shù)可以走游標(biāo)占位符。n：表示最多可以指定2100個(gè)參數(shù)的占位符。RECOMPILE|ENCRYPTION|（RECOMPILE，ENCRYPTION）：RECOMPILE表明SQLServer不會緩存該過程的計(jì)劃，該過程將在運(yùn)行時(shí)重新編譯。在使用非典型值或臨時(shí)值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計(jì)劃時(shí)，應(yīng)該使用[RECOMPILE]選項(xiàng)。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE語句文本的條目。使用ENCRYPTION可防止將過程作為SQLServer復(fù)制的一部分發(fā)布。說明：在升級過程中，SQLServer利用存儲在syscomments中的加密注釋來重新創(chuàng)建加密過程。FORREPLICATION：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。使用[FORREPLICATION]選項(xiàng)創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復(fù)制過程中執(zhí)行。該選項(xiàng)不能和[WITHRECOMPILE]選項(xiàng)一起使用。AS：指定過程要執(zhí)行的操作。sql_statement：過程中要包含的任意數(shù)目和類型的Transact-SQL語句，但有一些限制。ASsql_statement[…n]：其中的[n]是表示此過程可以包含多條Transact-SQL語句的占位符。3．注意事項(xiàng)①用戶定義的存儲過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建（臨時(shí)過程除外，臨時(shí)過程總是在tempdb中創(chuàng)建）。②成功執(zhí)行CREATEPROCEDURE語句后，過程名稱存儲在sysobjects系統(tǒng)表中，而CREATEPROCEDURE語句的文本存儲在syscomments中。③自動執(zhí)行存儲過程。SQLServer啟動時(shí)可以自動執(zhí)行一個(gè)或多個(gè)存儲過程。這些存儲過程必須由系統(tǒng)管理員在master數(shù)據(jù)庫中創(chuàng)建，并在sysadmin固定服務(wù)器角色下作為后臺過程執(zhí)行。這些過程不能有任何輸入?yún)?shù)。④sql_statement的限制。除了SETSHOWPLANTEXT和SETSHOWPLANALL外，其他SET語句均可在存儲過程內(nèi)使用。

必須使用對象所有者名對數(shù)據(jù)庫對象進(jìn)行限定的語句有：CREATETABLE、ALTERTABLE、DROPTABLE、TRUNCATETABLE、CREATEINDEX、DROPINDEX、UPDATESTATISTICS及DBCC語句。⑤權(quán)限。CREATEPROCEDURE的權(quán)限默認(rèn)授予sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫角色成員。sysadmin固定服務(wù)器角色成員和dlowner固定數(shù)據(jù)庫角色成員可以將CREATEPROCEDURE權(quán)限轉(zhuǎn)讓給其他用戶。USEStudent--檢查是否已存在同名的存儲過程，若有，則刪除。IFEXISTS(SELECTnameFROMsysobjectsWHEREname='stu_info_pro'ANDtype='P')DROPPROCEDUREstu_info_proGO--創(chuàng)建存儲過程CREATEPROCEDUREstu_info_proASSELECTstudent_Name,student_Sex,addressFROMStudent_InfoGOEXECstu_info_pro8.2.2使用企業(yè)管理器創(chuàng)建（1）打開企業(yè)管理器，展開服務(wù)器組，并展開相應(yīng)的服務(wù)器。（2）打開“數(shù)據(jù)庫”文件夾，并打開要創(chuàng)建存儲過程的數(shù)據(jù)庫。（3）選擇“存儲過程”選項(xiàng)，右擊鼠標(biāo)，執(zhí)行“新建存儲過程”命令，打開創(chuàng)建存儲過程對話框如圖8-2所示。圖8-2創(chuàng)建存儲過程對話框（4）在“文本”列表框中顯示了CREATEPROCEDURE語句的框架，可以修改要創(chuàng)建的存儲過程的名稱，然后加入存儲過程所包含的SQL語句。（5）單擊“檢查語法”按鈕，可以檢查創(chuàng)建存儲過程的SQL語句的語法是否正確。（6）如果要將其設(shè)置為下次創(chuàng)建存儲過程的模板，可單擊“另存為模板”按鈕。（7）完成后，單擊“確定”按鈕即可創(chuàng)建一個(gè)存儲過程。8.2.3使用向?qū)?chuàng)建

（1）在企業(yè)管理器中，執(zhí)行“工具”下拉菜單中的“向?qū)А泵?，打開“選擇向?qū)А睂υ捒?。?）在“數(shù)據(jù)庫”文件夾選擇“創(chuàng)建存儲過程”向?qū)В瑔螕簟按_定”按鈕，出現(xiàn)“創(chuàng)建存儲過程向?qū)А睔g迎對話框。（3）單擊“下一步”按鈕，出現(xiàn)選擇數(shù)據(jù)庫對話框，如圖8-3所示。（4）在“數(shù)據(jù)庫名稱”下拉列表中選擇數(shù)據(jù)庫后，單擊“下一步”按鈕，出現(xiàn)選擇存儲過程對話框，如圖8-4所示。圖8-3選擇數(shù)據(jù)庫對話框圖8-4選擇存儲過程對話框（5）單擊“下一步”按鈕，出現(xiàn)完成創(chuàng)建存儲過程對話框。若單擊“完成”按鈕，即可完成存儲過程的創(chuàng)建。（6）單擊“編輯”按鈕，可編輯存儲過程，如圖8-6所示。圖8-5完成創(chuàng)建存儲過程對話框圖8-6編輯存儲過程對話框（7）單擊“編輯SQL”按鈕，即可打開“編輯存儲過程SQL”對話框，其中的列表框顯示了創(chuàng)建該存儲過程的Transact-SQL語句。可以在已有的Transact-SQL語句的基礎(chǔ)上進(jìn)行編輯，也可以單擊“分析”按鈕來執(zhí)行語法檢查。如圖8-7所示。圖8-7“編輯存儲過程SQL”對話框（8）單擊“確定”按鈕，返回到圖8-5所示的對話框，再單擊“確定”按鈕即可。8.3執(zhí)行存儲過程1．語法格式[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n][WITHRECOMPILE]2．參數(shù)說明@return_status：一個(gè)可選的整型變量，保存存儲過程的返回狀態(tài)。此變量在用于EXECUTE語句前，必須在批處理、存儲過程或函數(shù)中已聲明。procedure_name：調(diào)用的存儲過程名稱。number：可選的整數(shù)，用于將相同名稱的過程進(jìn)行組合，使得它們可以用一句DROPPROCEDURE語句除去。該參數(shù)不能用于擴(kuò)展存儲過程。在同一應(yīng)用程序中使用的過程一般都以該方式組合。@procedure_name_var：局部定義變量名，代表存儲過程名稱。@parameter：過程參數(shù)，在[CREATEPROCEDURE]語句中定義。參數(shù)名稱前必須加上符號@。在以“@parameter_name=value”格式使用時(shí)，參數(shù)名稱和常量不一定按照[CREATEPROCEDURE]語句中定義的順序出現(xiàn)。但是，如果有一個(gè)參數(shù)使用“@parameter_name=value”格式，則其他所有參數(shù)都必須使用這種格式。

默認(rèn)情況下，參數(shù)可為空。如果傳遞NULL參數(shù)值，且該參數(shù)用于CREATE或ALTERTABLE語句中不允許為NULL的列（例如，插入至不允許為NULL的列），SQLServer就會報(bào)錯(cuò)。為避免將NULL參數(shù)值傳遞給不允許為NULL的列，可以在過程中添加程序設(shè)計(jì)邏輯或采用默認(rèn)值（使用CREATE或ALTERTABLE語句中的DEFAULT關(guān)鍵字）。value：過程中參數(shù)的值。如果參數(shù)名稱沒有指定，參數(shù)值必須以CREATEPROCEDURE語句中定義的順序給出。@variable：用來保存參數(shù)或者返回參數(shù)的變量。OUTPUT：指定存儲過程必須返回一個(gè)參數(shù)。該存儲過程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)建。使用游標(biāo)變量作參數(shù)時(shí)使用該關(guān)鍵字。

DEFAULT：根據(jù)過程的定義提供參數(shù)的默認(rèn)值。當(dāng)過程需要的參數(shù)值沒有事先定義好默認(rèn)值或缺少參數(shù)或指定了[DEFAULT]關(guān)鍵字，就會出錯(cuò)。n：占位符，表示在它前面的項(xiàng)目可以多次重復(fù)執(zhí)行。例如，EXCUTE語句可以指定一個(gè)或者多個(gè)@parameter、value或@variable。WITHRECOMPILE：強(qiáng)制編譯新的計(jì)劃。如果所提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變，使用該選項(xiàng)，在以后的程序執(zhí)行中使用更改過的計(jì)劃。該選項(xiàng)不能用于擴(kuò)展存儲過程。建議盡量少使用該選項(xiàng)，因?yàn)樗妮^多的系統(tǒng)資源。3．注意事項(xiàng)①如果存儲過程名的前三個(gè)字符為sp_，SQLServer會在Master數(shù)據(jù)庫中尋找該過程。如果沒能找到合法的過程名，SQLServer會尋找所有者名稱為dbo的過程。②參數(shù)可以通過value或@parametername：value提供。③執(zhí)行存儲過程時(shí)，若語句是批處理中的第一個(gè)語句，則不一定要指定EXECUTE關(guān)鍵字。8.4存儲過程的參數(shù)8.4.1參數(shù)傳遞方式1．順序法在傳遞參數(shù)時(shí)，使傳遞的參數(shù)和定義時(shí)的參數(shù)順序一致，對于使用默認(rèn)值的參數(shù)可以用DEFAULT值代替。USEStudentGoCREATEPROCEDUREAdd_class(@class_idint,@class_namechar(20),@directorchar(10),@professionchar(14))ASINSERTINTOclass_infoVALUES(@class_id,@class_name,@director,@profession)EXECAdd_class200202,'計(jì)算機(jī)應(yīng)用022','張波','計(jì)算機(jī)應(yīng)用'select*fromclass_infoUSEStudentIFEXISTS(SELECTnameFROMsysobjectsWHEREname='stu_score_pro'ANDtype='P')DROPPROCEDUREstu_score_proGoCREATEPROCEDUREstu_score_pro@snamechar(10),@cnamechar(10)ASSELECTa.student_name,c.course_name,c.course_start,b.result,c.course_scoreFROMstudent_infoaJOINresult_infobONa.student_id=b.student_idJOINcourse_infocONb.course_no=c.course_noWHEREc.course_name=@cnameanda.student_name=@snameEXECstu_score_pro'陳白露','馬克思主義'2．提示法

在傳遞參數(shù)時(shí)，采用提示的方法，如“@class_id=200202”的形式，此時(shí)，各個(gè)參數(shù)的順序可以任意排列。EXECAdd_class@class_id=200202,@class_name='計(jì)算機(jī)應(yīng)用022',@director='張波',@profession='計(jì)算機(jī)應(yīng)用'EXECAdd_class@class_name='計(jì)算機(jī)應(yīng)用022',@class_id=200202,@profession='計(jì)算機(jī)應(yīng)用',@director='張波'8.4.2使用默認(rèn)參數(shù)

創(chuàng)建存儲過程時(shí)，可以為參數(shù)提供一個(gè)默認(rèn)值，默認(rèn)值必須為常量或者NULL。USEStudentGoCREATEPROCEDUREAdd_class@class_idint,@class_namechar(20),@directorchar(10)='無',@professionchar(14)='無'ASINSERTINTOclass_infoVALUES(@class_id,@class_name,@director,@profession)EXECAdd_class200202,'計(jì)算機(jī)應(yīng)用022'Goselect*fromclass_infoGo8.4.3使用帶OUTPUT的返回參數(shù)

在創(chuàng)建存儲過程時(shí)，可以定義返回參數(shù)。在執(zhí)行存儲過程時(shí)，可以將結(jié)果返回給返回參數(shù)。USEStudentGoCREATEPROCEDUREQuery_student(@student_idint,@student_namechar(10)OUTPUT,@addressvarchar(50)OUTPUT)ASSELECT@student_name=student_name,@address=addressFROMstudent_infoWHEREstudent_id=@student_idDECLARE@student_namechar(10)DECLARE@addressvarchar(50)EXECQuery_student20000203,@student_nameOUTPUT,@addressOUTPUTSELECT'姓名'=@student_name,'家庭住址'=@addressGO8.5存儲過程的返回值

存儲過程在執(zhí)行后都會返回一個(gè)整型值。如果執(zhí)行成功，則返回0；否則返回?1～99之間的數(shù)值。8.5.1RETURN語句1．語法RETURN[整數(shù)表達(dá)式]2．功能從查詢或過程中無條件退出。[RETURN]語句可在任何時(shí)候從過程、批處理或語句塊中退出，不執(zhí)行位于[RETURN]之后的語句。3．說明①[整數(shù)表達(dá)式]：返回的整型值。存儲過程可以給調(diào)用過程或應(yīng)用程序返回整型值。②在建立存儲過程的時(shí)候，需要定義任意的出錯(cuò)條件，并把它們與整型的出錯(cuò)代碼聯(lián)系起來。③用于存儲過程時(shí)，[RETURN]不能返回空值。如果過程試圖返回空值，系統(tǒng)將生成警告信息并返回0值。USEStudentGoCREATEPROCEDUREtest_return(@input_numint=0)ASIF@input_num=0RETURN0IF@input_num>0RETURN1IF@input_num<0RETURN-18.5.2捕獲存儲過程的返回值

若要正確接收存儲過程的返回值，可使用EXECUTE語句。1．語法EXECUTE@return_status=procedure_name2．功能

執(zhí)行存儲過程，將RETURN語句返回的值送狀態(tài)變量@return_status。3．說明

在執(zhí)行EXECUTE語句之前，首先要聲名變量@return_status。DECLARE@ret_numintEXEC@ret_num=test_return100SELECT'返回值'=@ret_numGO8.6存儲過程的查看、修改和刪除8.6.1存儲過程的查看1．使用sp_helptext查看存儲過程EXECsp_helptexttest_return2．使用企業(yè)管理器（1）打開企業(yè)管理器，展開服務(wù)器組，并展開相應(yīng)的服務(wù)器；（2）打開數(shù)據(jù)庫文件夾，然后選擇存儲過程所在的數(shù)據(jù)庫“student”；（3）打開“存儲過程”文件夾，在右側(cè)詳細(xì)信息窗格中右擊存儲過程add_class，執(zhí)行“屬性”命令，打開“存儲過程屬性”對話框，如圖8-13所示；圖8-13存儲過程屬性對話框（4）可以在此對話框中，直接修改存儲過程的定義，也可以設(shè)置存儲過程的權(quán)限。完成后，單擊“確定”按鈕即可。8.6.2存儲過程的修改

修改存儲過程使用ALTERPROCEDURE語句。1．功能更改先前通過執(zhí)行CREATEPROCEDURE語句創(chuàng)建的存儲過程，但不會更改權(quán)限，也不影響相關(guān)的存儲過程或觸發(fā)器。2．語法ALTERPROC[EDURE]procedure_name[；number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]3．說明

各參數(shù)含義與CREATEPROCEDURE命令相同。如果原來的過程定義是用WITHENCRYPTION或WITHRECOMPILE創(chuàng)建的，那么只有在ALTERPROCEDURE中也包含這些選項(xiàng)時(shí)，這些選項(xiàng)才有效。ALTERPROCEDURE權(quán)限默認(rèn)授予sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫角色成員以及過程的所有者且不可轉(zhuǎn)讓。用ALTERPROCEDURE更改后，過程的權(quán)限和啟動屬性保持不變。8.6.3存儲過程的刪除1．通過企業(yè)管理器刪除在要刪除的存儲過程中右擊鼠標(biāo)，然后執(zhí)行“刪除”命令，在彈出的對話框中單擊“全部除去”按鈕即可。2．通過DROPPROCEDURE語句來完成語法格式：

DROPPROCEDURE{procedure}[，…n]功能：從當(dāng)前數(shù)據(jù)庫中刪除一個(gè)或多個(gè)存儲過程或存儲過程組。參數(shù)含義：①procedure：指要刪除的存儲過程或存儲過程組的名稱；②n：表示可以指定多個(gè)存儲過程同時(shí)刪除。③默認(rèn)情況下，將DROPPROCEDURE權(quán)限授予過程所有者，該權(quán)限不可轉(zhuǎn)讓。db_owner和db_ddladmin固定數(shù)據(jù)庫角色成員和sysadmin固定服務(wù)器角色成員可以通過在DROPPROCEDURE內(nèi)指定所有者刪除任何對象。

DROPPROCEDUREtest_return8.7觸發(fā)器的概念

觸發(fā)器是一種特殊類型的存儲過程，它不能顯示地被調(diào)用，它是在指定的表中插入記錄、更改記錄或者刪除記錄時(shí)，被自動激活。所以，觸發(fā)器可以用來對表實(shí)施復(fù)雜的完整性約束，當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生改變時(shí)，觸發(fā)器會自動被激活，從而防止對數(shù)據(jù)的不正確的修改。

在觸發(fā)器中可以查詢其他表，也可以查詢其他更復(fù)雜的T-SQL語句。觸發(fā)器和引起觸發(fā)器執(zhí)行的T-SQL語句被當(dāng)作一次事務(wù)處理，因此可以在觸發(fā)器中回滾這個(gè)事務(wù)。

如果發(fā)現(xiàn)引起觸發(fā)器執(zhí)行的T-SQL語句執(zhí)行了一個(gè)非法操作，則可以通過回滾事務(wù)使語句不能執(zhí)行，回滾后SQLServer會自動返回到此事務(wù)執(zhí)行前的狀態(tài)。8.8觸發(fā)器的創(chuàng)建8.8.1使用企業(yè)管理器創(chuàng)建觸發(fā)器（1）打開企業(yè)管理器，展開服務(wù)器組，并展開相應(yīng)的服務(wù)器。（2）打開“數(shù)據(jù)庫”文件夾，選擇要創(chuàng)建觸發(fā)器的數(shù)據(jù)庫。（3）選擇“表”文件夾，然后在要創(chuàng)建觸發(fā)器的表上右擊鼠標(biāo)，執(zhí)行“所有任務(wù)”子菜單下的“管理觸發(fā)器”命令，打開“觸發(fā)器屬性”對話框。在此對話框中的“文本”列表框中，輸入用于創(chuàng)建觸發(fā)器的Transact-SQL語句。單擊“檢查語法”按鈕可以檢查SQL語句的語法是否正確。注意：如果在“名稱”文本框中選擇已經(jīng)創(chuàng)建的觸發(fā)器，則單擊下面的“刪除”按鈕即可刪除該觸發(fā)器。（4）輸入完成后，單擊“確定”按鈕，即可創(chuàng)建觸發(fā)器。8.8.2使用Transact-SQL語句創(chuàng)建觸發(fā)器1．語法格式CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[DELETE][，][INSERT][，][UPDATE]}[NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][…n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[…n]}]sql_statement[…n]}}2．參數(shù)說明參數(shù)trigger_name用于指定觸發(fā)器名。觸發(fā)器名必須符合標(biāo)識符規(guī)則，并且在數(shù)據(jù)庫中必須惟一，可以包含觸發(fā)器所有者名。

tablelview是觸發(fā)器表或觸發(fā)器視圖，即在其上執(zhí)行觸發(fā)器的表或視圖。有時(shí)，可以包含表或視圖的所有者名。關(guān)鍵字WITHENCRYPTION可防止將觸發(fā)器作為SQLServer復(fù)制的一部分發(fā)布。

AFTER關(guān)鍵字用于說明觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，AFTER是默認(rèn)設(shè)置，不能在視圖上定義AFTER觸發(fā)器。INSTEADOF指定用觸發(fā)器中的操作代替觸發(fā)語句的操作，在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語句最多可以定義一個(gè)INSTEADOF觸發(fā)器。如果觸發(fā)器表存在約束，則在INSTEADOF觸發(fā)器執(zhí)行之后和AFTER觸發(fā)器執(zhí)行之前檢查這些約束。如果違反了約束，則回滾INSTEADOF觸發(fā)器操作且不執(zhí)行AFTER觸發(fā)器，INSTEADOF觸發(fā)器不能在WITHCHECKOPTION可更新視圖上定義。關(guān)鍵字DELETE、INSERT和UPDATE用于指定在表或視圖上執(zhí)行這一操作時(shí)將激活相應(yīng)的觸發(fā)器，必須指定一項(xiàng)或多項(xiàng)，項(xiàng)與項(xiàng)之間用逗號分隔。關(guān)鍵字選項(xiàng)NOTFORREPLICATION指該觸發(fā)器對于復(fù)制進(jìn)程無效。IFUPDATE(column)子句用于測試在指定的列上進(jìn)行的INSERT或UPDATE操作，不能用于DELETE操作；UPDATE(column)中的column為表或者視圖中的列名稱，說明這一列的數(shù)據(jù)是否被INSERT或者UPDATE操作修改過。如果修改過，則返回TRUE，否則返回FALSE。IF(COLUMNS_UPDATED())子句用于測試是否插入或更新了指定的列。返回的二進(jìn)制位數(shù)據(jù)，表示插入或更新了表中的哪些列，若對應(yīng)位為0，表示沒有插入或更新；若對應(yīng)位為1，表示對該列進(jìn)行了插入或更新。關(guān)于表文件的列與二進(jìn)制位的對應(yīng)關(guān)系為：如果表的列從左向右分別為C0，C1，C2，C3，C4…則分別對應(yīng)二進(jìn)制位的第0位、第1位、第2位、第3位、第4位……依此類推。

如果在表上創(chuàng)建的觸發(fā)器包含8列以上，則COLUMNS_UPDATED()返回多個(gè)字節(jié)。在INSERT操作中，COLUMNS_UPDATED將對所有列返回TRUE值，IF(COLUMNS_UPDATED())僅用于INSERT或UPDATE觸發(fā)器。bitwise_opemtor為用于比較運(yùn)算的位運(yùn)算符。updated_bitmask為整型的位屏蔽碼，假定該表上有UPDATE觸發(fā)器，若要檢查列C1、C2、C4是否都有更新，可指定updated_bitmask的值為00010110（即22）；若要檢查是否只有列C1有更新，可指定updated_bitmask的值為00000010（即2）。comparison_operator為比較運(yùn)算符。使用等號（=）檢查updated_bitmask中指定的所有列是否都實(shí)際進(jìn)行了更新。使用大于號（>）檢查update_bitmask中指定的任一列或某些列是否已更新。

column_bitmask為列屏蔽碼，用來檢查是否已更新或插入了對應(yīng)列。參數(shù)sql_statement為觸發(fā)器的T-SQL語句，當(dāng)執(zhí)行DELETE、INSERT或UPDATE操作時(shí)，對應(yīng)的觸發(fā)器操作將生效。

n表示觸發(fā)器中可以包含多條T-SQL語句。USEStudentGO/*如果表B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='B1')DROPTABLEB1GOCREATETABLEB1(student_numberint,student_namechar(30))GO/*如果觸發(fā)器Query1_B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Query1_B1'ANDtype='TR')DROPTRIGGERQuery1_B1GO/*創(chuàng)建觸發(fā)器Query1_B1*/CREATETRIGGERQuery1_B1ONB1FORINSERT,UPDATE,DELETEASSELECT*FROMB1GOINSERTB1VALUES(200401,'張山')8.9觸發(fā)器的使用8.9.1inserted表和deleted表

在觸發(fā)器執(zhí)行的時(shí)候，會產(chǎn)生兩個(gè)臨時(shí)表：inserted表和deleted表。它們的結(jié)構(gòu)和觸發(fā)器所在的表的結(jié)構(gòu)相同，SQLServer2000自動創(chuàng)建和管理這些表?？梢允褂眠@兩個(gè)臨時(shí)的駐留內(nèi)存的表測試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件；然而，不能直接對表中的數(shù)據(jù)進(jìn)行更改。inserted表用于存儲INSERT和UPDATE語句所影響的行的副本。在一個(gè)插入或更新事務(wù)處理中，新建行被同時(shí)添加到inserted表和觸發(fā)器表中。inserted表中的行是觸發(fā)器表和新行的副本。deleted表用于存儲DELETE和UPDATE語句所影響的行的復(fù)本。在執(zhí)行DELETE或UPDATE語句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。delete表和觸發(fā)器表通常沒有相同的行。執(zhí)行INSERT操作：插入到觸發(fā)器表中的新行被插入到inserted表中。執(zhí)行DELETE操作：從觸發(fā)器表中刪除的行被插入到deleted表中。執(zhí)行UPDATE操作：先從觸發(fā)器表中刪除舊行，然后再插入新行。其中被刪除的舊行被插入到deleted表中，插入的新行被插入到inserted表中。/*如果觸發(fā)器Query2_B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Query2_B1'ANDtype='TR')DROPTRIGGERQuery2_B1GO/*創(chuàng)建觸發(fā)器Query2_B1*/CREATETRIGGERQuery2_B1ONB1FORINSERT,UPDATE,DELETEASSELECT*FROMinsertedSELECT*FROMdeletedGOUPDATEB1SETstudent_name='張峰'WHEREstudent_number=2004018.9.2INSERT觸發(fā)器和UPDATE觸發(fā)器

當(dāng)向表中插入或者更新記錄時(shí)，INSERT或者UPDATE觸發(fā)器被執(zhí)行。一般情況下，這兩種觸發(fā)器常用來檢查插入或者修改后的數(shù)據(jù)是否滿足要求。USEStudentIFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_insert'ANDtype='TR')DROPTRIGGERcheck_insertGOCREATETRIGGERcheck_insertONresult_infoFORINSERTASIFEXISTS(SELECT*FROMinsertedaWHEREa.student_idNOTIN(SELECTb.student_idFROMstudent_infob)ORa.course_noNOTIN(SELECTc.course_noFROMcourse_infoc))BEGINRAISERROR('違背數(shù)據(jù)的完整性',16,1)ROLLBACKTRANSACTION/*回滾事務(wù)*/ENDinsertresult_infoVALUES('020',20040101,'31',20)USEStudentGO/*檢查是否存在score表，若存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='score')DROPTABLEscoreGO/*創(chuàng)建score表*/CREATETABLEscore(student_noint,scoreint)/*檢查是否存在check_score觸發(fā)器，若存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_score'ANDtype='TR')DROPTRIGGERche