第8章存儲過程與觸發(fā)器第8章存儲過程與觸發(fā)器1本章學(xué)習(xí)目標(biāo)l理解存儲過程和觸發(fā)器的作用l

熟練創(chuàng)建和管理存儲過程及觸發(fā)器l

靈活運用存儲過程和觸發(fā)器提高系統(tǒng)開發(fā)效率本章學(xué)習(xí)目標(biāo)2

8.1存儲過程

8.1.1存儲過程基礎(chǔ)知識

1．存儲過程簡介

存儲過程是存儲在服務(wù)器上的由SQL語句和控制流語句組成的一個預(yù)編譯集合。存儲過程劃分為系統(tǒng)存儲過程以及用戶存儲過程。一個存儲過程可以作為一個獨立的單元進行處理。在SQLServer中可以通過存儲過程來完成很多管理任務(wù)，利用一些常用的系統(tǒng)存儲過程可以很方便的查看、操作數(shù)據(jù)庫對象。系統(tǒng)存儲過程的命名通常以”sp_”作為前綴，并且存儲于Master數(shù)據(jù)庫中，如果用戶擁有足夠的權(quán)限，就可以在任何數(shù)據(jù)庫中調(diào)用這些存儲過程。

存儲過程提供了一種封裝某一個需要重復(fù)執(zhí)行任務(wù)的方法。一旦定義了一個存儲過程之后，在應(yīng)用程序中就可以對其進行調(diào)用。在存儲過程中，不僅可以包含程序流、邏輯以及對數(shù)據(jù)庫的查詢，而且也可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集。8.1存儲過程

8.1.1存儲過程基礎(chǔ)知識

1．存3所以通過設(shè)計自定義用戶存儲過程，用戶可以使其實現(xiàn)強大的編程功能。

使用存儲過程可以比單獨的SQL語句完成更為復(fù)雜的功能，并且系統(tǒng)會對存儲過程中的SQL語句進行了預(yù)編譯處理，使得執(zhí)行速度有了大幅度的提升。存儲過程被第一次調(diào)用后，會保存在高速緩沖區(qū)中，這樣再次執(zhí)行同一個存儲過程時，會提高了重復(fù)調(diào)用的效率。在實際應(yīng)用中，可以將復(fù)雜的商業(yè)規(guī)則封裝在存儲過程中，從而提高程序語句的利用率。

2．存儲過程的優(yōu)點存儲過程一旦執(zhí)行一次后，其執(zhí)行的計劃就會駐留在計算機的高速緩沖存儲器中。其后對同一個存儲過程的調(diào)用就可以直接利用編譯后在高速緩存中的二進制形式來完成操作?？梢栽趩蝹€存儲過程中執(zhí)行一系列SQL語句，因而可以用于設(shè)計、封裝企業(yè)的功能模塊。應(yīng)用程序使用相同的存儲過程進行操作，確保了在數(shù)據(jù)訪問、操縱的一致性。所以通過設(shè)計自定義用戶存儲過程，用戶可以使其實4可以在當(dāng)前的存儲過程內(nèi)部引用其它存儲過程，這樣可以將復(fù)雜語句進行簡化。提供了一種數(shù)據(jù)庫訪問的途徑，只需要用戶擁有執(zhí)行存儲過程的權(quán)限，那么通過使用存儲過程就可以完成對數(shù)據(jù)庫的各種操作，如添加數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)等，而不需要考慮用戶是否擁有存儲過程所處理的數(shù)據(jù)對象的訪問權(quán)限。3．使用存儲過程的注意事項在命名自定義存儲過程時避免與系統(tǒng)存儲過程名相同。存儲過程最多能夠支持32層的嵌套。命名存儲過程中的標(biāo)識符時，長度不能超過128個字符。存儲過程中參數(shù)的個數(shù)不能超過2100。3．使用存儲過程的注意事項58.1.2創(chuàng)建和執(zhí)行存儲過程

在SQLServer中，既可以通過企業(yè)管理器，也可以通過使用CREATEPROCEDRUE語句的方式來創(chuàng)建存儲過程。

1．使用企業(yè)管理器創(chuàng)建存儲過程

步驟：

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器節(jié)點、數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫并在該節(jié)點上點擊鼠標(biāo)右鍵，選擇【新建】菜單下的【存儲過程】級聯(lián)菜單，將彈出如圖8-1所示的對話框。圖8-1新建存儲過程對話框8.1.2創(chuàng)建和執(zhí)行存儲過程

在SQLServer中，6③在【存儲過程屬性】對話框中的【文本】編輯框中，對[OWNER]以及[PROCEDURENAME]部分進行修改，分別修改為該存儲過程的所有者和存儲過程名稱，并且輸入該存儲過程所包含的SQL語句。如圖8-2所示為創(chuàng)建了一個用于查詢學(xué)生信息的存儲過程。圖8-2用于查詢學(xué)生信息的存儲過程③在【存儲過程屬性】對話框中的【文本】編輯框中，對[OWNE7④如果需要驗證存儲過程中的SQL語句的正確性，可以單擊【檢查語法】按鈕，如果沒有語法錯誤則會彈出如圖8-3所示的“語法檢查成功”的對話框。圖8-3“語法檢查成功”的提示對話框⑤當(dāng)存儲過程中沒有語法錯誤后，可以單擊【確定】按鈕來保存自定義的存儲過程。④如果需要驗證存儲過程中的SQL語句的正確性，可以單擊【檢查82．使用CREATEPROCEDURE語句來創(chuàng)建存儲過程

該語句可以創(chuàng)建永久使用的存儲過程，也可以創(chuàng)建局部臨時過程、全局臨時過程。

語法格式為：

CREATEPROCEDURE存儲過程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語句[...n]]其中：存儲過程名稱必須符合標(biāo)識符命名規(guī)則，而且對于數(shù)據(jù)庫以及所有者命名必須唯一。一個完整的命名不能超過128個字符。2．使用CREATEPROCEDURE語句來創(chuàng)建存儲過程9【例8-1】使用CREATEPROCEDURE語句創(chuàng)建一個名稱為proc_bjrs的存儲過程，用于檢索現(xiàn)有班級及人數(shù)。

