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

1、存儲過程和觸發(fā)器存儲過程和觸發(fā)器是SQL Server的數(shù)據(jù)庫對象。使用存儲過程可以提高應(yīng)用程序的效率。觸發(fā)器可以大大增強應(yīng)用程序的健壯性、數(shù)據(jù)庫的可恢復(fù)性和可管理性。一、 存儲過程存儲過程是一組T-SQL語句，它們只需編譯一次，以后即可多次執(zhí)行。存儲過程是在SQL Server中定義的子過程，是數(shù)據(jù)庫對象之一。存儲過程可以執(zhí)行范圍很寬的各種操作與業(yè)務(wù)功能。比如可以插入、更新或刪除表中的數(shù)據(jù)。通過傳遞參數(shù)值，存儲過程可以判斷是選擇數(shù)據(jù)還是執(zhí)行其他操作。由于存儲過程可以接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過程或批處理返回多個值；存儲過程是包含用于在數(shù)據(jù)庫中執(zhí)行操作（包括調(diào)用其他過程）的編程語句。

2、此外存儲過程可以向調(diào)用過程或批處理返回狀態(tài)值，以指明成功或失?。ㄒ约笆〉脑颍?。故SQL Server 中的存儲過程與其他語言中的過程（有時也稱：函數(shù)）類似?？梢允褂?T-SQL EXECUTE 語句來運行存儲過程。存儲過程作為 SQL Server 數(shù)據(jù)庫系統(tǒng)中很重要的概念之一，合理的使用存儲過程，可以有效地提高程序的性能；并且將商業(yè)邏輯封裝在數(shù)據(jù)庫系統(tǒng)中的存儲過程中，可以大大提高整個軟件系統(tǒng)的維護性。當商業(yè)邏輯發(fā)生了改變的時候，不再需要修改并編譯客戶端的應(yīng)用程序以及重新分發(fā)它們到為數(shù)眾多的用戶手中，只需要修改位于服務(wù)器端的實現(xiàn)相應(yīng)商業(yè)邏輯的存儲過程即可。使用 SQL Server 創(chuàng)建應(yīng)

3、用程序時，T-SQL編程語言是應(yīng)用程序和 SQL Server 數(shù)據(jù)庫之間的主要編程接口。使用T-SQL程序時，可用兩種方法存儲和執(zhí)行程序；一種是將程序存儲在本地，然后創(chuàng)建向SQL Server發(fā)送命令并處理結(jié)果的應(yīng)用程序；另一種是將程序作為存儲過程存儲在SQL Server中，然后創(chuàng)建執(zhí)行過程并處理結(jié)果的應(yīng)用程序。在SQL Server中使用存儲過程而不使用存儲在客戶端計算機本地的T-SQL程序，原因在于存儲過程具有以下的好處：ü 存儲過程已在服務(wù)器注冊。ü 存儲過程具有安全特性（例如權(quán)限）和所有權(quán)鏈接，以及可以附加到它們的證書。ü 存儲過程可以強制應(yīng)用程序的安

4、全性。ü 存儲過程允許模塊化程序設(shè)計。存儲過程被創(chuàng)建后可以多次調(diào)用。ü 存儲過程是命名代碼，允許延遲綁定。ü 存儲過程可以減少網(wǎng)絡(luò)流量。對于同一個針對數(shù)據(jù)庫對象的操作，如果這一操作所涉及到的T-SQL語句被組織成一個存儲過程，那么當在客戶間計算機上調(diào)用該存儲過程時，網(wǎng)絡(luò)中傳送的只是調(diào)用存儲過程的語句，從而減少網(wǎng)絡(luò)負擔。一） 存儲過程類型在SQL Server 2005中有多種可用的存儲過程。主要有以下3種：系統(tǒng)存儲過程、用戶定義的存儲過程和擴展存儲過程。1. 系統(tǒng)存儲過程SQL Server 2005中許多管理活動都是通過一種特殊的存儲過程執(zhí)行的，這種存儲過程被稱

5、為系統(tǒng)存儲過程。系統(tǒng)存儲過程存儲在源數(shù)據(jù)庫中，并且?guī)в衧p_前綴。從邏輯意義上講，系統(tǒng)存儲過程出現(xiàn)在每個系統(tǒng)定義數(shù)據(jù)庫和用戶定義數(shù)據(jù)庫的sys構(gòu)架中。在 SQL Server 2005 中，可將GRANT、DENY和REVOKE權(quán)限應(yīng)用于系統(tǒng)存儲過程。SQL Server支持在SQL Server和外部程序之間提供一個接口以實現(xiàn)各種維護活動的系統(tǒng)存儲過程。這些擴展存儲程序使用xp_前綴。在“Microsoft SQL Server Management Studio”中可以查看系統(tǒng)存儲過程。查看步驟為：打開“Microsoft SQL Server Management Studio”后，在“

