1、實驗六觸發器、存儲過程編程實驗一、實驗目的學習存儲過程和觸發器的創建和使用方法。二、實驗環境硬件： PC 機軟件： SQL Server 2000三、實驗原理1. 觸發器觸發器是一種特殊的過程，它不帶參數， 不被用戶和程序調用，只能由用戶對數據庫中的表的操作（插入、刪除、修改）觸發。因此，可以利用觸發器來維護表間的數據一致性。觸發器只能在表上建立，一張表最多可有3 個觸發器，即插入觸發器、刪除觸發器、修改觸發器，分別由插入、刪除、修改操作觸發。觸發器可以查詢其它表，而且可以包含復雜的SQL 語句。它們主要用于強制復雜的業務規則及數據完整性。創建觸發器創建觸發器時需指定：名稱。在其上定義觸發器的

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

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

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

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

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

7、以及對數據庫的查詢。它們可以接受參數、輸出參數、返回單個或多個結果集以及返回值。存儲過程分為兩類：系統存儲過程和自定義存儲過程。系統存儲過程在系統安裝時自動裝載于系統數據庫中，便于用戶或數據庫管理員管理和維護數據庫中的各種數據信息和對象。系統存儲過程以“sp_”開頭。用戶自定義存儲過程，由用戶定義。創建存儲過程語法：CREATEPROC過程名 ( 參數變量數據類型 = default OUTPUT ) ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ASSQL語句 .n RETURN存儲過程執行狀態執行存儲過程可在命令行或批中調

8、用。如在批中調用，應加EXEC過程名參數OUTPUT ,.n刪除存儲過程語法：DROPPROC過程名查看存儲過程語法：sp_helptext過程名更改存儲過程名稱語法：sp_rename舊過程名 , 新過程名四、實驗內容1. 利用 T-SQL 語句創建觸發器，并測試其作用要求：在 person 表上創建一個觸發器，當刪除表person 中的員工信息時，級聯刪除表pay中該員工的工資信息。參考：CREATEONFORTRIGGERpersonDELETEperson_delASIFrowcount=0RETURNDELETEpayFROMpay t, deleted dWHEREt.No=d.N

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

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

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

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

13、2005,2,2200,280,12.4)4.利用 T-SQL 語句創建存儲過程，并調用要求：創建一個存儲過程Pro_CalAge ，根據 person 表中的出生日期計算其實際年齡。參考：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