CREATEPROCEDUREproc_bjrsAS

SELECTDISTINCT(班級表.班級編號),班級表.班級名稱，人數(shù)=COUNT(學(xué)生基本信息表.學(xué)號)

FROM學(xué)生基本信息表,班級表

WHERE班級表.班級編號=學(xué)生基本信息表.班級編號

GROUPBY班級表.班級編號,班級表.班級名稱

GO

在查詢分析器中執(zhí)行以上命令后，然后執(zhí)行該存儲過程EXECUTEproc_bjrs，運行結(jié)果如圖8-4所示。【例8-1】使用CREATEPROCEDURE語句創(chuàng)建一個10圖8-4檢索現(xiàn)有班級以及人數(shù)圖8-4檢索現(xiàn)有班級以及人數(shù)11【例8-2】設(shè)計一個帶有參數(shù)的存儲過程，該參數(shù)用于傳遞班級編號，根據(jù)該參數(shù)在學(xué)生基本信息表中檢索出某一個班級中所有學(xué)生的信息。

CREATEPROCEDUREproc_bjcx@bjbhVARCHAR(8)

AS

SELECT*FROM學(xué)生基本信息表WHERE班級編號=@bjbh

GO

其中參數(shù)@bjbh用于接收班級編號，在實際調(diào)用中根據(jù)該參數(shù)將會返回一個結(jié)果集。例如，EXECUTEproc_bjcx'20051003'，將會顯示班級編號為20051003的學(xué)生信息。運行結(jié)果如圖8-5所示【例8-2】設(shè)計一個帶有參數(shù)的存儲過程，該參數(shù)用于傳遞班級編12圖8-5用于傳遞班級編號的存儲過程圖8-5用于傳遞班級編號的存儲過程138.1.3修改與刪除存儲過程

在SQLServer中，可以通過企業(yè)管理器或SQL語句兩種方式修改或刪除存儲過程。

1．使用企業(yè)管理器修改存儲過程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，展開該數(shù)據(jù)庫節(jié)點后點擊【存儲過程】，在右側(cè)的窗口中將會顯示出存儲過程的列表。右擊需要修改的存儲過程，在彈出菜單中選擇【屬性】，彈出存儲過程屬性對話框，如圖8-6所示。圖8-6【存儲過程屬性】對話框8.1.3修改與刪除存儲過程

在SQLServer中，14③在【存儲過程屬性】對話框的【文本】編輯框中修改包含的SQL語句。需要注意的是，存儲過程的名稱不能修改。

④在檢查了語法的正確性之后，可以單擊【確定】按鈕保存修改并關(guān)閉對話框。

2．使用企業(yè)管理器重命名存儲過程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，展開該數(shù)據(jù)庫節(jié)點后點擊【存儲過程】，在右側(cè)的窗口中將會顯示出存儲過程的列表。右擊需要重命名的存儲過程，并選擇彈出菜單中的【重命名】命令，如圖8-7所示。③在【存儲過程屬性】對話框的【文本】編輯框中修改包含的SQL15圖8-7彈出菜單中的【重命名】命令③重新輸入存儲過程的名稱后，按下回車鍵完成修改。圖8-7彈出菜單中的【重命名】命令③重新輸入存儲過程的名16

3．使用ALTERPROCEDURE語句修改存儲過程

ALTERPROCEDURE語句可以對數(shù)據(jù)庫中已有的存儲過程進行修該，但不會更改權(quán)限，也不影響相關(guān)的存儲過程或觸發(fā)器。該語句的語法格式為：

ALTERPROCEDURE存儲過程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語句[...n]]

其中，存儲過程名稱為數(shù)據(jù)庫中已經(jīng)存在的過程名，語法中的常用選項與CREATEPROCEDURE語句一致。

3．使用ALTERPROCEDURE語句修改存儲過程

A17【例8-3】修改已經(jīng)創(chuàng)建的存儲過程proc_bjcx及其功能，將其修改為可以根據(jù)系部編號來查詢某一個系部中所有學(xué)生的信息。修改后將存儲過程名稱重命名為proc_xbcx。

在查詢分析器中運行如下命令，即可完成重命名：

ALTERPROCEDUREproc_bjcx@xbbhVARCHAR(2)

AS

SELECT*FROM學(xué)生基本信息表

WHERE班級編號

IN(SELECT班級編號FROM班級表WHERE系部編號=@xbbh)

GO

SP_RENAME'proc_bjcx','proc_xbcx'

GO【例8-3】修改已經(jīng)創(chuàng)建的存儲過程proc_bjcx及其功能18使用SP_RENAME可以重新命名一個現(xiàn)有的存儲過程。SP_RENAME的語法格式為：

SP_RENAME原名稱,新名稱。

完成修改后查詢系部編號為’01’的學(xué)生信息。

EXECproc_xbcx'01'

4．使用企業(yè)管理器刪除存儲過程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，展開該數(shù)據(jù)庫后點擊【存儲過程】，在右側(cè)的窗口中顯示出存儲過程的列表。右擊需要刪除的存儲過程，并在彈出菜單中選擇【刪除】命令，將彈出【除去對象】對話框，如圖8-8所示。

③在【除去對象】對話框中單擊【全部除去】按鈕將會完成對存儲過程的刪除操作。使用SP_RENAME可以重新命名一個現(xiàn)有的存儲過程。SP_19圖8-8【除去對象】對話框圖8-8【除去對象】對話框205．使用DROPPROCEDURE語句刪除存儲過程

使用DROPPROCEDURE語句可以從當(dāng)前的數(shù)據(jù)庫中刪除一個或多個存儲過程。DROPPROCEDURE語句的語法格式為：

DROPPROCEDURE{存儲過程名稱

}[,...n]

【例8-4】同時刪除當(dāng)前數(shù)據(jù)庫中的兩個存儲過程，proc_a和proc_b。

在查詢分析器中運行如下命令，即可完成刪除操作：

DROPPROCEDUREproc_a,proc_b

GO5．使用DROPPROCEDURE語句刪除存儲過程

使用D218.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡介

