SQLServer存儲過程和觸發(fā)器

SQLServer存儲過程和觸發(fā)器4.1SQLServer存儲過程

存儲過程概述存儲過程(StoredProcedure)是一組為了完成特定功能T-SQL語句集合，經(jīng)編譯后存儲在SQLServer服務(wù)器端數(shù)據(jù)庫中。存儲過程可以分為兩類：系統(tǒng)存儲過程和自定義存儲過程。系統(tǒng)存儲過程系統(tǒng)存儲過程系統(tǒng)存儲過程在SQLServer安裝成功后，就已經(jīng)存儲在系統(tǒng)數(shù)據(jù)庫Master中，這些存儲過程都是以sp_為前綴命名的它們主要是從系統(tǒng)表中獲取信息，系統(tǒng)管理員可以通過簡單調(diào)用系統(tǒng)存儲過程而完成復(fù)雜的SQLServer管理工作?？梢酝ㄟ^系統(tǒng)存儲過程完成許多管理性或信息的操作。系統(tǒng)存儲過程在Master數(shù)據(jù)庫中，在其他數(shù)據(jù)庫中可以直接調(diào)用，調(diào)用時不必在存儲過程名前加上數(shù)據(jù)庫名。自定義存儲過程自定義存儲過程是由用戶創(chuàng)建并能完成某一特定功能的存儲過程。存儲過程的優(yōu)點1．提高應(yīng)用程序的通用性和可移植性2．可以更有效地管理用戶操作數(shù)據(jù)庫的權(quán)限3．可以提高T-SQL的速度4．減輕服務(wù)器的負(fù)擔(dān)5．塊化程序設(shè)計。6．減少操作錯誤。7．能自動處理復(fù)雜的或敏感的事務(wù)。8．可以實現(xiàn)管理任務(wù)自動化。

存儲過程的創(chuàng)建與執(zhí)行創(chuàng)建前確定所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲過程的語句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。一個存儲過程的最大尺寸為128M1.直接創(chuàng)建存儲過程(1)打開MicrosoftSQLServerManager管理器(2)單擊數(shù)據(jù)庫前面的“+”號，然后單擊“Material_Data1”數(shù)據(jù)庫前面的“+”號，再單擊“可編程性”前面的“+”號，選擇“存儲過程”，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“新建存儲過程”命令。(3)打開了一個創(chuàng)建存儲過程的數(shù)據(jù)庫引擎查詢模板，修改相應(yīng)參數(shù)即可。直接創(chuàng)建存儲過程2.代碼創(chuàng)建存儲過程語法CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

