SQL課件存儲過程和觸發(fā)器

1、第10章 存儲過程和觸發(fā)器教學提示：開發(fā)中編寫的一些SQL語句會占用程序的很大篇幅，而且不便于在其他地方重用，且由于這些SQL語句通常還要跨越傳輸途徑從外部不僅會導致程序的運行效率低，還會產生安全隱患，而存儲過程則能克服以上的缺陷。 觸發(fā)器可以大大增強應用程序的健壯性、數據庫可恢復性和數據庫的可管理性。 存儲過程和觸發(fā)器都是SQL Server的數據庫對象。存儲過程的存在獨立于表，它存放在服務器上，供客戶端調用。1教學目標：本章主要包含以下內容。存儲過程概述設計存儲過程實現和管理存儲過程觸發(fā)器概述設計觸發(fā)器實現和管理觸發(fā)器 通過本章的學習，使讀者掌握存儲過程和觸發(fā)器的基礎知識，并基本學會編寫簡

2、單的存儲過程和觸發(fā)器，為以后在實際應用中不斷提高自己編寫存儲過程和觸發(fā)器的技能打下良好的基礎。2第10章 存儲過程和觸發(fā)器10.1 存儲過程概述10.2 設計存儲過程10.3 實現和管理存儲過程10.4 觸發(fā)器概述10.5 觸發(fā)器設計規(guī)則10.6 實現和管理觸發(fā)器310.1 存儲過程概述10.1.1 存儲過程的特點 能夠在單個存儲過程中執(zhí)行一系列的Transact-SQL語句，能夠以在一個存儲過程中調用其他的存儲過程。存儲過程是保存在服務器端的已經編譯的Transact-SQL語句。存儲過程可以使用控制流語句和變量，大大增強了SQL的功能。存儲過程在提交前會自動檢查語法。存儲過程是管理員放在服

3、務器端的Transact-SQL語句。410.1.2 存儲過程的分類1. 用戶存儲過程2. 系統(tǒng)存儲過程3. 擴展存儲過程510.2 設計存儲過程10.2.1 一些設計規(guī)則 用戶在設計數據庫(的存儲過程)時，應遵守以下規(guī)則： 在SQL Server 2005中，存儲過程可以使用Transact-SQL中的任何語句，但是表10.1中的語句除外。 略610.2.2 存儲過程的內部命名規(guī)范化 存儲過程中的Transact-SQL語句使用的對象名稱必須符合規(guī)范的模式，如果未指明對象所屬的模式，存儲過程將使用當前默認的模式。 每個存儲過程必須有惟一的名稱，存儲過程中使用的變量類型必須符合Transact

4、-SQL的要求，變量名必須以開頭，并且存儲過程的名稱和變量的命名要符合Transact-SQL標識符規(guī)則。710.2.3 存儲過程的加密定義 如果用戶想定義一個不讓其他用戶看到具體操作的存儲過程，可以使用WITH ENCRYPTION參數，那么存儲過程將會以不可讀的狀態(tài)存在，并且存儲過程擁有者、創(chuàng)建者和數據庫管理員都不可見。810.3 實現和管理存儲過程10.3.1 創(chuàng)建存儲過程 由于必須使用Transact-SQL語句創(chuàng)建存儲過程，所以用戶必須首先掌握創(chuàng)建存儲過程的Transact-SQL語法結構，然后再學習如何使用Management Studio創(chuàng)建存儲過程。910.3.2 執(zhí)行存儲過程

5、執(zhí)行存儲過程有多種方式，比較常用的有以下幾種。1. 通過Execute或Exec語句執(zhí)行2. 通過設置，使存儲過程自動執(zhí)行3. 作為批處理的第一行，直接輸入存儲過程名1010.3.3 修改存儲過程1. 修改存儲過程的語法注意：修改存儲過程的名稱會影響已關聯(lián)對象對此存儲過程的調用。2. 使用Management Studio修改存儲過程1110.3.4 重新編譯存儲過程 1.sp_recompile系統(tǒng)存儲過程可以強制指定的存儲過程在下次調用時重新編譯。其調用的語法結構為：sp_recompile objname = object，其中的object為存儲過程的名稱。 2.在創(chuàng)建存儲過程時使用W

6、ITH RECOMPILE參數項，SQL Server將不會把此存儲過程放在緩沖存儲器中，并且每次調用此存儲過程時都會重新編譯。 3.在調用存儲過程時，可以使用參數選項WITH RECOMPILE強制重新編譯。使用這個參數項要求存儲過程的參數必須是非典型的或者數據發(fā)生了很大的變化，否則一般不用它。1210.3.5 刪除存儲過程 如果一個存儲過程不再需要，用戶就可以刪除它，但是如果此存儲過程有關聯(lián)存儲過程調用，系統(tǒng)會返回錯誤信息。但是刪除存儲過程后，用戶重新定義的同名稱同參數的存儲過程可以被原來關聯(lián)的對象使用。刪除存儲過程的Transact-SQL語法結構為：DROP PROCEDURE sch

7、ema_name. procedure ,.n 1310.4 觸發(fā)器概述 觸發(fā)器是一種特殊類型的存儲過程，這種存儲過程能夠在某個規(guī)定的事件發(fā)生時觸發(fā)執(zhí)行。觸發(fā)器通常可以強制執(zhí)行一定的業(yè)務規(guī)則，以保持數據完整性、檢查數據有效性、實現數據庫管理任務和一些附加的功能。 觸發(fā)器是數據庫中比較高級的應用，靈活使用觸發(fā)器可以大大增強應用程序的健壯性、數據庫的可恢復性和數據庫的可管理性。同時可以使開發(fā)人員和數據庫管理員實現一些復雜的功能，簡化開發(fā)步驟，降低開發(fā)成本，增加開發(fā)效率，提高數據庫的可靠性。1410.4.1 觸發(fā)器的特點 觸發(fā)器是一種特殊的存儲過程，除了存儲過程的特點外，它還另外有以下特點：觸發(fā)器是