觸發(fā)器屬于一種特殊的存儲過程，可以在其中包含復(fù)雜的SQL語句。觸發(fā)器與存儲過程的區(qū)別在于觸發(fā)器能夠自動執(zhí)行并且不含有參數(shù)。通?？梢栽谟|發(fā)器內(nèi)編寫一段自動執(zhí)行的程序，用于保證數(shù)據(jù)操作的完整性，從而擴展了對默認(rèn)值、約束和規(guī)則的完整性檢查。對表進行包括添加數(shù)據(jù)、刪除數(shù)據(jù)、更新數(shù)據(jù)中的一種或多種操作時，觸發(fā)器就會自動執(zhí)行。

觸發(fā)器可以劃分為三種類別：INSERT觸發(fā)器、DELETE觸發(fā)器、UPDATE觸發(fā)器。這三種觸發(fā)器分別在發(fā)生數(shù)據(jù)的添加、刪除、修改行為時自動執(zhí)行。8.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡222．觸發(fā)器的優(yōu)點觸發(fā)器有助于在添加、更新或刪除表中的記錄時保留表之間已定義的關(guān)系。觸發(fā)器可以自動調(diào)用，當(dāng)發(fā)生了對數(shù)據(jù)所作的任何修改時，與之相關(guān)的觸發(fā)器就會立刻被激活?？梢酝瓿蓴?shù)據(jù)庫中相關(guān)表之間的級聯(lián)修改。級聯(lián)修改是指為了保證數(shù)據(jù)之間的邏輯性以及依賴關(guān)系，在對一張表進行修改的同時，其他表中需要進行的修改能夠自動實現(xiàn)。觸發(fā)器可以對需要存儲的數(shù)據(jù)加以限制，并且能夠?qū)崿F(xiàn)比CHECK約束更為復(fù)雜的功能。在CHECK約束中不允許引用其他表中的列，而觸發(fā)器可以引用。3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式可以分為后觸發(fā)和替代觸發(fā)兩種方式。

后觸發(fā)：指能夠引發(fā)觸發(fā)器的修改操作在完成之后才執(zhí)行觸發(fā)器的行為方式。創(chuàng)建該類觸發(fā)器，需要使用AFTER關(guān)鍵字或者FOR關(guān)鍵字。

2．觸發(fā)器的優(yōu)點3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式23替代觸發(fā)：指定執(zhí)行一個觸發(fā)器，而不是執(zhí)行SQL語句，這種替代觸發(fā)語句的方式稱為替代觸發(fā)方式。8.2.3創(chuàng)建觸發(fā)器

可以使用企業(yè)管理器以及CREATETRIGGER語句兩種方式來創(chuàng)建觸發(fā)器。

1．使用企業(yè)管理器創(chuàng)建觸發(fā)器

①打開企業(yè)管理器，在控制臺根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點、數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，并雙擊【表】節(jié)點，在右側(cè)窗口的列表中選擇其中的一張表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級聯(lián)菜單中的【管理觸發(fā)器】命令，將打開圖8-9所示的【觸發(fā)器屬性】對話框。替代觸發(fā)：指定執(zhí)行一個觸發(fā)器，而不是執(zhí)行SQL語句，這種替代24圖8-9【觸發(fā)器屬性】對話框圖8-9【觸發(fā)器屬性】對話框25④在【觸發(fā)器屬性】對話框的文本框中，將[TRIGGERNAME]修改為新建觸發(fā)器的名稱，并輸入觸發(fā)器所包含的SQL語句。

⑤通過點擊【檢查語法】按鈕來判斷觸發(fā)器的語法是否正確，最后，點擊【確定】按鈕保存新建的觸發(fā)器。

2．使用CREATETRIGGER語句創(chuàng)建觸發(fā)器

CREATETRIGGER語句的部分語法格式：

CREATETRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

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

AS

SQL語句[

...n]④在【觸發(fā)器屬性】對話框的文本框中，將[TRIGGERNA26

其中：觸發(fā)器名稱必須符合標(biāo)識符命名規(guī)則，并且在同一個數(shù)據(jù)庫中觸發(fā)器的名稱不允許重復(fù)。觸發(fā)器名稱中可以省略所有者名稱。表名或視圖名稱是指建立觸發(fā)器所依賴的對象。也可以稱為觸發(fā)器表或觸發(fā)器視圖。WITHENCRYPTION該選項的作用為對觸發(fā)器中所包含的SQL語句進行加密。FOR或AFTER代表后觸發(fā)方式，即當(dāng)滿足所有的引用級聯(lián)操作和約束檢查后并且完成了SQL語句中指定的所有操作后，指定的觸發(fā)器才會被執(zhí)行。在視圖上不允許采用后觸發(fā)方式。INSTEADOF表示替代觸發(fā)方式，每一個INSERT、UPDATE、DELETE語句只能定義一個INSTEADOF觸發(fā)器。其中：27DELETE、INSERT、UPDATE三個選項中應(yīng)該至少選擇其一，用于表示在表或視圖上執(zhí)行了哪一類的操作時會將觸發(fā)器激活。SQL語句為觸發(fā)器中包含的條件以及需要執(zhí)行的操作。在執(zhí)行觸發(fā)器時，系統(tǒng)會自動創(chuàng)建兩張臨時表INSERTED、DELETED，這兩張表的結(jié)構(gòu)與觸發(fā)器所依賴的表類似，用于保存在用戶操作過程中被插入或被刪除的數(shù)據(jù)。每一個觸發(fā)器在執(zhí)行過程中都會產(chǎn)生與之相關(guān)的上述兩張臨時表，并且在執(zhí)行結(jié)束后，兩個臨時表會自動被系統(tǒng)刪除。對臨時表可以進行查詢操作，如SELECT*FROMDELETED，但是不能對臨時表進行修改DELETE、INSERT、UPDATE三個選項中應(yīng)該至28【例8-5】在數(shù)據(jù)庫XSCJ中設(shè)計一個觸發(fā)器，該觸發(fā)器的作用為：當(dāng)在班級表中刪除某一個班級時，在學(xué)生基本信息表中該班級所包含的學(xué)生信息也全部被刪除。

提示：在此例中，由于涉及到了班級表的刪除操作，因而需要設(shè)計一個DELETE類型的觸發(fā)器。

在查詢分析器中運行如下命令：

USEXSCJ

GO

CREATETRIGGERdel_bjON班級表

AFTERDELETE

AS

DELETEFROM學(xué)生基本信息表WHERE班級編號

IN(SELECT班級編號FROMDELETED)

GO

運行結(jié)果如圖8-10所示?！纠?-5】在數(shù)據(jù)庫XSCJ中設(shè)計一個觸發(fā)器，該觸發(fā)器的作用29圖8-10一個DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)庫XSCJ中設(shè)計一個觸發(fā)器，該觸發(fā)器能夠保證在學(xué)生基本信息表中添加新的紀(jì)錄時，新學(xué)生的班級編號必須已經(jīng)存在于班級表中。圖8-10一個DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)30提示：設(shè)計該觸發(fā)器有助于實現(xiàn)學(xué)生信息的完整性。在此例中由于涉及到了學(xué)生基本信息表中的添加操作，因而需要設(shè)計一個INSERT類型的觸發(fā)器。

