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

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

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

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

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

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

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

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

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

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

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

11、是服務(wù)器級(jí)的響應(yīng)。在此只列出了SQL Server 2005中服務(wù)器級(jí)的DDL觸發(fā)事件，如表10.2所示，其他的DDL事件都是數(shù)據(jù)庫級(jí)的。1910.5.3 一些設(shè)計(jì)規(guī)則在設(shè)計(jì)觸發(fā)器時(shí)，用戶可以參考以下的設(shè)計(jì)規(guī)則：DML觸發(fā)器的實(shí)現(xiàn)者是表的默認(rèn)擁有者，權(quán)限不能轉(zhuǎn)移給別的用戶。DML觸發(fā)器必須是在當(dāng)前數(shù)據(jù)庫上創(chuàng)建，盡管它可以引用別的數(shù)據(jù)庫。不能對(duì)系統(tǒng)表和臨時(shí)表創(chuàng)建觸發(fā)器。每個(gè)表可以有多個(gè)不同名稱的AFTER觸發(fā)器，但每種觸發(fā)事件只能有一個(gè)INSTEAD OF類型觸發(fā)器。觸發(fā)器只能創(chuàng)建在表或者視圖的模式中。2010.6 實(shí)現(xiàn)和管理觸發(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)建作用范圍為數(shù)據(jù)庫的DDL觸發(fā)器的步驟5. 創(chuàng)建作用范圍為服務(wù)器的DDL觸發(fā)器2110.6.2 更改觸發(fā)器 當(dāng)一個(gè)觸發(fā)器必須修改時(shí)，用戶可以先刪除此觸發(fā)器然后重建新的觸發(fā)器，也可以使用Transact-SQL語句對(duì)它進(jìn)行修改。修改DML和DDL觸發(fā)器的語法結(jié)構(gòu)與創(chuàng)建它們的語法結(jié)構(gòu)類似，除了使用的開始關(guān)鍵詞變?yōu)锳LTER和在修改DML觸發(fā)器時(shí)不能使用WITH APPEND參數(shù)選項(xiàng)外，其他語法結(jié)構(gòu)都相同，在此我們不再給出詳細(xì)的語法列表。 在Microsoft SQL Server Management Studio中更改觸發(fā)器的具體步驟如下。略2210.6.3 刪除觸發(fā)器和停止觸發(fā)器 當(dāng)一個(gè)觸發(fā)器不再使用時(shí)，用戶可以刪除它，也可以停止使用它，停止使用的觸發(fā)器沒有被刪除，仍然保存在數(shù)據(jù)庫中，當(dāng)用戶想要使用的時(shí)候還可以激活它，新創(chuàng)建的觸發(fā)器默認(rèn)為激活狀態(tài)。1. 使用Management Studio刪除觸發(fā)器2. 使用Transa