6、對象資源管理器”展開“數(shù)據(jù)庫”選項，打開master系統(tǒng)數(shù)據(jù)庫，打開“可編程的”目錄下的“存儲過程”目錄，就可以看到“系統(tǒng)存儲過程目錄”，該目錄有所有系統(tǒng)存儲過程的列表。如下圖所示：2. 用戶定義的存儲過程用戶定義的存儲過程是指由用戶創(chuàng)建并能完成某一特定功能的存儲過程，是主要的存儲過程類型，用戶自定義的存儲過程封裝了可重用代碼的模塊或例程。存儲過程可以接受輸入?yún)?shù)、向客戶端返回表格或標量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言（DDL）和數(shù)據(jù)操作語言（DML）語句，然后返回輸出參數(shù)。在SQL Server 2005中，存儲過程有兩種類型：T-SQL和CLR。其中T-SQL存儲過程是指保存著T-SQL語句的

7、集合，可以接受和返回用戶提供的參數(shù)。CLR存儲過程是指.NET Framework公共語言運行時（CLR）方法的引用。3. 擴展存儲過程擴展存儲過程允許使用編程語言（例如C語言）創(chuàng)建自己的外部例程。 擴展存儲過程是指SQL Server的實例可以動態(tài)加載和運行的DLL。擴展存儲過程直接在SQL Server的實例的地址空間中運行，可以使用SQL Server擴展存儲過程API完成編程。二） 存儲過程的設(shè)計1.存儲過程的設(shè)計規(guī)則2. 限定存儲過程內(nèi)的名稱3. 存儲過程的內(nèi)部命名規(guī)范化4. 加密過程定義三） 存儲過程的實現(xiàn)1.創(chuàng)建存儲過程的前提2.創(chuàng)建存儲過程1） 使用SQL Server Man

8、agement Studio創(chuàng)建存儲過程2） 用T-SQL語句創(chuàng)建存儲過程【例1】創(chuàng)建一個名為“產(chǎn)品情況”的存儲過程USE 商品信息GOCREATE PROCEDURE 產(chǎn)品情況ASSELECT 產(chǎn)品名稱, 價格FROM 產(chǎn)品表GO3. 執(zhí)行存儲過程Execute 產(chǎn)品情況【例2】在“商品信息”數(shù)據(jù)庫下創(chuàng)建一個名為“通過編號查詢產(chǎn)品情況”的存儲過程。USE 商品信息GOCREATE PROCEDURE dbo.通過產(chǎn)品編號查詢產(chǎn)品情況Sno VARCHAR(6)ASBEGINSELECT 產(chǎn)品名稱, 價格FROM 產(chǎn)品表WHERE 產(chǎn)品編號=SnoENDGO該存儲過程的執(zhí)行方式為：Execut

9、e 通過產(chǎn)品編號查詢產(chǎn)品情況 '100010'或Execute 通過產(chǎn)品編號查詢產(chǎn)品情況 Sno='100010'4. 存儲過程的修改與刪除【例3】修改在“商品信息”數(shù)據(jù)庫下的名為“通過編號查詢產(chǎn)品情況”的存儲過程，使得能夠查找到所有產(chǎn)品編號比給定編號大的所有產(chǎn)品信息。ALTER PROCEDURE dbo.通過產(chǎn)品編號查詢產(chǎn)品情況Pno VARCHAR(6)ASBEGINSELECT 產(chǎn)品名稱, 價格FROM 產(chǎn)品表WHERE 產(chǎn)品編號>PnoENDGO執(zhí)行語句為：Execute 通過產(chǎn)品編號查詢產(chǎn)品情況Sno='100004'【例4】使

10、用DROP語句刪除“通過編號查詢產(chǎn)品情況”存儲過程DROP PROCEDURE 產(chǎn)品情況DROP PROCEDURE 通過產(chǎn)品編號查詢產(chǎn)品情況二、觸發(fā)器觸發(fā)器是一種特殊的存儲過程，它在執(zhí)行語言事件時自動生效。觸發(fā)器通過強制執(zhí)行一定的業(yè)務(wù)規(guī)則，保持數(shù)據(jù)完整性、檢查數(shù)據(jù)有效性、實現(xiàn)數(shù)據(jù)庫管理任務(wù)和一些附加的功能。約束和觸發(fā)器是SQL Server 2005提供的來強制執(zhí)行業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性的兩種主要機制。觸發(fā)器的主要作用是實現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)一致性。觸發(fā)器是數(shù)據(jù)庫中比較高級的應(yīng)用，靈活使用觸發(fā)器可以大大增強應(yīng)用程序的健壯性、數(shù)據(jù)庫的可恢復(fù)性和數(shù)據(jù)庫的可管理性。同時可

