第10章-存儲(chǔ)過(guò)程和觸發(fā)器

第10章存儲(chǔ)過(guò)程、觸發(fā)器第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)是SQLServer數(shù)據(jù)庫(kù)的三個(gè)重要組成部分。SQLServer2008使用它們從不同方面提高數(shù)據(jù)處理能力。在SQLServer2008中，可以像其他程序設(shè)計(jì)語(yǔ)言一樣定義子程序，稱(chēng)為存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是SQLServer2008提供的最強(qiáng)大的工具之一。理解并運(yùn)用它，可以創(chuàng)建健壯、安全且具有良好性能的數(shù)據(jù)庫(kù)，可以為用戶實(shí)現(xiàn)最復(fù)雜的商業(yè)事務(wù)。觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程：它通過(guò)事件觸發(fā)而被自動(dòng)執(zhí)行。自動(dòng)執(zhí)行意味著更少的手工操作以及更小的出錯(cuò)機(jī)率。觸發(fā)器用于強(qiáng)制復(fù)雜的完整性檢查，審核更改，維護(hù)不規(guī)范的數(shù)據(jù)等等。SQLServer2008允許DML語(yǔ)句和DDL語(yǔ)句創(chuàng)建觸發(fā)器，可以引發(fā)AFTER或者INSTEADOF觸發(fā)事件。游標(biāo)主要用于實(shí)現(xiàn)一些不能使用面向集合的語(yǔ)句實(shí)現(xiàn)的操作。通過(guò)游標(biāo)，SQLServer提供了一個(gè)對(duì)結(jié)果集進(jìn)行逐行處理的能力?？梢园延螛?biāo)看為一種特殊的指針，它可以指向結(jié)果集中的任意位置，在查詢數(shù)據(jù)的同時(shí)對(duì)數(shù)據(jù)進(jìn)行處理。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）本章學(xué)習(xí)目標(biāo)：

