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

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

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

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

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

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

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

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

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

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

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

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

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

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

14、限制更為復雜的其他限制。(3) DML觸發(fā)器可以評估修改前后表的狀態(tài)，并根據該差異采取措施。(4) 一個表中的多個同類DML觸發(fā)器（INSERT、UPDATE 或 DELETE）允許采取多個不同的操作來響應同一個修改語句。在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ā)存儲過程以響應事件。與DML不同的是，它們不會響應針對表或視圖的UPDATE、INS

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

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

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

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