11、以使開發(fā)人員和數(shù)據(jù)庫管理員實現(xiàn)一些復(fù)雜的功能，簡化開發(fā)步驟，降低開發(fā)成本。增加開發(fā)效率，提高數(shù)據(jù)庫的可靠性。觸發(fā)器是一種特殊的存儲過程，除了具有存儲過程的特點外，它還另外含有以下特點：1） 自動執(zhí)行觸發(fā)器是自動執(zhí)行的，可以在一定條件下觸發(fā)。2） 對相關(guān)表實現(xiàn)級聯(lián)更改觸發(fā)器可以同步數(shù)據(jù)庫的相關(guān)表，進行級聯(lián)更改。3） 觸發(fā)器可以實現(xiàn)更復(fù)雜的安全檢查觸發(fā)器可以實現(xiàn)比CHECK更復(fù)雜的業(yè)務(wù)規(guī)則，還可以引用其他表中的列。4） 觸發(fā)器可以實現(xiàn)數(shù)據(jù)庫的管理任務(wù)如DDL觸發(fā)器，在DDL語句執(zhí)行后觸發(fā)，可以實現(xiàn)一些統(tǒng)一的數(shù)據(jù)庫管理策略。一） 觸發(fā)器類型SQL Server 2005 包括兩大類觸發(fā)器：DML觸發(fā)

12、器和DDL觸發(fā)器。1. DML觸發(fā)器DML觸發(fā)器是在執(zhí)行數(shù)據(jù)操作語言事件時被調(diào)用的觸發(fā)器，其中數(shù)據(jù)操作語言事件包括：INSERT、UPDATE 和 DELETE 語句。DML觸發(fā)器用于在數(shù)據(jù)被修改時強制執(zhí)行業(yè)務(wù)規(guī)則，以及擴展Microsoft SQL Server 2005約束、默認值和規(guī)則的完整性檢查邏輯。觸發(fā)器中可以包含復(fù)雜的Transact-SQL語句觸發(fā)器整體被看作一個事物，可以回滾。約束和DML觸發(fā)器在特殊情況下各有優(yōu)點。DML觸發(fā)器的主要優(yōu)點在于它們可以包含使用T-SQL代碼的復(fù)雜處理邏輯。因此，DML支持約束的所有功能；但DML觸發(fā)器對于給定的并不一定是最好的方法。實體完整性必須

13、在最低級別上通過索引進行強制，這些索引是PRIMARY KEY 和 UNIQUE 約束的一部分，或者是獨立于約束而創(chuàng)建的。域完整性應(yīng)通過CHECK約束進行強制，而引用完整性（RI）則通過FOREIGN KEY約束進行強制，假設(shè)這些約束的功能滿足應(yīng)用程序的功能要求。當約束支持的功能無法滿足應(yīng)用程序的功能要求時，DML觸發(fā)器將變得非常有用。DML觸發(fā)器在以下方面非常有用：(1) DML觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改。不過，通過級聯(lián)引用完整性約束可以更有效地進行這些更改。(2) DML觸發(fā)器可以防止惡意或錯誤的INSERT、UPDATE以及DELETE操作，并強制執(zhí)行比CHECK約束定義的

14、限制更為復(fù)雜的其他限制。(3) DML觸發(fā)器可以評估修改前后表的狀態(tài)，并根據(jù)該差異采取措施。(4) 一個表中的多個同類DML觸發(fā)器（INSERT、UPDATE 或 DELETE）允許采取多個不同的操作來響應(yīng)同一個修改語句。在SQL Server 2005 中，可以創(chuàng)建AFTER觸發(fā)器、INSTEAD OF觸發(fā)器和CLR觸發(fā)器。(1) AFTER觸發(fā)器。在執(zhí)行INSERT、UPDATE 或 DELETE語句操作之后執(zhí)行AFTER觸發(fā)器。指定AFTER與指定FOR相同，它是SQL Server 早期版本中唯一可用的選項。AFTER觸發(fā)器只能在表上指定。(2) INSTEAD OF 觸發(fā)器。執(zhí)行 I

15、NSTEAD OF 觸發(fā)器代替通常的觸發(fā)動作。還可為一個或多個基表的視圖定義INSTEAD OF 觸發(fā)器，而這些觸發(fā)器能夠擴展視圖可支持的更新類型。(3) CLR觸發(fā)器。CLR 觸發(fā)器可以是 AFTER 觸發(fā)器還可以是DDL 觸發(fā)器。CLR 觸發(fā)器將執(zhí)行在托管代碼（在.NET Framework 中創(chuàng)建并在 SQL Server 中上載的程序集的成員）中編寫的方法，而不用執(zhí)行T-SQL存儲過程。2. DDL 觸發(fā)器DDL 觸發(fā)器是SQL Server 2005 的新增功能，與DML觸發(fā)器類似，DDL觸發(fā)器將激發(fā)存儲過程以響應(yīng)事件。與DML不同的是，它們不會響應(yīng)針對表或視圖的UPDATE、INS