ASsql_statement[...n]begin命令行或命令塊end說明procedure_name：用于指定要創(chuàng)建的存儲過程的名稱。number：該參數(shù)是可選的整數(shù)，它用來對同名的存儲過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。@parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。data_type：用于指定參數(shù)的數(shù)據(jù)類型。Default：用于指定參數(shù)的默認(rèn)值。OUTPUT：表明該參數(shù)是一個返回參數(shù)。recompile:表示每次執(zhí)行此存儲過程時都重新編譯一次encryption:所創(chuàng)建的存儲過程的內(nèi)容會被加密3.代碼執(zhí)行存儲過程利用T-SQL執(zhí)行存儲過程的語法格式如下：execute過程名[參數(shù)值，…[output](1)沒有參數(shù)的存儲過程創(chuàng)建createprochyproclasselect*frommanagerwherewage>1800執(zhí)行該存儲過程executehyprocl(2)有參數(shù)存儲過程創(chuàng)建createprochyproc2@mingzint,@maxgzintasselect*frommanagerwherewagebetween@mingzand@maxgz執(zhí)行實例假設(shè)要顯示工資在1000到2000之間的manager信息，具體代碼：executehyproc21005，18004.1.3修改存儲過程修改存儲過程具體格式如下：alterproc過程名@parameter參數(shù)類型@parameter參數(shù)類型outputassql_statement[...n]begin命令行或命令塊end實例修改存貯過程hyproc2,輸出manager性別分類人員數(shù)與總工資。

alterprochyproc2@sex1char(2),@managercountintoutput,@wagetotalrealoutputasbeginSelect*frommanagerselect@managercount=count(wage)frommanagerwheresex=@sex1select@wagetotal=sum(wage)frommanagerwheresex=@sex1end實例假設(shè)要顯示manager信息及輸出工資的最大值與平均值，具體代碼如下：Declare@x1char(2),@x2realexecutehyproc2'男',@x1output,@x2output4.2SQLServer觸發(fā)器觸發(fā)器是一種特殊類型的存儲過程，是用戶自定義的復(fù)雜的完整性控制過程。特點：功能強(qiáng)、開銷高維護(hù)行級數(shù)據(jù)的完整性與CHECK約束相比，能實現(xiàn)更加復(fù)雜的數(shù)據(jù)完整性數(shù)據(jù)完整性完整性是指數(shù)據(jù)的正確性相容性（一致性）三類基本完整性規(guī)則域完整性規(guī)則使基本表的列輸入有效?？刂朴蛲暾杂行У姆椒ㄓ校合拗茢?shù)據(jù)類型、格式、可能的取值范圍、修改列值時必須滿足的條件等。實體完整性規(guī)則實體完整性規(guī)則用來約束現(xiàn)實世界中的實體是可區(qū)分的，即它們具有唯一性標(biāo)識。這一規(guī)則在關(guān)系模型中的體現(xiàn)是基本表所有主屬性都不能取空值（NULL）。參照完整性規(guī)則參照完整性規(guī)則用來約束具有參照關(guān)系的兩個表中，主碼和外碼的數(shù)據(jù)要保持一致。觸發(fā)器的作用完成比約束更復(fù)雜的數(shù)據(jù)約束檢查所做的SQL是否允許觸發(fā)器可以檢查SQL所做的操作是否被允許。例如：在產(chǎn)品庫存表里，如果要刪除一條產(chǎn)品記錄，在刪除記錄時，觸發(fā)器可以檢查該產(chǎn)品庫存數(shù)量是否為零，如果不為零則取消該刪除操作。修改其它數(shù)據(jù)表里的數(shù)據(jù)當(dāng)一個SQL語句對數(shù)據(jù)表進(jìn)行操作的時候，觸發(fā)器可以根據(jù)該SQL語句的操作情況來對另一個數(shù)據(jù)表進(jìn)行操作。例如：一個訂單取消的時候，那么觸發(fā)器可以自動修改產(chǎn)品庫存表，在訂購量的字段上減去被取消訂單的訂購數(shù)量。調(diào)用更多的存儲過程觸發(fā)器本身就是一種存儲過程，而存儲過程是可以嵌套使用的，所以觸發(fā)器也可以調(diào)用一個或多過存儲過程。觸發(fā)器的作用發(fā)送SQLMail在SQL語句執(zhí)行完之后，觸發(fā)器可以判斷更改過的記錄是否達(dá)到一定條件，如果達(dá)到這個條件的話，觸發(fā)器可以自動調(diào)用SQLMail來發(fā)送郵件。例如：當(dāng)一個訂單交費(fèi)之后，可以物流人員發(fā)送Email，通知他盡快發(fā)貨。返回自定義的錯誤信息約束是不能返回信息的，而觸發(fā)器可以。例如插入一條重復(fù)記錄時，可以返回一個具體的友好的錯誤信息給前臺應(yīng)用程序。更改原本要操作的SQL語句觸發(fā)器可以修改原本要操作的SQL語句例如原本的SQL語句是要刪除數(shù)據(jù)表里的記錄，但該數(shù)據(jù)表里的記錄是最要記錄，不允許刪除的，那么觸發(fā)器可以不執(zhí)行該語句。防止數(shù)據(jù)表構(gòu)結(jié)更改或數(shù)據(jù)表被刪除為了保護(hù)已經(jīng)建好的數(shù)據(jù)表，觸發(fā)器可以在接收到Drop和Alter開頭的SQL語句里，不進(jìn)行對數(shù)據(jù)表的操作。觸發(fā)器的種類在SQLServer2005中，觸發(fā)器可以分為兩大類：DML觸發(fā)器和DDL觸發(fā)器DML觸發(fā)器：DML觸發(fā)器是當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操作語言（DataManipulationLanguage）事件時執(zhí)行的存儲過程。DML觸發(fā)器又分為兩類：After觸發(fā)器和InsteadOf觸發(fā)器DDL觸發(fā)器：DDL觸發(fā)器是在響應(yīng)數(shù)據(jù)定義語言（DataDefinitionLanguage）事件時執(zhí)行的存儲過程。DDL觸發(fā)器一般用于執(zhí)行數(shù)據(jù)庫中管理任務(wù)。如審核和規(guī)范數(shù)據(jù)庫操作、防止數(shù)據(jù)庫表結(jié)構(gòu)被修改等。SQLSERVER2005新增添功能After觸發(fā)器和InsteadOf觸發(fā)器After觸發(fā)器是在記錄更變完之后才被激活執(zhí)行的。以刪除記錄為例：SQLServer先將要刪除的記錄存放在刪除表里，然后把數(shù)據(jù)表里的記錄刪除。再激活A(yù)fter觸發(fā)器，執(zhí)行After觸發(fā)器里的SQL語句。執(zhí)行完畢之后，刪除內(nèi)存中的刪除表，退出整個操作。InsteadOf觸發(fā)器是在這些操作進(jìn)行之前就激活了，并且不再去執(zhí)行原來的SQL操作，而去運(yùn)行觸發(fā)器本身的SQL語句。觸發(fā)器的工作原理在SQLServer2005里，為每個DML觸發(fā)器都定義了兩個特殊的表，一個是插入表，一個是刪除表。這兩個表是建在數(shù)據(jù)庫服務(wù)器的內(nèi)存中的，是由系統(tǒng)管理的邏輯表，而不是真正存儲在數(shù)據(jù)庫中的物理表。對于這兩個表，用戶只有讀取的權(quán)限，沒有修改的權(quán)限。這兩個表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)是完全一致的，當(dāng)觸發(fā)器的工作完成之后，這兩個表也將會從內(nèi)存中刪除。插入表里存放的是更新前的記錄對于插入記錄操作來說，插入表里存放的是要插入的數(shù)據(jù)對于更新記錄操作來說，插入表里存放的是要更新的記錄。刪除表里存放的是更新后的記錄對于更新記錄操作來說，刪除表里存放的是更新前的記錄（更新完后即被刪除）；對于刪除記錄操作來說，刪除表里存入的是被刪除的舊記錄。觸發(fā)器的工作原理激活觸發(fā)器的動作Inserted表Deleted表Insert存放要插入的記錄Update存放要更新的記錄存放更新前的舊記錄Delete