在查詢分析器中運行如下命令：USEXSCJ

GO

CREATETRIGGERinsert_xsON學(xué)生基本信息表

AFTERINSERT

AS

IFEXISTS

(

SELECT*FROMINSERTED

WHERE班級編號IN(SELECT班級編號FROM班級表)

)

PRINT'添加成功！'

ELSE

BEGIN

PRINT'班級編號與現(xiàn)有的班級不符！'

ROLLBACKTRANSACTION

END提示：設(shè)計該觸發(fā)器有助于實現(xiàn)學(xué)生信息的完整性。在此例中由于涉31運行結(jié)果如圖8-11所示。圖8-11一個INSERT類型的觸發(fā)器

運行結(jié)果如圖8-11所示。圖8-11一個INSE32創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄進行測試，例如：

INSERTINTO學(xué)生基本信息表(學(xué)號,姓名,性別,族別,班級編號)VALUES('000108','王松濤','男','漢','20081001')

由于“班級表”中不存在編號為20081001的班級，因而添加操作將會被取消。ROLLBACKTRANSACTION用于回滾已經(jīng)完成的操作。運行結(jié)果如圖8-12所示。圖8-12添加操作被取消創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄338.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器

①在控制臺根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點、數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，并雙擊【表】節(jié)點，在右側(cè)窗口的列表中選擇某一張需要對其所建立的觸發(fā)器進行修該的表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級聯(lián)菜單中的【管理觸發(fā)器】命令，將打開【觸發(fā)器】屬性對話框，如圖8-13所示。

④在【觸發(fā)器屬性】對話框中，在【名稱】下拉列表框選擇一個已有的觸發(fā)器，并在【文本】編輯框中對其所包含的SQL語句進行修改。8.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器34圖8-13【觸發(fā)器】屬性對話框圖8-13【觸發(fā)器】屬性對話框35⑤在語法檢查無誤的情況下，點擊【確定】按鈕保存修改。

⑥如果需要刪除當(dāng)前觸發(fā)器，可以點擊【刪除】按鈕。

2．使用ALTERTRIGGER語句修改觸發(fā)器

ALTERTRIGGER語句與CREATETRIGGER語句的語法格式及其參數(shù)類似，部分語法格式為：

ALTERTRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

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

AS

SQL語句[

...n]⑤在語法檢查無誤的情況下，點擊【確定】按鈕保存修改。

⑥如果36【例8-7】修改del_bj觸發(fā)器，使得在班級表中刪除某一個班級時，不僅在學(xué)生基本信息表中該班級所包含的學(xué)生信息將被刪除，而且在成績表中與學(xué)生相關(guān)的數(shù)據(jù)也將被刪除。

在查詢分析器中運行如下命令：

ALTERTRIGGERdel_bjON班級表

AFTERDELETE

AS

DELETEFROM成績表WHERE學(xué)號

IN(SELECT學(xué)號FROM學(xué)生基本信息表WHERE班級編號

IN(SELECT班級編號FROMDELETED))

DELETEFROM學(xué)生基本信息表WHERE班級編號

IN(SELECT班級編號FROMDELETED)

運行結(jié)果如圖8-14所示?！纠?-7】修改del_bj觸發(fā)器，使得在班級表中刪除某一個37圖8-14修改del_bj觸發(fā)器3．使用DROPTRIGGER語句刪除觸發(fā)器

移除觸發(fā)器時，與觸發(fā)器有關(guān)的信息將從sysobjects和syscomments系統(tǒng)表中刪除。可以通過刪除觸發(fā)器或刪除表兩種方式移除觸發(fā)器。刪除表時，將除去所有與表相關(guān)聯(lián)的觸發(fā)器。圖8-14修改del_bj觸發(fā)器3．使用DROPTR38DROPTRIGGER語句的語法格式為：

DROPTRIGGER觸發(fā)器名稱[,...n]

觸發(fā)器名稱為數(shù)據(jù)庫中現(xiàn)有的觸發(fā)器。可以指定一個或多個觸發(fā)器名稱來刪除一個或多個觸發(fā)器?！纠?-8】刪除觸發(fā)器del_bj以及insert_xs。

在查詢分析器中運行如下命令：

USEXSCJ

GO

DROPTRIGGERdel_bj,insert_xs

運行結(jié)果如圖8-15所示。DROPTRIGGER語句的語法格式為：

DROPTRI39圖8-15刪除觸發(fā)器del_bj以及insert_xs圖8-15刪除觸發(fā)器del_bj以及insert_xs40本章小結(jié)

本章學(xué)習(xí)了存儲過程與觸發(fā)器的相關(guān)知識，存儲過程中可以將多條SQL語句集中在一起完成復(fù)雜的功能，從而用戶可以在不具有對數(shù)據(jù)對象訪問的權(quán)限下調(diào)用這些存儲過程完成的特定的操作。觸發(fā)器可以幫助用戶完成數(shù)據(jù)的驗證功能，從而能夠保證數(shù)據(jù)的一致性、完整性。本章小結(jié)

本章學(xué)習(xí)了存儲過程與觸發(fā)器的相關(guān)知識，存儲過程中可41練習(xí)與上機

一．選擇題

1．下面關(guān)于存儲過程的描述中哪些是正確的（）