了解存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)的基本概念與特點(diǎn)掌握存儲(chǔ)過(guò)程的基本類(lèi)型和相關(guān)操作掌握觸發(fā)器的類(lèi)型與相關(guān)操作第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.1存儲(chǔ)過(guò)程通過(guò)前面的學(xué)習(xí)，我們能夠編寫(xiě)并運(yùn)行T-SQL程序以完成各種不同的應(yīng)用。保存T-SQL程序的方法有兩種：一種是在本地保存程序的源文件，運(yùn)行時(shí)先打開(kāi)源文件再執(zhí)行程序；另一種方法即將程序存儲(chǔ)為存儲(chǔ)過(guò)程，運(yùn)行時(shí)調(diào)用存儲(chǔ)過(guò)程執(zhí)行。因?yàn)榇鎯?chǔ)過(guò)程是由一組T-SQL語(yǔ)句構(gòu)成的，要使用存儲(chǔ)過(guò)程，我們必需熟悉前面幾章所討論的基本的T-SQL語(yǔ)句，并且需要了解掌握一些關(guān)于函數(shù)、過(guò)程的概念。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.1.1存儲(chǔ)過(guò)程的基本概念存儲(chǔ)過(guò)程是事先編好的、存儲(chǔ)在數(shù)據(jù)庫(kù)中一組被編譯了的T-SQL命令集合，這些命令用來(lái)完成對(duì)數(shù)據(jù)庫(kù)的指定操作：存儲(chǔ)過(guò)程可以接收用戶的輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語(yǔ)言(DDL)和數(shù)據(jù)操作語(yǔ)言(DML)語(yǔ)句，然后返回輸出參數(shù)。通過(guò)定義可以看到，存儲(chǔ)過(guò)程起到了我們?cè)谄渌Z(yǔ)言中所說(shuō)的子程序的作用，我們可以將經(jīng)常執(zhí)行的管理任務(wù)或者復(fù)雜的業(yè)務(wù)規(guī)則，預(yù)先用T-SQL語(yǔ)句寫(xiě)好并保存為存儲(chǔ)過(guò)程,當(dāng)需要數(shù)據(jù)庫(kù)提供與該存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí)，只需要使用EXECUTE命令，即可調(diào)用存儲(chǔ)過(guò)程完成命令。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）儲(chǔ)過(guò)程的優(yōu)點(diǎn)：1.減少網(wǎng)絡(luò)流量：存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)服務(wù)器端執(zhí)行，只向客戶端返回執(zhí)行結(jié)果。因此可以將在網(wǎng)絡(luò)中要發(fā)送的數(shù)百行代碼，編寫(xiě)為一條存儲(chǔ)過(guò)程，這樣客戶端只需要提交存儲(chǔ)過(guò)程的名稱(chēng)和參數(shù)，即可實(shí)現(xiàn)相應(yīng)功能，節(jié)省了網(wǎng)絡(luò)流量，提高了執(zhí)行的效率。此外，由于所有的操作都在服務(wù)器端完成，避免了在客戶端和服務(wù)器端之間的多次往返。存儲(chǔ)過(guò)程只需要將最終結(jié)果通過(guò)網(wǎng)絡(luò)傳輸?shù)娇蛻舳恕?.提高系統(tǒng)性能：一般T-SQL語(yǔ)句每執(zhí)行一次就需要編譯一次，而存儲(chǔ)過(guò)程只在創(chuàng)建時(shí)進(jìn)行編譯，被編譯后存放在數(shù)據(jù)庫(kù)服務(wù)器的過(guò)程高速緩存中，當(dāng)使用時(shí)，服務(wù)器不必再重新分析和編譯它們。因此，當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)（如對(duì)多個(gè)表進(jìn)行UPDATE、INSERT或DELETE操作時(shí)），可將這些復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用，節(jié)省了分析、解析和優(yōu)化代碼所需的CPU資源和時(shí)間。3.安全性高：使用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并且可授予沒(méi)有直接執(zhí)行存儲(chǔ)過(guò)程中語(yǔ)句的權(quán)限的用戶，也可執(zhí)行該存儲(chǔ)過(guò)程的權(quán)限。另外可以防止用戶直接訪問(wèn)表，強(qiáng)制用戶使用存儲(chǔ)過(guò)程執(zhí)行特定的任務(wù)。4.可重用性：存儲(chǔ)過(guò)程只需創(chuàng)建并存儲(chǔ)在數(shù)據(jù)庫(kù)中，以后即可任意在程序中調(diào)用該過(guò)程。存儲(chǔ)過(guò)程可獨(dú)立于程序源代碼而單獨(dú)修改，減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。5.可自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)：存儲(chǔ)過(guò)程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行，完成一些需要預(yù)先執(zhí)行的任務(wù)，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行人工操作。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.1.2存儲(chǔ)過(guò)程的類(lèi)型1.系統(tǒng)存儲(chǔ)過(guò)程2.擴(kuò)展存儲(chǔ)過(guò)程3.用戶存儲(chǔ)過(guò)程用戶存儲(chǔ)過(guò)程在用戶數(shù)據(jù)庫(kù)中創(chuàng)建，通常與數(shù)據(jù)庫(kù)對(duì)象進(jìn)行交互，用于完成特定數(shù)據(jù)庫(kù)操作任務(wù)，可以接受和返回用戶提供的參數(shù)，名稱(chēng)不能以sp_為前綴。在SQLServer2008中，用戶存儲(chǔ)過(guò)程有兩種類(lèi)型：Transact-SQL存儲(chǔ)過(guò)程和存儲(chǔ)過(guò)程。Transact-SQL存儲(chǔ)過(guò)程保存T-SQL語(yǔ)句的集合，可以接受和返回用戶提供的參數(shù)，也可以從數(shù)據(jù)庫(kù)向客戶端應(yīng)用程序返回?cái)?shù)據(jù)；CLR存儲(chǔ)過(guò)程是指對(duì)Microsoft.NETFramework公共語(yǔ)言運(yùn)行時(shí)方法的引用，可以接受和返回用戶提供的參數(shù)。它們?cè)?NETFramework程序集中是作為類(lèi)的公共靜態(tài)方法實(shí)現(xiàn)的第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.1.3用戶存儲(chǔ)過(guò)程的創(chuàng)建與執(zhí)行1.創(chuàng)建和執(zhí)行用戶存儲(chǔ)過(guò)程實(shí)例創(chuàng)建用戶存儲(chǔ)過(guò)程是通過(guò)編輯代碼實(shí)現(xiàn)的。下面通過(guò)一個(gè)實(shí)例介紹創(chuàng)建用戶存儲(chǔ)過(guò)程的一般步驟?！纠?0-1】創(chuàng)建名為snoquery的存儲(chǔ)過(guò)程：通過(guò)用戶輸入學(xué)生學(xué)號(hào)來(lái)查詢學(xué)生的姓名、年齡、性別和所屬院系。（1）啟動(dòng)SSMS，展開(kāi)服務(wù)器。（2）展開(kāi)所需的“數(shù)據(jù)庫(kù)”文件夾，展開(kāi)要在其中創(chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)。本例中，我們展開(kāi)stuinfo數(shù)據(jù)庫(kù)。（3）展開(kāi)“可編程性”文件夾，在“存儲(chǔ)過(guò)程”文件夾上右擊鼠標(biāo)，在彈出的快捷菜單中選擇“新建存儲(chǔ)過(guò)程”項(xiàng)。（4）系統(tǒng)彈出T-SQL語(yǔ)句編寫(xiě)窗口，其中的代碼是創(chuàng)建存儲(chǔ)過(guò)程的格式說(shuō)明。我們輸入以下T-SQL代碼：CREATEPROCEDUREsnoquery @xuehaochar(10)AS SELECTsno學(xué)號(hào),sname學(xué)生姓名,sage年齡,ssex性別,sdept所屬院系 FROMstudent WHEREsno=@xuehao （5）代碼輸入結(jié)束后，只要將以上代碼在“查詢分析器”里執(zhí)行一次，系統(tǒng)就會(huì)在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為snoquery的存儲(chǔ)過(guò)程。點(diǎn)擊刷新按鈕，選擇stuinfo數(shù)據(jù)庫(kù)，在左邊的樹(shù)型列表中選擇“存儲(chǔ)過(guò)程”，就可以看到屬于dbo（databaseowner）的存儲(chǔ)過(guò)程dbo.snoquery。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）【例10-2】使用存儲(chǔ)過(guò)程snoquery查詢學(xué)號(hào)為“20070102”學(xué)生的信息。T-SQL語(yǔ)句為：EXECUTEsnoquery'20070102‘2.創(chuàng)建存儲(chǔ)過(guò)程的T-SQL語(yǔ)句基本語(yǔ)法格式為：CREATEPROC[EDURE]<存儲(chǔ)過(guò)程名稱(chēng)> --定義存儲(chǔ)過(guò)程名稱(chēng) [@參數(shù)名稱(chēng)數(shù)據(jù)類(lèi)型] --定義參數(shù)及其數(shù)據(jù)類(lèi)型 [=default][OUTPUT][,…n1] --定義參數(shù)的屬性 AS SQL語(yǔ)句 [,…n2] --執(zhí)行的操作第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）3.運(yùn)行存儲(chǔ)過(guò)程的T-SQL語(yǔ)句存儲(chǔ)過(guò)程創(chuàng)建完成后，可以使用EXECUTE語(yǔ)句調(diào)用它?；菊Z(yǔ)法格式如下：EXEC[UTE] {存儲(chǔ)過(guò)程名稱(chēng)} [[@參數(shù)名稱(chēng)=]value|@variable[OUTPUT]|[DEFAULT]] [,…n1]}第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.1.4存儲(chǔ)過(guò)程的查看、修改和刪除展開(kāi)所選數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)→“可編程性”文件夾→“存儲(chǔ)過(guò)程”文件夾，即可以看到數(shù)據(jù)庫(kù)的系統(tǒng)存儲(chǔ)過(guò)程和用戶存儲(chǔ)過(guò)程；系統(tǒng)存儲(chǔ)過(guò)程方便用戶管理數(shù)據(jù)庫(kù)的有關(guān)對(duì)象。sp_help：用于查看有關(guān)存儲(chǔ)過(guò)程的名稱(chēng)列表。向用戶報(bào)告有關(guān)數(shù)據(jù)庫(kù)對(duì)象、用戶定義數(shù)據(jù)類(lèi)型或所提供的數(shù)據(jù)類(lèi)型的摘要信息；sp_helptext：用于顯示規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義函數(shù)、觸發(fā)器或視圖的過(guò)程定義代碼。我們可以利用下面的語(yǔ)句查看存儲(chǔ)過(guò)程的信息：

