實(shí)驗(yàn)六觸發(fā)器、存儲(chǔ)過(guò)程編程實(shí)驗(yàn)

1、實(shí)驗(yàn)六觸發(fā)器、存儲(chǔ)過(guò)程編程實(shí)驗(yàn)一、實(shí)驗(yàn)?zāi)康膶W(xué)習(xí)存儲(chǔ)過(guò)程和觸發(fā)器的創(chuàng)建和使用方法。二、實(shí)驗(yàn)環(huán)境硬件： PC 機(jī)軟件： SQL Server 2000三、實(shí)驗(yàn)原理1. 觸發(fā)器觸發(fā)器是一種特殊的過(guò)程，它不帶參數(shù)， 不被用戶和程序調(diào)用，只能由用戶對(duì)數(shù)據(jù)庫(kù)中的表的操作（插入、刪除、修改）觸發(fā)。因此，可以利用觸發(fā)器來(lái)維護(hù)表間的數(shù)據(jù)一致性。觸發(fā)器只能在表上建立，一張表最多可有3 個(gè)觸發(fā)器，即插入觸發(fā)器、刪除觸發(fā)器、修改觸發(fā)器，分別由插入、刪除、修改操作觸發(fā)。觸發(fā)器可以查詢其它表，而且可以包含復(fù)雜的SQL 語(yǔ)句。它們主要用于強(qiáng)制復(fù)雜的業(yè)務(wù)規(guī)則及數(shù)據(jù)完整性。創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器時(shí)需指定：名稱。在其上定義觸發(fā)器的

2、表。觸發(fā)器將何時(shí)激發(fā)。激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句。有效選項(xiàng)為INSERT 、UPDATE或 DELETE 。多個(gè)數(shù)據(jù)修改語(yǔ)句可激活同一個(gè)觸發(fā)器。例如，觸發(fā)器可由INSERT 或 UPDATE 語(yǔ)句激活。執(zhí)行觸發(fā)操作的編程語(yǔ)句。語(yǔ)法：CREATETRIGGER觸發(fā)器名ON 表名|視圖名 FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE ASSQL 語(yǔ)句塊RETURN使用 inserted 和 deleted 表觸發(fā)器語(yǔ)句中使用了兩種特殊的表：deleted 表和 inserted 表。Microsoft SQL Server 2000自動(dòng)創(chuàng)建和管理

3、這些表?？梢允褂眠@兩個(gè)臨時(shí)的駐留內(nèi)存的表測(cè)試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件；然而，不能直接對(duì)表中的數(shù)據(jù)進(jìn)行更改。inserted 和 deleted 表主要用于觸發(fā)器中：擴(kuò)展表間引用完整性。在以視圖為基礎(chǔ)的基表中插入或更新數(shù)據(jù)。檢查錯(cuò)誤并基于錯(cuò)誤采取行動(dòng)。找到數(shù)據(jù)修改前后表狀態(tài)的差異，并基于此差異采取行動(dòng)。Deleted 表用于存儲(chǔ) DELETE 和 UPDATE 語(yǔ)句所影響的行的復(fù)本。在執(zhí)行 DELETE 或 UPDATE 語(yǔ)句時(shí)， 行從觸發(fā)器表中刪除， 并傳輸?shù)?deleted 表中。 Deleted 表和觸發(fā)器表通常沒有相同的行。Inserted 表用于存儲(chǔ)INSERT 和 U