16、ERT 或 DELETE 語句而激發(fā)。它響應(yīng)的觸發(fā)事件是由數(shù)據(jù)定義語言引起的事件，包括：CREATE、ALTER 和 DROP 語句，DDL 觸發(fā)器用于執(zhí)行數(shù)據(jù)庫管理任務(wù)，如調(diào)解和審計數(shù)據(jù)庫運行。DDL觸發(fā)器只能在觸發(fā)事件發(fā)生后才能調(diào)用執(zhí)行，即它只能是AFTER類型的。如果要執(zhí)行以下操作，可使用DDL觸發(fā)器：(1) 要防止對數(shù)據(jù)庫進行某些更改(2) 希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改(3) 要記錄數(shù)據(jù)庫架構(gòu)中的更改或事件僅在運行觸發(fā)DDL觸發(fā)器的DDL語句后，DDL觸發(fā)器才會激發(fā)。DDL觸發(fā)器無法作為INSTEAD OF觸發(fā)器使用。二） 觸發(fā)器的設(shè)計與實現(xiàn)(1)創(chuàng)建觸發(fā)器【例1】

17、 創(chuàng)建一個觸發(fā)器，使得對于“商品信息”數(shù)據(jù)庫，如果在“產(chǎn)品表”中添加數(shù)據(jù)，則將向客戶端顯示一條“數(shù)據(jù)插入成功”的信息。USE 商品信息GOCREATE TRIGGER 產(chǎn)品表_trigger1ON 產(chǎn)品表FOR INSERTASPRINT '數(shù)據(jù)插入成功'GOUSE 商品信息GOINSERT INTO 產(chǎn)品表VALUES('100015','IPHONE6',5600,20)GO【例2】 創(chuàng)建一個觸發(fā)器，使得對于“商品信息”數(shù)據(jù)庫，如果在“產(chǎn)品表”中刪除數(shù)據(jù)，則將向客戶端顯示一條“數(shù)據(jù)刪除成功”的信息。USE 商品信息GOCREATE TRIGG

18、ER 產(chǎn)品表_trigger2ON 產(chǎn)品表FOR DELETEASPRINT '數(shù)據(jù)刪除成功'GOUSE 商品信息GODELETE FROM 產(chǎn)品表WHERE 產(chǎn)品編號='100013'(2)刪除觸發(fā)器【例3】 刪除“產(chǎn)品表_triger2”觸發(fā)器USE 商品信息GODROP TRIGGER 產(chǎn)品表_trigger2(3)修改觸發(fā)器【例4】 修改“產(chǎn)品表_triger1”觸發(fā)器，使得對于商品信息數(shù)據(jù)庫，如果在產(chǎn)品表中添加或者更新數(shù)據(jù)，則將向客戶端顯示一條信息“操作成功”的信息。USE 商品信息GOALTER TRIGGER 產(chǎn)品表_trigger1ON 產(chǎn)品表F

19、OR INSERT, UPDATEASPRINT '操作成功'GOUSE 商品信息GOINSERT INTO 產(chǎn)品表 VALUES('100013','MP4',650,50)USE 商品信息GOUPDATE 產(chǎn)品表SET 價格=600WHERE 產(chǎn)品編號='100013'三、 事務(wù)由于觸發(fā)器是對其中的某張表進行添加、刪除、修改等操作可能引起另一張或多張表中數(shù)據(jù)的數(shù)據(jù)的變化，為了保持數(shù)據(jù)完整性和一致性，觸發(fā)器和事務(wù)是一個強有力的結(jié)合。1. 什么是事務(wù)事務(wù)是一個不可分隔的邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時事務(wù)是最小的控制單元來使用。它包含的所有數(shù)據(jù)庫操作命令作為一個整體一起提交或撤銷，即這一組數(shù)據(jù)庫操作命令要要么都執(zhí)行，要么都不執(zhí)行。2. 事務(wù)的4個屬性(1) 原子性(Atomicity)。事務(wù)中的所有元素作為一個整體提交或回滾，事務(wù)的各元素是不可分的，事務(wù)是一個完整的操作。(2) 一致性(Consistency)。事務(wù)完成時，數(shù)據(jù)必須是一致的，也就是說，在事務(wù)開始之前，數(shù)據(jù)存儲中的數(shù)據(jù)處于一致狀態(tài)，保證數(shù)據(jù)的無損。(3) 隔離性(Isolati