EXECUTEsp_help存儲(chǔ)過(guò)程名稱(chēng) —用于查看存儲(chǔ)過(guò)程的對(duì)象信息 EXECUTEsp_helptext存儲(chǔ)過(guò)程名稱(chēng) —用于查看存儲(chǔ)過(guò)程的代碼文本信息第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）【例10-3】查看存儲(chǔ)過(guò)程snoquery的對(duì)象信息和T-SQL代碼。查看對(duì)象信息的T-SQL語(yǔ)句為：USEstuinfoEXECUTEsp_helpsnoquery待查看的存儲(chǔ)過(guò)程必須在當(dāng)前數(shù)據(jù)庫(kù)中，因此，要使用USEstuinfo語(yǔ)句打開(kāi)數(shù)據(jù)庫(kù)?？梢钥吹酱鎯?chǔ)過(guò)程的相關(guān)信息及其中的參數(shù)信息。查看代碼信息的T-SQL語(yǔ)句為：USEstuinfoEXECUTEsp_helptextsnoquery可以看到存儲(chǔ)過(guò)程snoquery的詳細(xì)T-SQL代碼。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）2.修改存儲(chǔ)過(guò)程基本語(yǔ)法格式：ALTERPROC[EDURE]<存儲(chǔ)過(guò)程名稱(chēng)> [@參數(shù)名稱(chēng)數(shù)據(jù)類(lèi)型] [=default][OUTPUT][,…n1] ASSQL語(yǔ)句 [,…n2] 各參數(shù)的操作與創(chuàng)建存儲(chǔ)過(guò)程相同。

