數據庫實驗九-存儲過程和觸發(fā)器班

實驗09存儲過程和觸發(fā)器〖儀器設備〗每位學生一臺PC機〔標準配制，1G內存〕，裝有SQLServer2005或者其他DBMS〖目的要求〗1.了解幾個常用的系統(tǒng)存儲過程。2.掌握如何使用T-SQL語句創(chuàng)立、執(zhí)行和重編譯存儲過程。3.了解如何使用企業(yè)管理器管理存儲過程。4.掌握如何使用T-SQL語句創(chuàng)立觸發(fā)器。 5.了解如何使用企業(yè)管理器創(chuàng)立觸發(fā)器。6.掌握AFTER觸發(fā)器的用法，并了解INSTEADOF觸發(fā)器的用法?！紝嶒瀮热荨?.創(chuàng)立存儲過程。(1)創(chuàng)立一個存儲過程，查看“2”號課程的選修情況，包括選修該課程的學號、姓名和成績。執(zhí)行存儲過程(1)上面所建立的存儲過程只能對“2”號課程的選修情況進行查看，要想對所有課程進行隨機查看，需要進行參數的傳遞。(2)按位置傳遞參數(3)通過參數名傳遞參數(1)執(zhí)行存儲過程ssc_4時，如果沒有給出參數，系統(tǒng)會報錯。如果希望不給參數時，能查詢所有課程的進修情況，那么可以使用默認參數值來實現。(2)執(zhí)行下面兩條語句，比擬執(zhí)行結果。4.使用輸出參數。(1)創(chuàng)立一個存儲過程ssc_6，獲得選修某門課程的總人數。(2)執(zhí)行存儲過程ssc_6。5.使用返回值。(1)創(chuàng)立一個返回執(zhí)行狀態(tài)碼的存儲過程ssc_7，它接受課程號為輸入參數，如果執(zhí)行成功，返回0；如果沒有給出課程號，返回錯誤碼1；如果給出的課程號不存在，返回錯誤碼2；如果出現其他錯誤，返回錯誤碼3。執(zhí)行存儲過程ssc_7。6.修改存儲過程-使用對象資源管理器修改存儲過程。查看存儲過程。刪除存儲過程。9.創(chuàng)立觸發(fā)器。(1)在表student中建立刪除觸發(fā)器，實現表student和表sc中數據的級聯刪除。(2)在對象資源管理器中，向表course中插入一列status(char(1))，并且默認值為“0”。在表SC上建立一個Insert觸發(fā)器，當向表SC中插入一行時，檢查課程表中的課程是否正在準備中〔查看對應課程在Course中的狀態(tài)是否為1〕，如果是在準備中，那么不能進行選修。(3)運行以上創(chuàng)立觸發(fā)器的代碼，然后再向表course中插入以下記錄。(4)向SC表參加“9”號課程的選修記錄，查看執(zhí)行情況。(5)將“9”號課程的狀態(tài)必為“0”。向表SC參加“9”號課程的選修記錄，查看執(zhí)行情況。(6)表SC的cno列設置了外鍵約束，試用觸發(fā)器實現這一功能。(7)在上例中，只針對插入單行數據，不包括對多行的判斷，如果要利用觸發(fā)器對插入的數據進行逐行檢查，并將那些課程號在表Course中不存在的行刪掉，請修改以上觸發(fā)器。10.使觸發(fā)器無效或重新有效。(1)使表SC上的scinsert觸發(fā)器無效。(2)使表SC上的scinsert觸發(fā)器重新有效。11.刪除scinsert觸發(fā)器。〖問題解答〗1.創(chuàng)立存儲過程之前應考慮什么？創(chuàng)立存儲過程之前應考慮以下4點：?CREATEPROCEDURE語句不能與別的T-SQL語句位于同一個批中；?創(chuàng)立存儲過程的權限默認屬于數據庫擁有者，該權限可以授予他人；?存儲過程是數據庫對象，它的命名應符合標識符的命名標準；?只能在當前數據庫中創(chuàng)立存儲過程。2.什么是INSERTED表和DELETED表？SQLServer為每個觸發(fā)器都創(chuàng)立了兩個專用表：INSERTED表和DELETED表。這是兩個邏輯表，由系統(tǒng)來維護，用戶不能對它們進行修改。它們存放在內存而不是數據庫中。這兩個表的結構總是與被該觸發(fā)器作用的表的結構相同。觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關的這兩個表也被刪除。DELETED表存放由于執(zhí)行DELETE或UPDATE語句而要從表中刪除的所有行。在執(zhí)行DELETE或UPDATE操作時，被刪除的行從激活觸發(fā)器的表中被移動到DELETED表，這兩個表不會有共同的行。INSERTED表存放由于執(zhí)行INSERT或UPDATE語句而要向表中插入的所有行。在執(zhí)行INSERT或UPDATE操作時，新的行同時添加到激活觸發(fā)器的表和INSERTED表中，INSERTED表的內容是激活觸發(fā)器的表中新行的備份?！妓伎肌⒕毩曨}〗使用對象資源管理器創(chuàng)立存儲過程ssc_3。使用對象資源管理器查看存儲過程ssc_3的定義。3.使用對象資源管理器刪除存儲過程ssc_3。4.在創(chuàng)立觸發(fā)器之前要考慮哪些問題？?CREATETRIGGER必須是批處理中的第一條語句，并且只能應用到一個表中。?觸發(fā)器只能在當前的數據庫中創(chuàng)立，不過觸發(fā)器可以引用當前數據庫的外部對象。?如果指定觸發(fā)器所有者名稱以限定觸發(fā)器，請以相同的方式限定表名。?在同一條CREATETRIGGER語句中，可以為多種用戶操作〔如INSERT和UPDATE〕定義相同的觸發(fā)器操作。?如果一個表的外鍵在DELETE/UPDATE操作上定義了級聯，那么不能在該表上定義INSTEADOFDELETE/UPDATE觸發(fā)器。?在觸發(fā)器內可以指定任意的SET語句。所選擇的SET選項在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復到以前的設置。?與使用存儲過程一樣，當觸發(fā)器激發(fā)時，將向調用應用程序返回結果。假設要防止由于觸發(fā)器激發(fā)而向應用程序返回結果，請不要包含返回結果的SELECT語句，也不要包含在觸發(fā)器中進行變量賦值的語句。?包含向用戶返回結果的SELECT語句或進行變量賦值的語句的觸發(fā)器需要特殊處理；這些返回的結果必須寫入允許修改觸發(fā)器表的每個應用程序中。如果必須在觸發(fā)器中進行變量賦值，那么應該在觸發(fā)器的開頭?使用SETNOCOUNT語句以防止返回任何結果集。?DELETE觸發(fā)器不能捕獲TRUNCATETABLE語句。盡管TRUNCATETABLE語句實際上是沒有WHERE子句的DELETE〔它刪除所有行〕，但它是無日志記錄的，因而不能執(zhí)行觸發(fā)器。因為TRUNCATETABLE語句的權限默認授予表所有者且不可轉讓，所以只有表所有者才需要考慮無意中用TRUNCATETABLE語句躲避DELETE觸發(fā)器的問題。?無論有日志記錄還是無日志記錄，WRITETEXT語句都不激活觸發(fā)器。?觸發(fā)器中不允許以下Transact-SQL語句：ALTERDATABASECREATEDATABASEDISKINITDISKRESIZEDROPDATABAS