4、PDATE 語(yǔ)句所影響的行的副本。在一個(gè)插入或更新事務(wù)處理中，新建行被同時(shí)添加到inserted 表和觸發(fā)器表中。新行的副本。更新事務(wù)類似于在刪除之后執(zhí)行插入；首先舊行被復(fù)制到制到觸發(fā)器表和inserted 表中。在設(shè)置觸發(fā)器條件時(shí)，應(yīng)當(dāng)為引發(fā)觸發(fā)器的操作恰當(dāng)使用Inserted 表中的行是觸發(fā)器表中deleted 表中，然后新行被復(fù)inserted 和 deleted 表。雖然在測(cè)試 INSERT 時(shí)引用 deleted 表或在測(cè)試 DELETE 時(shí)引用 inserted 表不會(huì)引起任何錯(cuò)誤，但是在這種情形下這些觸發(fā)器測(cè)試表中不會(huì)包含任何行。刪除觸發(fā)器從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)觸發(fā)器。語(yǔ)法

5、：DROPTRIGGER觸發(fā)器名 ,.n 2. 自定義數(shù)據(jù)類型SQL Server 2000 允許用戶定義自己的數(shù)據(jù)類型。創(chuàng)建用戶定義的數(shù)據(jù)類型語(yǔ)法：sp_addtype類型名 ,系統(tǒng)數(shù)據(jù)類型名,屬性 刪除用戶定義的數(shù)據(jù)類型語(yǔ)法：sp_droptype類型名 查看用戶定義的數(shù)據(jù)類型語(yǔ)法：sp_help類型名 3. 函數(shù)SQL Server 2000 支持兩種函數(shù)類型：內(nèi)置函數(shù)按 Transact-SQL 參考中定義的方式運(yùn)行且不能修改。只有使用Transact-SQL 參考中所定義語(yǔ)法的Transact-SQL 語(yǔ)句才能引用這類函數(shù)。用戶定義函數(shù)可以用 CREATE FUNCTION語(yǔ)句定義自

6、己的Transact-SQL 函數(shù)。創(chuàng)建函數(shù)語(yǔ)法：CREATEFUNCTION函數(shù)名(參數(shù)AS類型 = default ,.n )RETURNS函數(shù)返回值類型| TABLEASBEGIN函數(shù)體語(yǔ)句RETURN函數(shù)返回值| SELECT 語(yǔ)句END函數(shù)調(diào)用語(yǔ)法：DeclareSelect變量名 數(shù)據(jù)類型 變量名 = 函數(shù)名( 參數(shù)表達(dá)式 ,. )刪除函數(shù)語(yǔ)法：DROPFUNCTION函數(shù)名 ,.n 4. 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是 SQL 語(yǔ)句和可選控制流語(yǔ)句的預(yù)編譯集合，以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理。它在建立時(shí)由 RDBMS 編譯和優(yōu)化，其執(zhí)行代碼存儲(chǔ)于數(shù)據(jù)庫(kù)中的程序中。存儲(chǔ)過(guò)程可包含程序流、邏輯

7、以及對(duì)數(shù)據(jù)庫(kù)的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及返回值。存儲(chǔ)過(guò)程分為兩類：系統(tǒng)存儲(chǔ)過(guò)程和自定義存儲(chǔ)過(guò)程。系統(tǒng)存儲(chǔ)過(guò)程在系統(tǒng)安裝時(shí)自動(dòng)裝載于系統(tǒng)數(shù)據(jù)庫(kù)中，便于用戶或數(shù)據(jù)庫(kù)管理員管理和維護(hù)數(shù)據(jù)庫(kù)中的各種數(shù)據(jù)信息和對(duì)象。系統(tǒng)存儲(chǔ)過(guò)程以“sp_”開頭。用戶自定義存儲(chǔ)過(guò)程，由用戶定義。創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)法：CREATEPROC過(guò)程名 ( 參數(shù)變量數(shù)據(jù)類型 = default OUTPUT ) ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ASSQL語(yǔ)句 .n RETURN存儲(chǔ)過(guò)程執(zhí)行狀態(tài)執(zhí)行存儲(chǔ)過(guò)程可在命令行或批中調(diào)