第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）【例10-4】修改存儲(chǔ)過(guò)程snoquery：通過(guò)用戶輸入學(xué)生姓名來(lái)查詢學(xué)生的姓名、年齡、性別和所屬院系。修改完成后查詢學(xué)生王小華的信息。T-SQL語(yǔ)句為：ALTERPROCEDUREsnoquery @namenchar(10)AS SELECTsno學(xué)號(hào),sname學(xué)生姓名,sage年齡,ssex性別,sdept所屬院系 FROMstudent WHEREsname=@nameGOEXECUTEsnoqueryN'王小華'第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）3.刪除存儲(chǔ)過(guò)程當(dāng)不再使用存儲(chǔ)過(guò)程時(shí)，可以在SSMS中選擇對(duì)應(yīng)的數(shù)據(jù)庫(kù)和存儲(chǔ)過(guò)程，單擊“刪除”按鈕，也可以使用DROPPROCEDURE語(yǔ)句可以將其永久從數(shù)據(jù)庫(kù)中刪除。在刪除之前，需要確認(rèn)該存儲(chǔ)過(guò)程沒(méi)有任何函數(shù)依賴關(guān)系。語(yǔ)法格式為：DROPPROCEDURE<存儲(chǔ)過(guò)程名稱(chēng)>[,…n] 【例10-5】刪除存儲(chǔ)過(guò)程snoqueryT-SQL語(yǔ)句為：USEstuinfoDROPPROCEDUREsnoquery第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.2觸發(fā)器SQLServer2008提供兩種主要機(jī)制來(lái)強(qiáng)制使用業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。我們使用ALTERTABLE和CREATETABLE語(yǔ)句聲明字段的域完整性，使用PRIMARYKEY和FOREIGNKEY約束實(shí)現(xiàn)表之間的參照完整性。對(duì)于數(shù)據(jù)庫(kù)中約束所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性我們使用觸發(fā)器來(lái)實(shí)現(xiàn)。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.2.1觸發(fā)器概述1.觸發(fā)器的功能在SQLServer內(nèi)部，觸發(fā)器被看作是存儲(chǔ)過(guò)程，它與存儲(chǔ)過(guò)程所經(jīng)歷的處理過(guò)程類(lèi)似。但是觸發(fā)器沒(méi)有輸入?yún)?shù)和輸出參數(shù)，因而不能被顯示調(diào)用。它作為語(yǔ)句的執(zhí)行結(jié)果自動(dòng)引發(fā)，而存儲(chǔ)過(guò)程則是通過(guò)存儲(chǔ)過(guò)程名稱(chēng)而被直接調(diào)用。觸發(fā)器與表格緊密相連，當(dāng)用戶對(duì)表進(jìn)行諸如UPDATE、INSERT和DELETE這些操作時(shí)，系統(tǒng)會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理符合由這些SQL語(yǔ)句所定義的規(guī)則。除此之外，觸發(fā)器還有其它許多不同的功能：強(qiáng)化約束：觸發(fā)器能夠?qū)崿F(xiàn)比CHECK語(yǔ)句更為復(fù)雜的約束：觸發(fā)器可以很方便地引用其他表的列，去進(jìn)行邏輯上的檢查；觸發(fā)器是在CHECK之后執(zhí)行的；觸發(fā)器可以插入，刪除，更新多行。跟蹤變化：觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作從而禁止數(shù)據(jù)庫(kù)中未經(jīng)許可的更新和變化，確保輸入表中的數(shù)據(jù)的有效性。例如在庫(kù)存系統(tǒng)中，觸發(fā)器可以檢測(cè)到當(dāng)實(shí)際庫(kù)存下降到了需要再進(jìn)貨的臨界量，就給出管理員相應(yīng)提示信息或自動(dòng)生成給供應(yīng)商的訂單；級(jí)聯(lián)運(yùn)行：觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作，并自動(dòng)地級(jí)聯(lián)影響整個(gè)數(shù)據(jù)庫(kù)的不同表中的各項(xiàng)內(nèi)容。如：設(shè)置一個(gè)觸發(fā)器，當(dāng)student表中刪除一個(gè)學(xué)號(hào)信息時(shí)，對(duì)應(yīng)的sc表中相應(yīng)的學(xué)號(hào)信息也被改寫(xiě)為NULL或刪除相關(guān)學(xué)生記錄；調(diào)用存儲(chǔ)過(guò)程：為了響應(yīng)數(shù)據(jù)庫(kù)更新，觸發(fā)器可以調(diào)用一個(gè)或多個(gè)存儲(chǔ)過(guò)程。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）2.觸發(fā)器的種類(lèi)SQLServer2008支持兩種類(lèi)型的觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。DML觸發(fā)器：如果用戶要通過(guò)數(shù)據(jù)操作語(yǔ)言(DML)編輯數(shù)據(jù)，則執(zhí)行DML觸發(fā)器。DML事件是針對(duì)表或視圖的INSERT、UPDATE和DELETE語(yǔ)句，即DML觸發(fā)器在數(shù)據(jù)修改時(shí)被執(zhí)行。系統(tǒng)將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤（例如，磁盤(pán)空間不足），則整個(gè)事務(wù)自動(dòng)回滾；DDL觸發(fā)器為了響應(yīng)各種數(shù)據(jù)定義語(yǔ)言(DDL)事件而激發(fā)。DDL事件主要與以關(guān)鍵字CREATE、ALTER和DROP開(kāi)頭的T-SQL語(yǔ)句對(duì)應(yīng)。它們可以用于在數(shù)據(jù)庫(kù)中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫(kù)操作第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.2.2DML觸發(fā)器的創(chuàng)建和應(yīng)用1.DML觸發(fā)器的分類(lèi)觸發(fā)器有很多用途，對(duì)于DML觸發(fā)器來(lái)說(shuō)，最常見(jiàn)的用途就是強(qiáng)制業(yè)務(wù)規(guī)則。例如，當(dāng)客戶下訂單時(shí)，DML觸發(fā)器可用于檢查是否有充足的資金。如果檢查完成，就可以完成進(jìn)一步的操作，或者返回錯(cuò)誤信息，對(duì)更新進(jìn)行回滾。在實(shí)際應(yīng)用中，DML觸發(fā)器分為兩類(lèi)： （1）AFTER觸發(fā)器：這類(lèi)觸發(fā)器是在記錄已經(jīng)被改變完，相關(guān)事務(wù)提交后，才會(huì)被觸發(fā)執(zhí)行。主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯(cuò)誤，可以用ROLLBACKTRANSACTION語(yǔ)句來(lái)回滾本次的操作。對(duì)同一個(gè)表達(dá)操作，可定義多個(gè)AFTER觸發(fā)器，并定義各種觸發(fā)器執(zhí)行的先后順序。 （2）INSTEADOF觸發(fā)器：這類(lèi)觸發(fā)器并不去執(zhí)行其所定義的操作（INSERT、UPDATE、DELETE），而去執(zhí)行觸發(fā)器本身所定義的操作。這類(lèi)觸發(fā)器一般是用來(lái)取代原本的操作，在記錄變更之前被觸發(fā)的。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）2.觸發(fā)器中的邏輯（虛擬）表當(dāng)表被修改，無(wú)論是插入、修改還是刪除，在數(shù)據(jù)行中所操作確切的記錄，都保存在兩個(gè)系統(tǒng)的邏輯表中，這兩個(gè)邏輯表是inserted（插入）表和deleted（刪除）表。這兩個(gè)表是建在數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存中的，是由系統(tǒng)管理的邏輯表，而不是真正存儲(chǔ)在數(shù)據(jù)庫(kù)中的物理表。對(duì)于這兩個(gè)表，用戶只有讀取的權(quán)限，沒(méi)有修改的權(quán)限。當(dāng)觸發(fā)器的工作完成之后，這兩個(gè)表將會(huì)從內(nèi)存中刪除。inserted表里存放的是更新前的記錄：對(duì)于INSERT操作來(lái)說(shuō)，INSERT觸發(fā)器執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。很顯然，只在進(jìn)行INSERT和UPDATE觸發(fā)器時(shí)，inserted表中有數(shù)據(jù)，而在DELETE觸發(fā)器中inserted表是空的。Deleted表里存放的是已從表中刪除的記錄：對(duì)于DELETE操作來(lái)說(shuō)，DELETE觸發(fā)器執(zhí)行，被刪除的舊記錄存放到Deleted表中。UPDATE操作等價(jià)于插入一條新記錄，同時(shí)刪除舊記錄。對(duì)于UPDATE操作來(lái)說(shuō)，UPDATE觸發(fā)器執(zhí)行，表中原記錄被移動(dòng)到Deleted表中（更新完后即被刪除），修改過(guò)的記錄插入到Inserted表中。inserted和deleted表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)是完全一致的。它們的操作和普通表的操作也一致。例如，若要檢索deleted表中的所有值，則使用語(yǔ)句：SELECT*FROMdeleted第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）3.創(chuàng)建DML觸發(fā)器的語(yǔ)法規(guī)則創(chuàng)建DML觸發(fā)器的語(yǔ)法規(guī)則如下：CREATETRIGGER觸發(fā)器名稱(chēng)ON{table|view} --指定操作的對(duì)象為表或視圖，視圖只能被INSTEADOF觸發(fā)器引用{FOR|AFTER|INSTEADOF} --觸發(fā)器的類(lèi)型{[INSERT][,][UPDATE][,][DELETE]} --指定數(shù)據(jù)修改操作，ASSQL語(yǔ)句 [,…n] 第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）【例10-6】創(chuàng)建觸發(fā)器stu_delete，實(shí)現(xiàn)如下功能：當(dāng)按照學(xué)號(hào)刪除student表中的某學(xué)生記錄后，對(duì)應(yīng)的該學(xué)生在sc表中的記錄也被自動(dòng)刪除。T-SQL語(yǔ)句為：USEstuinfoGOCREATETRIGGERstu_DeleteONstudentFORDELETEAS DELETEFROMscWHEREsno=(SELECTsnoFROMdeleted)執(zhí)行后，我們查詢student表和sc表，如圖10-6所示，可以看到兩個(gè)表中均存在學(xué)號(hào)為20070102的學(xué)生記錄。在student表中執(zhí)行數(shù)據(jù)刪除語(yǔ)句：DELETEFROMstudentWHEREsno='20070102'student表中有一行受影響而sc表中有三行數(shù)據(jù)受影響。說(shuō)明設(shè)定的觸發(fā)器被觸發(fā)，sc表中的相應(yīng)數(shù)據(jù)被自動(dòng)刪除。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）通過(guò)以上AFTER觸發(fā)器的例子我們可以驗(yàn)證，只有在成功執(zhí)行觸發(fā)T-SQL語(yǔ)句之后，才會(huì)激活A(yù)FTER觸發(fā)器。判斷執(zhí)行成功的標(biāo)準(zhǔn)是：執(zhí)行了所有與已更新對(duì)象或已刪除對(duì)象相關(guān)聯(lián)的引用級(jí)聯(lián)操作和約束檢查。以刪除表中記錄為例，整個(gè)執(zhí)行分為以下步驟：（1）當(dāng)系統(tǒng)接收到一個(gè)要執(zhí)行student表刪除操作的T-SQL語(yǔ)句時(shí)，系統(tǒng)將要?jiǎng)h除的記錄存放在刪除表Deleted中；（2）把數(shù)據(jù)表student中的相應(yīng)記錄刪除；（3）刪除操作激活了事先編制的AFTER觸發(fā)器，系統(tǒng)執(zhí)行AFTER觸發(fā)器中AS定義后的T-SQL語(yǔ)句；（4）觸發(fā)器執(zhí)行完畢之后，刪除內(nèi)存中的Deleted表，退出整個(gè)操作。若干觸發(fā)器語(yǔ)句執(zhí)行失敗，則整個(gè)過(guò)程回滾，恢復(fù)到初始狀態(tài)。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.2.3DDL觸發(fā)器的創(chuàng)建和應(yīng)用DDL觸發(fā)器可用于回滾違反規(guī)則的結(jié)構(gòu)更改、審核結(jié)構(gòu)更改或以合適的形式響應(yīng)結(jié)構(gòu)更改。DDL觸發(fā)器同DML觸發(fā)器一樣，在響應(yīng)事件時(shí)執(zhí)行?？梢允褂门cDML觸發(fā)器相似的T-SQL語(yǔ)法創(chuàng)建DDL觸發(fā)器，他們具有以下區(qū)別：DML觸發(fā)器響應(yīng)INSERT、UPDATE和DELETE語(yǔ)句的操作，而DDL觸發(fā)器響應(yīng)CREATE、ALTER和DROP語(yǔ)句的操作；只有在執(zhí)行完T-SQL語(yǔ)句后才會(huì)觸發(fā)DDL觸發(fā)器，即SQLServer僅支持AFTER類(lèi)型的DDL觸發(fā)器；系統(tǒng)不會(huì)為DDL觸發(fā)器創(chuàng)建Inserted表和Deleted表。第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）1.創(chuàng)建DDL觸發(fā)器的語(yǔ)法規(guī)則CREATETRIGGER觸發(fā)器名稱(chēng)ON{ALLSERVER|DATABASE} --指定觸發(fā)器的作用域{FOR|AFTER} --觸發(fā)器的類(lèi)型{事件類(lèi)型|事件組} [,…n] --指定數(shù)據(jù)修改操作ASSQL語(yǔ)句 [,…n] 第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）2.DDL觸發(fā)器的應(yīng)用【例10-7】創(chuàng)建服務(wù)器范圍的DDL觸發(fā)器，當(dāng)創(chuàng)建數(shù)據(jù)庫(kù)是，系統(tǒng)返回提示信息：“DATABASECREATED”。T-SQL語(yǔ)句為：CREATETRIGGERtrig_createONALLSERVERFORCREATE_DATABASEAS PRINT'DATABASECREATED'運(yùn)行創(chuàng)建觸發(fā)器后，使用語(yǔ)句：CREATEDATABASEdemo運(yùn)行結(jié)果，消息欄內(nèi)出現(xiàn)我們?cè)O(shè)定的“DATABASECREATED”第10章存儲(chǔ)過(guò)程和觸發(fā)器ppt課件（全）10.2.4查看、修改和刪除觸發(fā)器1.查看數(shù)據(jù)庫(kù)中已有觸發(fā)器查看表中已有哪些觸發(fā)器，這些觸發(fā)器究竟對(duì)表有哪些操作，我們需要能夠查看觸發(fā)其信息。查看觸發(fā)器有兩種常用方法：（1）使用SQLServer2008的SSMS查看觸發(fā)器信息在SQLServer2008中，展開(kāi)服務(wù)器和數(shù)據(jù)庫(kù)，此處我們選擇展開(kāi)stuinfo數(shù)據(jù)庫(kù)。選擇表student，展開(kāi)觸發(fā)器選項(xiàng)，即看到我們?cè)诶?0-6中建立的觸發(fā)器stu_delete。右擊觸發(fā)器stu_delete，從彈出的快捷菜單中選擇修改，