A．自定義存儲過程與系統(tǒng)存儲過程名稱可以相同

B．存儲過程最多能夠支持64層的嵌套

C．命名存儲過程中的標(biāo)識符時，長度不能超過256個字符

D．存儲過程中參數(shù)的個數(shù)不能超過2100

2．用于創(chuàng)建存儲過程的SQL語句為（）

A．CREATEDATABASEB．CREATETRIGGER

C．CREATEPROCEDURED．CREATETABLE

3．用于修改存儲過程的SQL語句為（）

A．ALTERTABLEB．ALTERDATABASE

C．ALTERTRIGGERD．ALTERPROCEDURE

練習(xí)與上機

一．選擇題

1．下面關(guān)于存儲過程的描述中哪些是正42

4．下列對觸發(fā)器的描述中哪一個是錯誤的（）

A．觸發(fā)器屬于一種特殊的存儲過程

B．觸發(fā)器與存儲過程的區(qū)別在于觸發(fā)器能夠自動執(zhí)行并且不含有參數(shù)

C．觸發(fā)器有助于在添加、更新或刪除表中的記錄時保留表之間已定義的關(guān)系

D．既可以對INSERTED、DELETED臨時表進行查詢，也可以進行修改

4．下列對觸發(fā)器的描述中哪一個是錯誤的（）

A．觸發(fā)器43

二．填空題

1．一個存儲過程的名稱不能超過_______個字符。

2．使用_______語句可以對存儲過程進行重命名。

3．觸發(fā)器有_______、_______觸發(fā)方式。

4．用_________語句可以刪除觸發(fā)器。

5．觸發(fā)器可以劃分為三種類別____________、____________、____________。

三．簡答題

1．簡述什么是存儲過程？

2．簡要說明存儲過程的語法格式？

3．簡述什么是觸發(fā)器？

4．簡要說明觸發(fā)器的語法格式？

二．填空題

1．一個存儲過程的名稱不能超過_______個44項目實訓(xùn)

1．創(chuàng)建一個存儲過程，該存儲過程能夠?qū)崿F(xiàn)根據(jù)系部的編號查詢出系部中男生、女生的人數(shù)。

2．創(chuàng)建一個INSERT觸發(fā)器，該觸發(fā)器能夠在向成績表中添加數(shù)據(jù)時，自動判斷學(xué)號、課程編號、成績是否合法，如果非法則對插入操作進行回滾。

3.修改存儲過程的名字。

4．建立一個存儲過程，來修改student數(shù)據(jù)庫的c、sc兩張表，當(dāng)用戶修改c表中的cno后，修改cno表中對應(yīng)的數(shù)據(jù)。項目實訓(xùn)

1．創(chuàng)建一個存儲過程，該存儲過程能夠?qū)崿F(xiàn)根據(jù)系部的45第8章存儲過程與觸發(fā)器第8章存儲過程與觸發(fā)器46本章學(xué)習(xí)目標(biāo)l理解存儲過程和觸發(fā)器的作用l

熟練創(chuàng)建和管理存儲過程及觸發(fā)器l

靈活運用存儲過程和觸發(fā)器提高系統(tǒng)開發(fā)效率本章學(xué)習(xí)目標(biāo)47

8.1存儲過程

8.1.1存儲過程基礎(chǔ)知識

1．存儲過程簡介

存儲過程是存儲在服務(wù)器上的由SQL語句和控制流語句組成的一個預(yù)編譯集合。存儲過程劃分為系統(tǒng)存儲過程以及用戶存儲過程。一個存儲過程可以作為一個獨立的單元進行處理。在SQLServer中可以通過存儲過程來完成很多管理任務(wù)，利用一些常用的系統(tǒng)存儲過程可以很方便的查看、操作數(shù)據(jù)庫對象。系統(tǒng)存儲過程的命名通常以”sp_”作為前綴，并且存儲于Master數(shù)據(jù)庫中，如果用戶擁有足夠的權(quán)限，就可以在任何數(shù)據(jù)庫中調(diào)用這些存儲過程。

存儲過程提供了一種封裝某一個需要重復(fù)執(zhí)行任務(wù)的方法。一旦定義了一個存儲過程之后，在應(yīng)用程序中就可以對其進行調(diào)用。在存儲過程中，不僅可以包含程序流、邏輯以及對數(shù)據(jù)庫的查詢，而且也可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集。8.1存儲過程

8.1.1存儲過程基礎(chǔ)知識

1．存48所以通過設(shè)計自定義用戶存儲過程，用戶可以使其實現(xiàn)強大的編程功能。

使用存儲過程可以比單獨的SQL語句完成更為復(fù)雜的功能，并且系統(tǒng)會對存儲過程中的SQL語句進行了預(yù)編譯處理，使得執(zhí)行速度有了大幅度的提升。存儲過程被第一次調(diào)用后，會保存在高速緩沖區(qū)中，這樣再次執(zhí)行同一個存儲過程時，會提高了重復(fù)調(diào)用的效率。在實際應(yīng)用中，可以將復(fù)雜的商業(yè)規(guī)則封裝在存儲過程中，從而提高程序語句的利用率。

2．存儲過程的優(yōu)點存儲過程一旦執(zhí)行一次后，其執(zhí)行的計劃就會駐留在計算機的高速緩沖存儲器中。其后對同一個存儲過程的調(diào)用就可以直接利用編譯后在高速緩存中的二進制形式來完成操作?？梢栽趩蝹€存儲過程中執(zhí)行一系列SQL語句，因而可以用于設(shè)計、封裝企業(yè)的功能模塊。應(yīng)用程序使用相同的存儲過程進行操作，確保了在數(shù)據(jù)訪問、操縱的一致性。所以通過設(shè)計自定義用戶存儲過程，用戶可以使其實49可以在當(dāng)前的存儲過程內(nèi)部引用其它存儲過程，這樣可以將復(fù)雜語句進行簡化。提供了一種數(shù)據(jù)庫訪問的途徑，只需要用戶擁有執(zhí)行存儲過程的權(quán)限，那么通過使用存儲過程就可以完成對數(shù)據(jù)庫的各種操作，如添加數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)等，而不需要考慮用戶是否擁有存儲過程所處理的數(shù)據(jù)對象的訪問權(quán)限。3．使用存儲過程的注意事項在命名自定義存儲過程時避免與系統(tǒng)存儲過程名相同。存儲過程最多能夠支持32層的嵌套。命名存儲過程中的標(biāo)識符時，長度不能超過128個字符。存儲過程中參數(shù)的個數(shù)不能超過2100。3．使用存儲過程的注意事項508.1.2創(chuàng)建和執(zhí)行存儲過程