存放要刪除的舊記錄其他注意事項After觸發(fā)器只能用于數(shù)據(jù)表中，InsteadOf觸發(fā)器可以用于數(shù)據(jù)表和視圖上，但兩種觸發(fā)器都不可以建立在臨時表上。一個數(shù)據(jù)表可以有多個觸發(fā)器，但是一個觸發(fā)器只能對應(yīng)一個表。在同一個數(shù)據(jù)表中，對每個操作（如Insert、Update、Delete）而言可以建立許多個After觸發(fā)器，但I(xiàn)nsteadOf觸發(fā)器針對每個操作只有建立一個。如果針對某個操作即設(shè)置了After觸發(fā)器又設(shè)置了InsteadOf觸發(fā)器，那么Insteadof觸發(fā)器一定會激活，而After觸發(fā)器就不一定會激活了。觸發(fā)器定義語法after觸發(fā)器：createtrigger觸發(fā)器名on表名[withencryption]forinsert[，update，delete]asbegin命令行或程序塊endInsteadof觸發(fā)器：createtrigger觸發(fā)器名on表名或視圖名insteadofinsert[，update，delete]asbegin命令行或程序塊end實例創(chuàng)建一個觸發(fā)器，向manager中插入一條記錄，同時創(chuàng)建一個數(shù)據(jù)庫表并向表中插入兩條記錄。(1)打開MicrosoftSQLServerManager管理器。(2)新建一個數(shù)據(jù)庫引擎查詢文檔。(3)在數(shù)據(jù)庫引擎查詢文檔中輸入如下代碼：UseMaterial_Data1(4)按鍵盤上的"F5”鍵，顯示如下提示信息：命令已成功完成。(5)這樣就打開要使用的數(shù)據(jù)庫。實例createtriggerhytriggerlonmanagerforupdateasbegincreatetabletriuser(useridintidentity(1,1)primarykey,usermamevarchar(50),userpwdvarchar(50))insertintotriuser(username,userpwd)values('李明','111')insertintotriuser(usermame,userpwd)values('王明','222')insertintotriuser(usermame,userpwd)values('劉芳','333')end實例(6)選擇創(chuàng)建觸發(fā)器的代碼，按鍵盤上的"F5”鍵，顯示如下提示信息：命令已成功完成。(7)這樣就成功創(chuàng)建了觸發(fā)器。在這里要注意，只是創(chuàng)建了觸發(fā)器，并沒有執(zhí)行觸發(fā)器中的代碼，即表triuser還不存在，當(dāng)然該表中也不會有記錄。(8)下面來通過對manager表的更新操作調(diào)用觸發(fā)器hytriggerl，具體代碼如下：Updatemanagersetwage=wage+100wheremanagerNo='001'(9)選擇SQL語句，按下鍵盤上的"F5”鍵執(zhí)行該SQL語句，顯示如圖提示信息：實例