8、用。如在批中調(diào)用，應(yīng)加EXEC過(guò)程名參數(shù)OUTPUT ,.n刪除存儲(chǔ)過(guò)程語(yǔ)法：DROPPROC過(guò)程名查看存儲(chǔ)過(guò)程語(yǔ)法：sp_helptext過(guò)程名更改存儲(chǔ)過(guò)程名稱語(yǔ)法：sp_rename舊過(guò)程名 , 新過(guò)程名四、實(shí)驗(yàn)內(nèi)容1. 利用 T-SQL 語(yǔ)句創(chuàng)建觸發(fā)器，并測(cè)試其作用要求：在 person 表上創(chuàng)建一個(gè)觸發(fā)器，當(dāng)刪除表person 中的員工信息時(shí)，級(jí)聯(lián)刪除表pay中該員工的工資信息。參考：CREATEONFORTRIGGERpersonDELETEperson_delASIFrowcount=0RETURNDELETEpayFROMpay t, deleted dWHEREt.No=d.N

9、oRETURN注： rowcount=0 是 SQL Server 提供的系統(tǒng)變量，其值表示表中有幾行記錄被刪除了。在 pay 表上創(chuàng)建一個(gè)觸發(fā)器，檢查在修改該表時(shí)是否有不存在于person 表中的員工代碼出現(xiàn)。參考：CREATEONFORTRIGGERpayUpdatepay_updateASDeclarenum_rowsintSelectnum_rows=rowcountIFnum_rows=0RETURNIF(Select From Wherecount(*)person p, inserted ip.No=i.No) !=num_rowsBeginraiserrorrollback33

10、333'試圖修改非法的員工號(hào)到transactionpay 表中 'returnEndRETURN在 pay 表上創(chuàng)建一個(gè)觸發(fā)器，當(dāng)向該表插入數(shù)據(jù)時(shí)，必須參考表person 中的 No。參考：CREATETRIGGERpay_insertONpayFORInsertASDeclarenum_rowsintSelectnum_rows=rowcountIFnum_rows=0RETURNIF(Selectcount(*)Fromperson p, inserted iWherep.No=i.No) !=num_rowsBeginraiserror33333'試圖插入非法的

11、員工號(hào)到rollbacktransactionpay 表中 'returnEndRETURN2. 利用 T-SQL 語(yǔ)句自定義數(shù)據(jù)類型要求：定義一個(gè)數(shù)據(jù)類型d_no ，將其長(zhǎng)度定義為2B ，并以此來(lái)重新定義dept 表。參考：sp_addtyped_no, 'char(2)', 'NOT NULL'gocreate table dept1(deptnod_no,deptnamevarchar(10)Not Null)3. 利用 T-SQL 語(yǔ)句創(chuàng)建函數(shù)，并調(diào)用要求：創(chuàng)建一個(gè)函數(shù)Fun_CheckNo ，檢測(cè)給定的員工號(hào)是否存在，如果存在返回0，否則返回-

12、1。參考：CreatefunctionFun_CheckNo(pno char(6)ReturnsintegerasBeginDeclarenumIntIfExists(SelectnofrompersonWhereno=pno)Select num=0ElseSelect num=-1ReturnnumEND調(diào)用函數(shù)Fun_CheckNo ，如果返回0，則向表pay 中插入一行該員工的工資記錄。參考：DeclarenumIntSelectnum=DBO. Fun_CheckNo('000008')Ifnum=0Insertpayvalues('000008',

13、2005,2,2200,280,12.4)4.利用 T-SQL 語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程，并調(diào)用要求：創(chuàng)建一個(gè)存儲(chǔ)過(guò)程Pro_CalAge ，根據(jù) person 表中的出生日期計(jì)算其實(shí)際年齡。參考：CreatePROCPro_CalAgecode char(6), age int OUTPUTAsDeclarebirth varchar(4), today varchar(4)Selectbirth=DATENAME(year,birthday)FrompersonWhereno=codeSelecttoday=DATENAME(year,GETDATE()Selectage=CONVERT(INT,today)-CONVERT(I