在SQLServer中，既可以通過企業(yè)管理器，也可以通過使用CREATEPROCEDRUE語句的方式來創(chuàng)建存儲過程。

1．使用企業(yè)管理器創(chuàng)建存儲過程

步驟：

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器節(jié)點、數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫并在該節(jié)點上點擊鼠標(biāo)右鍵，選擇【新建】菜單下的【存儲過程】級聯(lián)菜單，將彈出如圖8-1所示的對話框。圖8-1新建存儲過程對話框8.1.2創(chuàng)建和執(zhí)行存儲過程

在SQLServer中，51③在【存儲過程屬性】對話框中的【文本】編輯框中，對[OWNER]以及[PROCEDURENAME]部分進行修改，分別修改為該存儲過程的所有者和存儲過程名稱，并且輸入該存儲過程所包含的SQL語句。如圖8-2所示為創(chuàng)建了一個用于查詢學(xué)生信息的存儲過程。圖8-2用于查詢學(xué)生信息的存儲過程③在【存儲過程屬性】對話框中的【文本】編輯框中，對[OWNE52④如果需要驗證存儲過程中的SQL語句的正確性，可以單擊【檢查語法】按鈕，如果沒有語法錯誤則會彈出如圖8-3所示的“語法檢查成功”的對話框。圖8-3“語法檢查成功”的提示對話框⑤當(dāng)存儲過程中沒有語法錯誤后，可以單擊【確定】按鈕來保存自定義的存儲過程。④如果需要驗證存儲過程中的SQL語句的正確性，可以單擊【檢查532．使用CREATEPROCEDURE語句來創(chuàng)建存儲過程

該語句可以創(chuàng)建永久使用的存儲過程，也可以創(chuàng)建局部臨時過程、全局臨時過程。

語法格式為：

CREATEPROCEDURE存儲過程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語句[...n]]其中：存儲過程名稱必須符合標(biāo)識符命名規(guī)則，而且對于數(shù)據(jù)庫以及所有者命名必須唯一。一個完整的命名不能超過128個字符。2．使用CREATEPROCEDURE語句來創(chuàng)建存儲過程54【例8-1】使用CREATEPROCEDURE語句創(chuàng)建一個名稱為proc_bjrs的存儲過程，用于檢索現(xiàn)有班級及人數(shù)。

CREATEPROCEDUREproc_bjrsAS

SELECTDISTINCT(班級表.班級編號),班級表.班級名稱，人數(shù)=COUNT(學(xué)生基本信息表.學(xué)號)

FROM學(xué)生基本信息表,班級表

WHERE班級表.班級編號=學(xué)生基本信息表.班級編號

GROUPBY班級表.班級編號,班級表.班級名稱

GO

在查詢分析器中執(zhí)行以上命令后，然后執(zhí)行該存儲過程EXECUTEproc_bjrs，運行結(jié)果如圖8-4所示?！纠?-1】使用CREATEPROCEDURE語句創(chuàng)建一個55圖8-4檢索現(xiàn)有班級以及人數(shù)圖8-4檢索現(xiàn)有班級以及人數(shù)56【例8-2】設(shè)計一個帶有參數(shù)的存儲過程，該參數(shù)用于傳遞班級編號，根據(jù)該參數(shù)在學(xué)生基本信息表中檢索出某一個班級中所有學(xué)生的信息。

CREATEPROCEDUREproc_bjcx@bjbhVARCHAR(8)

AS

SELECT*FROM學(xué)生基本信息表WHERE班級編號=@bjbh

GO

其中參數(shù)@bjbh用于接收班級編號，在實際調(diào)用中根據(jù)該參數(shù)將會返回一個結(jié)果集。例如，EXECUTEproc_bjcx'20051003'，將會顯示班級編號為20051003的學(xué)生信息。運行結(jié)果如圖8-5所示【例8-2】設(shè)計一個帶有參數(shù)的存儲過程，該參數(shù)用于傳遞班級編57圖8-5用于傳遞班級編號的存儲過程圖8-5用于傳遞班級編號的存儲過程588.1.3修改與刪除存儲過程

在SQLServer中，可以通過企業(yè)管理器或SQL語句兩種方式修改或刪除存儲過程。

1．使用企業(yè)管理器修改存儲過程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，展開該數(shù)據(jù)庫節(jié)點后點擊【存儲過程】，在右側(cè)的窗口中將會顯示出存儲過程的列表。右擊需要修改的存儲過程，在彈出菜單中選擇【屬性】，彈出存儲過程屬性對話框，如圖8-6所示。圖8-6【存儲過程屬性】對話框8.1.3修改與刪除存儲過程

在SQLServer中，59③在【存儲過程屬性】對話框的【文本】編輯框中修改包含的SQL語句。需要注意的是，存儲過程的名稱不能修改。

④在檢查了語法的正確性之后，可以單擊【確定】按鈕保存修改并關(guān)閉對話框。

2．使用企業(yè)管理器重命名存儲過程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，展開該數(shù)據(jù)庫節(jié)點后點擊【存儲過程】，在右側(cè)的窗口中將會顯示出存儲過程的列表。右擊需要重命名的存儲過程，并選擇彈出菜單中的【重命名】命令，如圖8-7所示。③在【存儲過程屬性】對話框的【文本】編輯框中修改包含的SQL60圖8-7彈出菜單中的【重命名】命令③重新輸入存儲過程的名稱后，按下回車鍵完成修改。圖8-7彈出菜單中的【重命名】命令③重新輸入存儲過程的名61

3．使用ALTERPROCEDURE語句修改存儲過程