圖4.5執(zhí)行觸發(fā)器

實例(10)提示信息表示影響了四行，即更新了倉庫表中的一條記錄，創(chuàng)建triuser表，并向該表中插入三條記錄，下面通過select*fromtriuser來顯示觸發(fā)器產(chǎn)生新表中的數(shù)據(jù)信息，如圖所示。實例圖4.6顯示觸發(fā)器執(zhí)行后的結(jié)果

實例在訂單明細(xì)表里，折扣字段不能大于，如果插入記錄時，折扣大于的話，回滾操作。CREATETRIGGER訂單明細(xì)_InsertON訂單明細(xì)AFTERINSERTASBEGINif(Select折扣frominsertedbeginprint'折扣不能大于0.6'RollbackTransactionendENDGO實例在訂單明細(xì)表里，折扣字段不能大于，如果插入記錄時，折扣大于的話，回滾操作。CREATETRIGGER訂單明細(xì)_InsertON訂單明細(xì)InsteadOfINSERTASBEGINdeclare@訂單IDint,@產(chǎn)品IDint,@單價money,@數(shù)量smallint,@折扣realset@訂單ID=(select訂單IDfrominserted)set@產(chǎn)品ID=(select產(chǎn)品IDfrominserted)set@單價=(select單價frominserted)set@數(shù)量=(select數(shù)量frominserted)set@折扣=(select折扣frominserted)if(@折扣print'折扣不能大于0.6‘elseINSERTinto訂單明細(xì)(@訂單ID,@產(chǎn)品ID,@單價,@數(shù)量,@折扣)實例如果更改了學(xué)生的學(xué)號,希望他的借書記錄仍然與這個學(xué)生相關(guān)(也就是同時更改借書記錄表的學(xué)號)CreateTriggertruStudent

OnStudent

--在Student表中創(chuàng)建觸發(fā)器

forUpdate

--為什么事件觸發(fā)

As

--事件觸發(fā)后所要做的事情

ifUpdate(StudentID)

begin

UpdateBorrowRecord

SetStudentID=

FromBorrowRecordbr,Deleted

d,Insertedi

Where=

end

實例如果該學(xué)生已經(jīng)畢業(yè)，希望刪除他的學(xué)號的同時，也刪除它的借書記錄。CreatetriggertrdStudent

OnStudent

forDelete

As

DeleteBorrowRecord

FromBorrowRecordbr,Deltedd

Where=

4.2.2查看觸發(fā)器基本信息通過sp_help能夠查看觸發(fā)器的基本信息觸發(fā)器名、所有者、創(chuàng)建者和創(chuàng)建時間。其語法格式如下：execsp_help觸發(fā)器名如查看觸發(fā)器hytriggerl信息execsp_helphytriggerl實例圖4.7查看觸發(fā)器基本信息

4.2.3查看觸發(fā)器代碼通過sp_helptext能夠查看觸發(fā)器SQL的代碼信息，但要注意如果在創(chuàng)建觸發(fā)器時使withencrypdon選項，則執(zhí)行該命令也看不到SQL代碼。其語法格式如下：execsp_helptext觸發(fā)器名。若要查看觸發(fā)器hytriggerl代碼execsp_helptexthytriggerl實例圖4.8查看觸發(fā)器hytriggerl代碼4.2.4修改觸發(fā)器

修改觸發(fā)器的方法很簡單，利用T-SQL修改觸發(fā)器的語法格式如下：after觸發(fā)器：altertrigger觸發(fā)器名On表名[withencryption]forinsert[，update，delete]asbegin命令行或程序塊End實例修改hytriggerl插入行數(shù)據(jù)‘劉芳’為‘張清’altertriggerhytriggerlonmanagerforupdateasbegincreatetabletriuser(useridintidentity(1,1)primarykey,usermamevarchar(50),userpwdvarchar(50))insertintotriuser(username,userpwd)values('李明','111')insertintotriuser(usermame,userpwd)values('王明','222')insertintotriuser(usermame,userpwd)values('張清','333')end修改insteadof觸發(fā)器altertrigger觸發(fā)器名on表名或視圖名insteadofinsert[,update,delete]asbegin命令行或程序塊end修改觸發(fā)器與創(chuàng)建觸發(fā)器幾乎相同，只是把create改為alter即可。刪除觸發(fā)器刪除觸發(fā)器的方法很簡單，利用T-SQL刪除觸發(fā)器的語法格式如下：droptrigger觸發(fā)器名4.2.6DDL觸發(fā)器DDL觸發(fā)器是SQLServer2005新增的一個觸發(fā)器類型，是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語言（DDL）語句時觸發(fā)。一般用于數(shù)據(jù)庫中執(zhí)行管理任務(wù)。與DML觸發(fā)器一樣，DDL觸發(fā)器也是通過事件來激活，并執(zhí)行其中的SQL語句的。但與DML觸發(fā)器不同，DML觸發(fā)器是響應(yīng)Insert、Update或Delete語句而激活的，DDL觸發(fā)器是響應(yīng)Create、Alter或Drop開頭的語句而激活的。以下幾種情況下可以使用DDL觸發(fā)器：數(shù)據(jù)庫里的庫架構(gòu)或數(shù)據(jù)表架構(gòu)很重要，不允許被修改。防止數(shù)據(jù)庫或數(shù)據(jù)表被誤操作刪除。在修改某個數(shù)據(jù)表結(jié)構(gòu)的同時修改另一個數(shù)據(jù)表的相應(yīng)的結(jié)構(gòu)。要記錄對數(shù)據(jù)庫結(jié)構(gòu)操作的事件語法CREATETRIGGER觸發(fā)器名ON

ALLSERVER或DATABASEFOR或AFTER激活DDL觸發(fā)器的事件AS

要執(zhí)行的SQL語句實例啟動ManagementStudio，登錄到指定的服務(wù)器上。在【對象資源管理器】下選擇【數(shù)據(jù)庫】，定位到【Northwind】數(shù)據(jù)庫上。單擊【新建查詢】按鈕，在彈出的【查詢編輯器】的編輯區(qū)里輸入以下代碼：CREATETRIGGER禁止對數(shù)據(jù)表操作ONDATABASEFORDROP_TABLE,ALTER_TABLEAS

PRINT'對不起，您不能對數(shù)據(jù)表進(jìn)行操作'

ROLLBACK;實例建立一個DDL觸發(fā)器，用于保護(hù)當(dāng)前SQLServer服務(wù)器里所有數(shù)據(jù)庫不能被刪除。具體代碼如下：CREATETRIGGER不允許刪除數(shù)據(jù)庫ONallserver

FORDROP_DATABASEAS

PRINT'對不起，您不能刪除數(shù)據(jù)庫'

ROLLBACK;GO在ManagementStudio如果要修改DDL觸發(fā)器內(nèi)容，就只能先刪除該觸發(fā)器，再重新建立一個DDL觸發(fā)器。觸發(fā)器的創(chuàng)建例：對S表定義一個刪除觸發(fā)器，使得當(dāng)刪除學(xué)生記錄時，將S_C表中相應(yīng)的選課記錄刪除。S（SNO,SNAME）S_C（SNO,CNO,SCORE）觸發(fā)器的創(chuàng)建例：對S_C表定義一個插入觸發(fā)器，使得當(dāng)插入選課記錄時，檢查所參照的學(xué)生學(xué)號和課程號是否存在，如果不存在，則撤消所做的插入操作。S（SNO,SNAME）C（CNO，CNAME）S_C（SNO,CNO