8、自動執(zhí)行的，可以在一定條件下觸發(fā)。觸發(fā)器可以同步數據庫的相關表，進行級聯(lián)更改。觸發(fā)器可以實現更復雜的安全檢查。它可以實現比CHECK更復雜的業(yè)務規(guī)則，還可以引用其他表中的列。觸發(fā)器可以實現數據庫的管理任務。如DDL觸發(fā)器，在DDL語句執(zhí)行后觸發(fā)，可以實現一些統(tǒng)一的數據庫管理策略。1510.4.2 觸發(fā)器的種類1. DML觸發(fā)器 DML觸發(fā)器是在執(zhí)行數據操作語言事件時被調用的觸發(fā)器，其中數據操作語言事件包括：INSERT、UPDATE和DELETE語句。觸發(fā)器中可以包含復雜的Transact-SQL語句，觸發(fā)器整體被看作一個事務，可以回滾。2. DDL觸發(fā)器 與DML觸發(fā)器類似，與DML不同的是

9、，它相應的觸發(fā)事件是由數據定義語言引起的事件，包括：CREATE、ALTER和DROP語句，DDL觸發(fā)器用于執(zhí)行數據庫管理任務，如調節(jié)和審計數據庫運轉。DDL觸發(fā)器只能在觸發(fā)事件發(fā)生后才會調用執(zhí)行，即它只能是AFTER類型的。1610.4.3 inserted表和deleted表 觸發(fā)器中兩個比較特殊的對象：inserted表和deleted表。在使用觸發(fā)器過程中，SQL Server使用到了兩張?zhí)厥獾呐R時表，分別是inserted表和deleted表。這兩張表都存在于高速緩存中(當然，如果內存不夠用，也可能存儲在硬盤上)，實際上是事務日志的視圖，它們與創(chuàng)建了觸發(fā)器的表有著相同的結構。1710

10、.5 觸發(fā)器設計規(guī)則10.5.1 確定觸發(fā)器的種類 首先用戶應根據相應的觸發(fā)事件確定到底是DML觸發(fā)器還是DDL觸發(fā)器，如果是DML觸發(fā)器，用戶在要根據數據操作類型確定是INSERT、UPDATE還是DELETE觸發(fā)器，然后再根據約束規(guī)則，是希望觸發(fā)事件發(fā)生后還是發(fā)生前來觸發(fā)執(zhí)行觸發(fā)器，從而確定是AFTER還是INSTEAD OF類型的觸發(fā)器。 對于DDL觸發(fā)器，它只有AFTER一種類型，只需根據觸發(fā)事件的類型確定觸發(fā)器，來做出相應的響應。1810.5.2 確定觸發(fā)器的響應范圍 對于DML觸發(fā)器，其響應范圍是在數據庫范圍中，我們無需討論。而對于DDL觸發(fā)器，就需要確定它的響應范圍是數據庫級的還

11、是服務器級的響應。在此只列出了SQL Server 2005中服務器級的DDL觸發(fā)事件，如表10.2所示，其他的DDL事件都是數據庫級的。1910.5.3 一些設計規(guī)則在設計觸發(fā)器時，用戶可以參考以下的設計規(guī)則：DML觸發(fā)器的實現者是表的默認擁有者，權限不能轉移給別的用戶。DML觸發(fā)器必須是在當前數據庫上創(chuàng)建，盡管它可以引用別的數據庫。不能對系統(tǒng)表和臨時表創(chuàng)建觸發(fā)器。每個表可以有多個不同名稱的AFTER觸發(fā)器，但每種觸發(fā)事件只能有一個INSTEAD OF類型觸發(fā)器。觸發(fā)器只能創(chuàng)建在表或者視圖的模式中。2010.6 實現和管理觸發(fā)器10.6.1 創(chuàng)建觸發(fā)器1. 創(chuàng)建DML觸發(fā)器的語法2. 創(chuàng)建D

12、DL觸發(fā)器的語法3. 創(chuàng)建DML觸發(fā)器的步驟4. 創(chuàng)建作用范圍為數據庫的DDL觸發(fā)器的步驟5. 創(chuàng)建作用范圍為服務器的DDL觸發(fā)器2110.6.2 更改觸發(fā)器 當一個觸發(fā)器必須修改時，用戶可以先刪除此觸發(fā)器然后重建新的觸發(fā)器，也可以使用Transact-SQL語句對它進行修改。修改DML和DDL觸發(fā)器的語法結構與創(chuàng)建它們的語法結構類似，除了使用的開始關鍵詞變?yōu)锳LTER和在修改DML觸發(fā)器時不能使用WITH APPEND參數選項外，其他語法結構都相同，在此我們不再給出詳細的語法列表。 在Microsoft SQL Server Management Studio中更改觸發(fā)器的具體步驟如下。略2210.6.3 刪除觸發(fā)器和停止觸發(fā)器 當一個觸發(fā)器不再使用時，用戶可以刪除它，也可以停止使用它，停止使用的觸發(fā)器沒有被刪除，仍然保存在數據庫中，當用戶想要使用的時候還可以激活它，新創(chuàng)建的觸發(fā)器默認為激活狀態(tài)。1. 使用Management Studio刪除觸發(fā)器2. 使用Transa