ALTERPROCEDURE語句可以對數(shù)據(jù)庫中已有的存儲過程進行修該，但不會更改權(quán)限，也不影響相關(guān)的存儲過程或觸發(fā)器。該語句的語法格式為：

ALTERPROCEDURE存儲過程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語句[...n]]

其中，存儲過程名稱為數(shù)據(jù)庫中已經(jīng)存在的過程名，語法中的常用選項與CREATEPROCEDURE語句一致。

3．使用ALTERPROCEDURE語句修改存儲過程

A62【例8-3】修改已經(jīng)創(chuàng)建的存儲過程proc_bjcx及其功能，將其修改為可以根據(jù)系部編號來查詢某一個系部中所有學(xué)生的信息。修改后將存儲過程名稱重命名為proc_xbcx。

在查詢分析器中運行如下命令，即可完成重命名：

ALTERPROCEDUREproc_bjcx@xbbhVARCHAR(2)

AS

SELECT*FROM學(xué)生基本信息表

WHERE班級編號

IN(SELECT班級編號FROM班級表WHERE系部編號=@xbbh)

GO

SP_RENAME'proc_bjcx','proc_xbcx'

GO【例8-3】修改已經(jīng)創(chuàng)建的存儲過程proc_bjcx及其功能63使用SP_RENAME可以重新命名一個現(xiàn)有的存儲過程。SP_RENAME的語法格式為：

SP_RENAME原名稱,新名稱。

完成修改后查詢系部編號為’01’的學(xué)生信息。

EXECproc_xbcx'01'

4．使用企業(yè)管理器刪除存儲過程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，展開該數(shù)據(jù)庫后點擊【存儲過程】，在右側(cè)的窗口中顯示出存儲過程的列表。右擊需要刪除的存儲過程，并在彈出菜單中選擇【刪除】命令，將彈出【除去對象】對話框，如圖8-8所示。

③在【除去對象】對話框中單擊【全部除去】按鈕將會完成對存儲過程的刪除操作。使用SP_RENAME可以重新命名一個現(xiàn)有的存儲過程。SP_64圖8-8【除去對象】對話框圖8-8【除去對象】對話框655．使用DROPPROCEDURE語句刪除存儲過程

使用DROPPROCEDURE語句可以從當(dāng)前的數(shù)據(jù)庫中刪除一個或多個存儲過程。DROPPROCEDURE語句的語法格式為：

DROPPROCEDURE{存儲過程名稱

}[,...n]

【例8-4】同時刪除當(dāng)前數(shù)據(jù)庫中的兩個存儲過程，proc_a和proc_b。

在查詢分析器中運行如下命令，即可完成刪除操作：

DROPPROCEDUREproc_a,proc_b

GO5．使用DROPPROCEDURE語句刪除存儲過程

使用D668.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡介

觸發(fā)器屬于一種特殊的存儲過程，可以在其中包含復(fù)雜的SQL語句。觸發(fā)器與存儲過程的區(qū)別在于觸發(fā)器能夠自動執(zhí)行并且不含有參數(shù)。通常可以在觸發(fā)器內(nèi)編寫一段自動執(zhí)行的程序，用于保證數(shù)據(jù)操作的完整性，從而擴展了對默認(rèn)值、約束和規(guī)則的完整性檢查。對表進行包括添加數(shù)據(jù)、刪除數(shù)據(jù)、更新數(shù)據(jù)中的一種或多種操作時，觸發(fā)器就會自動執(zhí)行。

觸發(fā)器可以劃分為三種類別：INSERT觸發(fā)器、DELETE觸發(fā)器、UPDATE觸發(fā)器。這三種觸發(fā)器分別在發(fā)生數(shù)據(jù)的添加、刪除、修改行為時自動執(zhí)行。8.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡672．觸發(fā)器的優(yōu)點觸發(fā)器有助于在添加、更新或刪除表中的記錄時保留表之間已定義的關(guān)系。觸發(fā)器可以自動調(diào)用，當(dāng)發(fā)生了對數(shù)據(jù)所作的任何修改時，與之相關(guān)的觸發(fā)器就會立刻被激活?？梢酝瓿蓴?shù)據(jù)庫中相關(guān)表之間的級聯(lián)修改。級聯(lián)修改是指為了保證數(shù)據(jù)之間的邏輯性以及依賴關(guān)系，在對一張表進行修改的同時，其他表中需要進行的修改能夠自動實現(xiàn)。觸發(fā)器可以對需要存儲的數(shù)據(jù)加以限制，并且能夠?qū)崿F(xiàn)比CHECK約束更為復(fù)雜的功能。在CHECK約束中不允許引用其他表中的列，而觸發(fā)器可以引用。3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式可以分為后觸發(fā)和替代觸發(fā)兩種方式。

后觸發(fā)：指能夠引發(fā)觸發(fā)器的修改操作在完成之后才執(zhí)行觸發(fā)器的行為方式。創(chuàng)建該類觸發(fā)器，需要使用AFTER關(guān)鍵字或者FOR關(guān)鍵字。

2．觸發(fā)器的優(yōu)點3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式68替代觸發(fā)：指定執(zhí)行一個觸發(fā)器，而不是執(zhí)行SQL語句，這種替代觸發(fā)語句的方式稱為替代觸發(fā)方式。8.2.3創(chuàng)建觸發(fā)器

可以使用企業(yè)管理器以及CREATETRIGGER語句兩種方式來創(chuàng)建觸發(fā)器。

1．使用企業(yè)管理器創(chuàng)建觸發(fā)器

①打開企業(yè)管理器，在控制臺根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點、數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，并雙擊【表】節(jié)點，在右側(cè)窗口的列表中選擇其中的一張表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級聯(lián)菜單中的【管理觸發(fā)器】命令，將打開圖8-9所示的【觸發(fā)器屬性】對話框。替代觸發(fā)：指定執(zhí)行一個觸發(fā)器，而不是執(zhí)行SQL語句，這種替代69圖8-9【觸發(fā)器屬性】對話框圖8-9【觸發(fā)器屬性】對話框70④在【觸發(fā)器屬性】對話框的文本框中，將[TRIGGERNAME]修改為新建觸發(fā)器的名稱，并輸入觸發(fā)器所包含的SQL語句。

⑤通過點擊【檢查語法】按鈕來判斷觸發(fā)器的語法是否正確，最后，點擊【確定】按鈕保存新建的觸發(fā)器。

2．使用CREATETRIGGER語句創(chuàng)建觸發(fā)器

CREATETRIGGER語句的部分語法格式：

CREATETRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

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

AS

SQL語句[

...n]④在【觸發(fā)器屬性】對話框的文本框中，將[TRIGGERNA71

其中：觸發(fā)器名稱必須符合標(biāo)識符命名規(guī)則，并且在同一個數(shù)據(jù)庫中觸發(fā)器的名稱不允許重復(fù)。觸發(fā)器名稱中可以省略所有者名稱。表名或視圖名稱是指建立觸發(fā)器所依賴的對象。也可以稱為觸發(fā)器表或觸發(fā)器視圖。WITHENCRYPTION該選項的作用為對觸發(fā)器中所包含的SQL語句進行加密。FOR或AFTER代表后觸發(fā)方式，即當(dāng)滿足所有的引用級聯(lián)操作和約束檢查后并且完成了SQL語句中指定的所有操作后，指定的觸發(fā)器才會被執(zhí)行。在視圖上不允許采用后觸發(fā)方式。INSTEADOF表示替代觸發(fā)方式，每一個INSERT、UPDATE、DELETE語句只能定義一個INSTEADOF觸發(fā)器。其中：72DELETE、INSERT、UPDATE三個選項中應(yīng)該至少選擇其一，用于表示在表或視圖上執(zhí)行了哪一類的操作時會將觸發(fā)器激活。SQL語句為觸發(fā)器中包含的條件以及需要執(zhí)行的操作。在執(zhí)行觸發(fā)器時，系統(tǒng)會自動創(chuàng)建兩張臨時表INSERTED、DELETED，這兩張表的結(jié)構(gòu)與觸發(fā)器所依賴的表類似，用于保存在用戶操作過程中被插入或被刪除的數(shù)據(jù)。每一個觸發(fā)器在執(zhí)行過程中都會產(chǎn)生與之相關(guān)的上述兩張臨時表，并且在執(zhí)行結(jié)束后，兩個臨時表會自動被系統(tǒng)刪除。對臨時表可以進行查詢操作，如SELECT*FROMDELETED，但是不能對臨時表進行修改DELETE、INSERT、UPDATE三個選項中應(yīng)該至73【例8-5】在數(shù)據(jù)庫XSCJ中設(shè)計一個觸發(fā)器，該觸發(fā)器的作用為：當(dāng)在班級表中刪除某一個班級時，在學(xué)生基本信息表中該班級所包含的學(xué)生信息也全部被刪除。

提示：在此例中，由于涉及到了班級表的刪除操作，因而需要設(shè)計一個DELETE類型的觸發(fā)器。

在查詢分析器中運行如下命令：

USEXSCJ

GO

CREATETRIGGERdel_bjON班級表

AFTERDELETE

AS

DELETEFROM學(xué)生基本信息表WHERE班級編號

IN(SELECT班級編號FROMDELETED)

GO

運行結(jié)果如圖8-10所示。【例8-5】在數(shù)據(jù)庫XSCJ中設(shè)計一個觸發(fā)器，該觸發(fā)器的作用74圖8-10一個DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)庫XSCJ中設(shè)計一個觸發(fā)器，該觸發(fā)器能夠保證在學(xué)生基本信息表中添加新的紀(jì)錄時，新學(xué)生的班級編號必須已經(jīng)存在于班級表中。圖8-10一個DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)75提示：設(shè)計該觸發(fā)器有助于實現(xiàn)學(xué)生信息的完整性。在此例中由于涉及到了學(xué)生基本信息表中的添加操作，因而需要設(shè)計一個INSERT類型的觸發(fā)器。

在查詢分析器中運行如下命令：USEXSCJ

GO

CREATETRIGGERinsert_xsON學(xué)生基本信息表

AFTERINSERT

AS

IFEXISTS

(

SELECT*FROMINSERTED

WHERE班級編號IN(SELECT班級編號FROM班級表)

)

PRINT'添加成功！'

ELSE

BEGIN

PRINT'班級編號與現(xiàn)有的班級不符！'

ROLLBACKTRANSACTION

END提示：設(shè)計該觸發(fā)器有助于實現(xiàn)學(xué)生信息的完整性。在此例中由于涉76運行結(jié)果如圖8-11所示。圖8-11一個INSERT類型的觸發(fā)器

運行結(jié)果如圖8-11所示。圖8-11一個INSE77創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄進行測試，例如：

INSERTINTO學(xué)生基本信息表(學(xué)號,姓名,性別,族別,班級編號)VALUES('000108','王松濤','男','漢','20081001')

由于“班級表”中不存在編號為20081001的班級，因而添加操作將會被取消。ROLLBACKTRANSACTION用于回滾已經(jīng)完成的操作。運行結(jié)果如圖8-12所示。圖8-12添加操作被取消創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄788.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器

①在控制臺根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點、數(shù)據(jù)庫節(jié)點。

②選擇某一個數(shù)據(jù)庫，并雙擊【表】節(jié)點，在右側(cè)窗口的列表中選擇某一張需要對其所建立的觸發(fā)器進行修該的表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級聯(lián)菜單中的【管理觸發(fā)器】命令，將打開【觸發(fā)器】屬性對話框，如圖8-13所示。

④在【觸發(fā)器屬性】對話框中，在【名稱】下拉列表框選擇一個已有的觸發(fā)器，并在【文本】編輯框中對其所包含的SQL語句進行修改。8.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器79圖8-13【觸發(fā)器】屬性對話框圖8-13【觸發(fā)器】屬性對話框80⑤在語法檢查無誤的情況下，點擊【確定】按鈕保存修改。

⑥如果需要刪除當(dāng)前觸發(fā)器，可以點擊【刪除】按鈕。

2．使用ALTERTRIGGER語句修改觸發(fā)器

ALTERTRIGGER語句與CREATETRIGGER語句的語法格式及其參數(shù)類似，部分語法格式為：

ALTERTRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

FOR|AFTER|INSTEADOF[DELETE][,][INSE