[教育學(xué)]情景8 視圖、索引、存儲(chǔ)過(guò)程及

1、單擊此處編輯母版標(biāo)題樣式單擊此處編輯母版文本樣式第二級(jí)第三級(jí)第四級(jí)第五級(jí)*單擊此處編輯母版標(biāo)題樣式單擊此處編輯母版文本樣式第二級(jí)第三級(jí)第四級(jí)第五級(jí)單擊此處編輯母版標(biāo)題樣式單擊此處編輯母版文本樣式第二級(jí)第三級(jí)第四級(jí)第五級(jí)單擊此處編輯母版標(biāo)題樣式單擊此處編輯母版文本樣式第二級(jí)第三級(jí)第四級(jí)第五級(jí)情景8 視圖、索引、存儲(chǔ)過(guò)程及觸發(fā)器設(shè)計(jì)?SQL Server數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用 ?目錄任務(wù)1 掌握視圖與索引任務(wù)2 掌握存儲(chǔ)過(guò)程任務(wù)3 掌握觸發(fā)器任務(wù)1 掌握視圖與索引視圖可以使用戶只關(guān)心自己感興趣的某些特定數(shù)據(jù)和自己所負(fù)責(zé)的特定任務(wù)，大大地簡(jiǎn)化了用戶對(duì)數(shù)據(jù)的操作。視圖提供了一個(gè)簡(jiǎn)單而有效的平安機(jī)制。建立索引

2、是提高查詢(xún)速度的有效手段，在數(shù)據(jù)庫(kù)中，索引使數(shù)據(jù)庫(kù)程序無(wú)需對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)。 8.1 視圖的概念及創(chuàng)立方法8.1.1 視圖的概念1視圖的定義視圖是從一個(gè)或者多個(gè)表或視圖中導(dǎo)出的表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對(duì)表的查詢(xún)根底上的。視圖是一個(gè)虛擬表，數(shù)據(jù)庫(kù)中只存視圖定義而不存視圖對(duì)應(yīng)的數(shù)據(jù)，數(shù)據(jù)仍存在原根本表中。 2使用視圖的優(yōu)點(diǎn)簡(jiǎn)化操作、視點(diǎn)集中、定制數(shù)據(jù)、合并分割數(shù)據(jù)、平安性注意：使用視圖時(shí)，要注意以下事項(xiàng)：1視圖一經(jīng)定義以后，就可以像表一樣被查詢(xún)、修改、刪除和更新。2只有在當(dāng)前數(shù)據(jù)庫(kù)中才能創(chuàng)立視圖。視圖的命名必須遵循標(biāo)識(shí)符命名規(guī)那么，不能與表同名，且對(duì)每個(gè)用戶視圖名必須是唯

3、一的，即對(duì)不同用戶，即使是定義相同的視圖，也必須使用不同的名字。3如果視圖引用的根本表或者視圖被刪除，那么該視圖不能再使用，直到創(chuàng)立新的基表或者視圖。4不能把規(guī)那么、默認(rèn)值或觸發(fā)器與視圖相關(guān)聯(lián)。5不能在視圖上建立任何索引、包括全文索引。 8.1.2 視圖的創(chuàng)立方法1使用CREATE VIEW語(yǔ)句創(chuàng)立視圖 USE webshopgo -此處的go不能省略CREATE VIEW goods_view AS SELECT * FROM goods WHERE g_Status=熱點(diǎn)go -此處的go也不能省略select * from goods_view語(yǔ)法格式：CREATE VIEW schem

4、a_name . view_name (column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ; 參數(shù)說(shuō)明：schema_name：視圖所屬架構(gòu)的名稱(chēng)。view_name：視圖的名稱(chēng)。視圖名稱(chēng)必須符合有關(guān)標(biāo)識(shí)符的規(guī)那么?？梢赃x擇是否指定視圖所有者名稱(chēng)。 column ：視圖中的列使用的名稱(chēng)。WITH ：指出視圖的屬性。view_attribute可取以下值：ENCRYPTION：說(shuō)明在系統(tǒng)表 syscomments 中存儲(chǔ) CREATE VIEW 語(yǔ)句時(shí)進(jìn)行加密。SCHEMABINDING：說(shuō)明將視圖與其所依賴(lài)的表或視圖結(jié)構(gòu)相關(guān)

5、聯(lián)。VIEW_METADATA：指定為引用視圖的查詢(xún)請(qǐng)求瀏覽模式的元數(shù)據(jù)時(shí)，向 DBLIB，ODBC或 OLEDB API 返回有關(guān)視圖的元數(shù)據(jù)信息，而不是返回給基表或其他表。AS ：指定視圖要執(zhí)行的操作。select_statement ：定義視圖的 SELECT 語(yǔ)句。該語(yǔ)句可以使用多個(gè)表和其他視圖。CHECK OPTION：指定在視圖執(zhí)行的所有數(shù)據(jù)修改語(yǔ)句都必須符合在 select_statement 中設(shè)置的條件。【例8-1】運(yùn)行分析以下語(yǔ)句段USE webshop-select * from goods-select * from orderdetailsgocreate view

6、goods_orderdetails_view asselect distinct goods.g_ID 商品編號(hào),g_name 商品名稱(chēng),d_price 進(jìn)價(jià),g_price 零售價(jià)from goods,orderdetails where goods.g_ID=orderdetails.g_IDgoselect * from goods_orderdetails_view注意：CREATE VIEW 必須是批命令的第一條語(yǔ)句。Select 語(yǔ)句中不能使用 ORDER BY、INTO 等子句。2在 SSMS 中創(chuàng)立視圖一、利用SSMS創(chuàng)立上列兩個(gè)視圖，命令和操作相結(jié)合，調(diào)試執(zhí)行，最后注意保存

7、。二、演示操作過(guò)程。圖8-1 創(chuàng)立視圖 8.2 索引的概念及創(chuàng)立方法 8.2.1 索引的概念1索引的定義索引是一個(gè)單獨(dú)的、物理的數(shù)據(jù)結(jié)構(gòu)，是某個(gè)表中一列或假設(shè)干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁(yè)的邏輯指針清單。索引依賴(lài)于表建立的，提供了編排表中數(shù)據(jù)的內(nèi)部方法。索引可以快速找到表或索引視圖中的特定信息。但創(chuàng)立索引要花費(fèi)時(shí)間和占用存儲(chǔ)空間，也降低了數(shù)據(jù)修改的速度。 2索引的分類(lèi) 索引是與表或視圖關(guān)聯(lián)的磁盤(pán)上結(jié)構(gòu)，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。 表或視圖可以包含以下類(lèi)型的索引：1聚集索引。 聚集索引根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲(chǔ)這些

8、數(shù)據(jù)行。索引定義中包含聚集索引列。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序排序。2非聚集索引。 非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引包含非聚集索引鍵值，并且每個(gè)鍵值項(xiàng)都有指向包含該鍵值的數(shù)據(jù)行的指針。 注意：1索引可以是唯一的，這意味著不會(huì)有兩行記錄相同的索引鍵值，這樣的索引稱(chēng)為唯一索引。如果索引是根據(jù)多列組合創(chuàng)立的，這樣的索引稱(chēng)為復(fù)合索引。2索引是非顯示的，查詢(xún)時(shí)自動(dòng)調(diào)用。3當(dāng)表中創(chuàng)立主鍵約束PRIMARY KEY時(shí) SQL Server 將自動(dòng)創(chuàng)立唯一性聚集索引；當(dāng)表中創(chuàng)立唯一性約束UNIQUE時(shí)，SQL Server自動(dòng)創(chuàng)立一個(gè)唯一性非聚集索引。假設(shè)表中已有數(shù)據(jù)，那

9、么在創(chuàng)立索引時(shí)，SQL Server會(huì)檢查數(shù)據(jù)的合法性，假設(shè)有不合法數(shù)據(jù)，那么創(chuàng)立索引失敗。8.2.2 索引的創(chuàng)立方法1使用CREATE INDEX語(yǔ)句創(chuàng)立索引1選擇創(chuàng)立索引的列。 如果在一個(gè)列上創(chuàng)立索引，該列就稱(chēng)為索引列。通常使用的索引列有如下這些：表的主鍵列。連接中頻繁使用的列。在某一范圍內(nèi)頻繁搜索的列和按排列順序頻繁檢索的列。 建立索引需要產(chǎn)生一定的存儲(chǔ)開(kāi)銷(xiāo)，在進(jìn)行插入和更新數(shù)據(jù)的操作時(shí)，維護(hù)索引也要花費(fèi)時(shí)間和空間，因此沒(méi)有必要對(duì)表中的所有列都建立索引。一般來(lái)所，下面這些列不考慮建立索引：很少被查詢(xún)的列。只有幾個(gè)值的列，如“性別只有兩個(gè)值，“男和“女。以 bit、text、image 數(shù)

10、據(jù)類(lèi)型定義的列。 另外，數(shù)據(jù)行數(shù)很少的小表一般也沒(méi)有必要建立索引。2語(yǔ)法格式：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON ( column ASC | DESC ,.n ) 參數(shù)說(shuō)明：UNIQUE：為表或視圖創(chuàng)立唯一索引。唯一索引不允許兩行具有相同的索引鍵值。視圖的聚集索引必須唯一。CLUSTERED：表示創(chuàng)立聚集索引。創(chuàng)立索引時(shí)，鍵值的邏輯順序決定表中對(duì)應(yīng)行的物理順序。聚集索引的底層或稱(chēng)葉級(jí)別包含該表的實(shí)際數(shù)據(jù)行。一個(gè)表或視圖只允許同時(shí)有一個(gè)聚集索引。具有唯一聚集索引的視圖稱(chēng)為索引視圖。NONCLUSTERED：表示創(chuàng)

11、立的索引為聚集索引。ASC | DESC：指定索引值的排列順序，ASC 表示升序，DESC 表示降序，缺省值為 ASC。8.2.2 索引的創(chuàng)立方法【例8-2】在Student_info表中學(xué)號(hào)列創(chuàng)立一個(gè)名 STU_INDEX 的唯一聚集索引。 CREATE UNIQUE CLUSTERED INDEX STU_INDEX ON Student_infoSid2在 SSMS 中創(chuàng)立索引 1啟動(dòng)SQL Server Management Studio，在對(duì)象資源管理器中依次展開(kāi)“數(shù)據(jù)庫(kù)節(jié)點(diǎn)、Student節(jié)點(diǎn)和表節(jié)點(diǎn)，在 Student_info 表上單擊鼠標(biāo)右鍵，在彈出的快捷選單上選擇“設(shè)計(jì)，如

12、圖8-5 所示的表設(shè)計(jì)器界面。 8.2.2 索引的創(chuàng)立方法圖8-5 表設(shè)計(jì)器界面2在表設(shè)計(jì)器界面上右擊，出現(xiàn)如圖8-6 所示的快捷選單，在快捷選單中選擇“索引/鍵選單項(xiàng)，出現(xiàn)如圖8-7 所示的“索引/鍵對(duì)話框。圖8-7 “索引/鍵對(duì)話框圖8-6 快捷選單3在如圖8-7 所示的“索引/鍵對(duì)話框中單擊“添加按鈕，進(jìn)行索引的設(shè)置，如圖8-8 所示，可以設(shè)置索引的類(lèi)型、選擇索引列、是否唯一設(shè)置、名稱(chēng)設(shè)置等。圖8-8 “索引/鍵設(shè)置8.2.2 索引的創(chuàng)立方法8.3 Student數(shù)據(jù)庫(kù)中視圖與索引的創(chuàng)立 8.3.1 視圖的創(chuàng)立 下面用 T-SQL 語(yǔ)句來(lái)創(chuàng)立 Student 數(shù)據(jù)庫(kù)中的視圖?！纠?-3】

13、建立視圖 VIEW1，輸出所有學(xué)生的年齡。-use student-select * from student_infocreate view view1asselect sid, Sname, YEAR(GETDATE()-YEAR(Sbirth) AS sagefrom Student_infogoselect * from view1【例8-4】建立視圖 VIEW2，查找總數(shù)量在50分以上的商品的編號(hào)和數(shù)量。use webshopgocreate view view2asselect t_ID 商品編號(hào),sum(g_Number) 總數(shù)量from goods group by t_ID

14、having sum(g_Number)=50go select * from view28.3.2 索引的創(chuàng)立 假設(shè) Student 數(shù)據(jù)庫(kù)中的課程表信息，成績(jī)表信息經(jīng)常會(huì)被用戶查詢(xún)，為了加快查詢(xún)速度，我們分別為經(jīng)常查詢(xún)的字段建立索引。 【例8-6】為Course_info 表的課程名列創(chuàng)立索引。use studentif exists(select name from sysindexes where name=Course_index) drop index Course_info.Course_index /*刪除重復(fù)索引文件*/gocreate index Course_index

15、on Course_info(Cname)go【例8-7】為Course_info 表的課程號(hào)列創(chuàng)立唯一聚集索引。因指定了 CLUSTERED 子句，該索引將對(duì)磁盤(pán)上的數(shù)據(jù)進(jìn)行物理排序。use studentif exists(select name from sysindexes where name=Course_index_id) drop index Course_info.Course_index_idgocreate clustered index Course_index_id on Course_info(Cid)go【例8-8】為 SC 表的學(xué)號(hào)列和課程列創(chuàng)立復(fù)合索引。use

16、 studentif exists(select name from sysindexes where name=SC_index) drop index SC.SC_indexgocreate index SC_index on SC(Sid,Cid)Go通常，在唯一列、非空列或標(biāo)識(shí)列上創(chuàng)立聚集索引可獲較佳性能，在查詢(xún)經(jīng)常用到的所有列上創(chuàng)立非聚集索引可獲較佳性能。 任務(wù)2 掌握存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程是一組為了完成特定功能的 SQL 語(yǔ)句集，該語(yǔ)句集經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)效勞器中，因此稱(chēng)它為存儲(chǔ)過(guò)程，使用時(shí)只要調(diào)用即可。從而實(shí)現(xiàn)模塊化編程，加快程序的運(yùn)行速度。 8.4 存儲(chǔ)過(guò)程的概念 1存儲(chǔ)過(guò)

17、程的概念存儲(chǔ)過(guò)程是是一組為了完成特定功能的 SQL 語(yǔ)句集，經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)如果存儲(chǔ)過(guò)程帶有參數(shù)來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是獨(dú)立存在于表之外的數(shù)據(jù)庫(kù)對(duì)象?？梢杂煽蛻粽{(diào)用它，也可以從另一個(gè)過(guò)程或觸發(fā)器調(diào)用它，它的參數(shù)可以被傳遞和返回，它的出錯(cuò)代碼也可以被檢驗(yàn)。存儲(chǔ)過(guò)程有如下優(yōu)點(diǎn)：1存儲(chǔ)過(guò)程在效勞器端運(yùn)行，執(zhí)行速度快。2存儲(chǔ)過(guò)程執(zhí)行一次后，就生成了執(zhí)行方案，駐留在高速緩沖存儲(chǔ)器中，以后每次調(diào)用即可，提高了系統(tǒng)的性能。 3確保數(shù)據(jù)庫(kù)的平安。使用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并可以通過(guò)編程方式控制上述操作對(duì)數(shù)據(jù)庫(kù)信息訪問(wèn)的權(quán)限。4自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)或

18、者預(yù)定的功能。存儲(chǔ)過(guò)程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行，完成一些需要預(yù)先執(zhí)行的任務(wù)，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手工操作，大大方便了用戶的使用。2存儲(chǔ)過(guò)程的類(lèi)型 在 SQL Server 2021 中，存儲(chǔ)過(guò)程有兩種類(lèi)型：Transact-SQL 或 CLR。1Transact-SQL:指保存的 Transact-SQL 語(yǔ)句集合，可以接受和返回用戶提供的參數(shù)。我們主要學(xué)習(xí)Transact-SQL存儲(chǔ)過(guò)程。2CLR。CLR 存儲(chǔ)過(guò)程是指對(duì) Microsoft .NET Framework 公共語(yǔ)言運(yùn)行時(shí) (CLR) 方法的引用，可以接受和返回用戶提供的參數(shù)。 8.4 存儲(chǔ)過(guò)程的概念 Transact-SQ

19、L存儲(chǔ)過(guò)程分為兩類(lèi)：系統(tǒng)提供的存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程和用戶自定義的存儲(chǔ)過(guò)程。系統(tǒng)存儲(chǔ)過(guò)程定義在master數(shù)據(jù)庫(kù)中并以sp_為前綴。例如常用的顯示系統(tǒng)對(duì)象信息的sp_help存儲(chǔ)過(guò)程，它們?yōu)闄z索系統(tǒng)表的信息方便、快捷的方法。盡管這些系統(tǒng)存儲(chǔ)過(guò)程被放在master數(shù)據(jù)庫(kù)中，但是仍可以在其它數(shù)據(jù)庫(kù)中對(duì)其進(jìn)行調(diào)用，調(diào)用時(shí)不必在存儲(chǔ)過(guò)程名前加上數(shù)據(jù)庫(kù)名，而且當(dāng)創(chuàng)立一個(gè)新數(shù)據(jù)庫(kù)時(shí)，一些系統(tǒng)存儲(chǔ)過(guò)程會(huì)在新數(shù)據(jù)庫(kù)中被自動(dòng)創(chuàng)立。用戶自定義存儲(chǔ)過(guò)程是由用戶創(chuàng)立的，并能完成一定的功能，如查詢(xún)用戶所需要數(shù)據(jù)信息、數(shù)據(jù)統(tǒng)計(jì)分析等。用戶自定義存儲(chǔ)過(guò)程只能定義在當(dāng)前數(shù)據(jù)庫(kù)中，默認(rèn)情況下，用戶自定義存儲(chǔ)過(guò)程歸數(shù)據(jù)庫(kù)所有者所有

20、，數(shù)據(jù)庫(kù)所有者可以把許可授權(quán)給其它用戶同。8.5 存儲(chǔ)過(guò)程的創(chuàng)立方法 1使用 T-SQL 語(yǔ)句創(chuàng)立存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程定義包括兩個(gè)主要內(nèi)容：過(guò)程名和參數(shù)的說(shuō)明以及過(guò)程體包含執(zhí)行存儲(chǔ)過(guò)程操作的 SQL 語(yǔ)句。創(chuàng)立存儲(chǔ)過(guò)程前，應(yīng)該注意以下事項(xiàng)：不能將 CREATE PROCEDURE 語(yǔ)句與其他 SQL 語(yǔ)句組合到單個(gè)批處理中。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)對(duì)象，其名稱(chēng)必須遵循標(biāo)識(shí)符的命名規(guī)那么。只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)立存儲(chǔ)過(guò)程每個(gè)存儲(chǔ)過(guò)程最多可以使用1024個(gè)參數(shù)。存儲(chǔ)過(guò)程最多支持32層嵌套。語(yǔ)法格式：CREATE PROC | PROCEDURE procedure_name ; number /*定義過(guò)程名

21、parameter type_schema_name. data_type /*定義參數(shù)類(lèi)型 VARYING = default OUT | OUTPUT READONLY /*定義參數(shù)的屬性 ,.n WITH ,.n /*定義存儲(chǔ)過(guò)程的處理方式 FOR REPLICATION /*說(shuō)明不能在訂閱效勞器上執(zhí)行為復(fù)制創(chuàng)立的存儲(chǔ)過(guò)程AS ; .n /*執(zhí)行的操作 := ENCRYPTION RECOMPILE EXECUTE AS Clause := BEGIN statements END 8.5 存儲(chǔ)過(guò)程的創(chuàng)立方法 參數(shù)說(shuō)明：number ：是可選整數(shù)，用于對(duì)同名的過(guò)程分組。VARYING：指

22、定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變。default OUT | OUTPUT READONLY：default ，參數(shù)的默認(rèn)值。如果定義了 default 值，那么無(wú)需指定此參數(shù)的值即可執(zhí)行過(guò)程；OUTPUT， 指示參數(shù)是輸出參數(shù)。此選項(xiàng)的值可以返回給調(diào)用 EXECUTE 的語(yǔ)句。使用 OUTPUT 參數(shù)將值返回給過(guò)程的調(diào)用方；READONLY ，指示不能在過(guò)程的主體中更新或修改參數(shù)。ENCRYPTION：表示 SQL SERVER 加密 syscomments 表中包含 CREATE PROC 語(yǔ)句文本的條目，可防止將過(guò)程作為 SQL SERVER 復(fù)制

23、的一局部發(fā)布，防止用戶使用系統(tǒng)存儲(chǔ)過(guò)程讀取存儲(chǔ)過(guò)程的定義文本。RECOMPILE：說(shuō)明 SQL SERVER 每次運(yùn)行該過(guò)程時(shí)，將對(duì)其重新編譯。EXECUTE AS ：指定在其中執(zhí)行存儲(chǔ)過(guò)程的平安上下文 【例8-9】創(chuàng)立一個(gè)存儲(chǔ)過(guò)程 goods_sp ：use webshopgocreate proc goods_sp tid char(10)asselect g_Name 名稱(chēng),g_Price 價(jià)格,g_Number 數(shù)量,g_Status 狀態(tài)from goods where t_id=tidgoexec goods_sp 012在 SSMS 中創(chuàng)立存儲(chǔ)過(guò)程1啟動(dòng)SQL Server Ma

24、nagement Studio，在對(duì)象資源管理器中依次展開(kāi)“數(shù)據(jù)庫(kù)節(jié)點(diǎn)、Student節(jié)點(diǎn)和可編程性節(jié)點(diǎn)，在“存儲(chǔ)過(guò)程上單擊鼠標(biāo)右鍵，在彈出的快捷選單上選擇“新建存儲(chǔ)過(guò)程，如圖8-9 所示。 圖8-9 “新建存儲(chǔ)過(guò)程選擇2在彈出的存儲(chǔ)過(guò)程編輯器語(yǔ)法模版中直接修改編輯，也可以刪除模版重新編輯，如圖8-10 所示。 圖8-10 存儲(chǔ)過(guò)程編輯器界面3編輯完成后，單擊編輯器窗口右上角的關(guān)閉按鈕，出現(xiàn)如圖8-11 所示的是否需要保存文件對(duì)話框。 圖8-11 “保存更改對(duì)話框4選擇“是按鈕，出現(xiàn)出現(xiàn)如圖8-12 所示的“另存文件對(duì)話框，在其中輸入保存路徑、文件名，并單擊“保存按鈕，便完成了存儲(chǔ)過(guò)程的創(chuàng)立。

25、 圖8-12 “另存文件對(duì)話框8.6 存儲(chǔ)過(guò)程的運(yùn)行、刪除和查看 存儲(chǔ)過(guò)程的運(yùn)行:Exec goods_sp 01或者Execute goods_sp 01goods_sp 01 Exec goods_sp tid =01或者Execute goods_sp tid =01存儲(chǔ)過(guò)程的刪除:Drop procedure 存儲(chǔ)過(guò)程名例如： Drop procedure goods_sp存儲(chǔ)過(guò)程的查看：Sp_help 存儲(chǔ)過(guò)程名 /Sp_helptext 存儲(chǔ)過(guò)程名例如： Sp_help goods_sp/Sp_helptext goods_sp任務(wù)3 掌握觸發(fā)器8.7 觸發(fā)器的概念 1觸發(fā)器的概念

26、觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程，它是通過(guò)事件觸發(fā)而被執(zhí)行的。觸發(fā)器基于一個(gè)表創(chuàng)立并和一個(gè)或多個(gè)數(shù)據(jù)修改操作插入數(shù)據(jù)、更新數(shù)據(jù)或刪除數(shù)據(jù)相關(guān)聯(lián)。當(dāng)出現(xiàn)一次這樣的操作時(shí)，觸發(fā)器就會(huì)自動(dòng)激活。2觸發(fā)器的類(lèi)型 SQL Server 包括三種常規(guī)類(lèi)型的觸發(fā)器：DML 觸發(fā)器、DDL 觸發(fā)器和登錄觸發(fā)器。 1當(dāng)效勞器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言 (DDL) 事件時(shí)將調(diào)用 DDL 觸發(fā)器。2登錄觸發(fā)器將為響應(yīng) LOGON 事件而激發(fā)存儲(chǔ)過(guò)程。與 SQL Server 實(shí)例建立用戶會(huì)話時(shí)將引發(fā)此事件。3當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言 (DML) 事件時(shí)將調(diào)用 DML 觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的

27、insert語(yǔ)句、update語(yǔ)句或delete語(yǔ)句。觸發(fā)器主要有以下優(yōu)點(diǎn)：利用觸發(fā)器可以方便地實(shí)現(xiàn)數(shù)據(jù)庫(kù)中數(shù)據(jù)的完整性。觸發(fā)器是自動(dòng)的，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行了任何修改操作之后立即被激活。觸發(fā)器可以進(jìn)行強(qiáng)制限制，這些限制比用 CHECK 約束所定義的更復(fù)雜。8.7 觸發(fā)器的概念 8.8 觸發(fā)器的工作機(jī)制 1觸發(fā)器中使用的特殊表執(zhí)行觸發(fā)器時(shí)，系統(tǒng)自動(dòng)創(chuàng)立了兩個(gè)特殊的邏輯表：inserted 表和deleted 表。用戶不能對(duì)這兩個(gè)表進(jìn)行修改，它們存在內(nèi)存而不是數(shù)據(jù)庫(kù)中。觸發(fā)器執(zhí)行完成后，這兩個(gè)表也被自動(dòng)刪除。inserted邏輯表：當(dāng)向表中插入數(shù)據(jù)時(shí)，INSERT 觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸

28、發(fā)器表和inserted邏輯表中。deleted 邏輯表：用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個(gè) DELETE 觸發(fā)器時(shí)，被刪除的記錄存放到 deleted 邏輯表中。 修改一條記錄等于刪除一條舊記錄，同時(shí)插入一條新記錄。當(dāng)對(duì)update觸發(fā)器的表記錄修改時(shí)，表中原記錄移到deleted邏輯表中，修改正的記錄插入到inserted邏輯表中。2觸發(fā)器的工作機(jī)制1INSERT 觸發(fā)器工作原理。 當(dāng)一個(gè)記錄插入到表中時(shí)，INSERT 觸發(fā)器自動(dòng)觸發(fā)執(zhí)行，系統(tǒng)創(chuàng)立一個(gè) inserted 表，新的記錄被增加到該觸發(fā)器表和 inserted 表中。2DELETE 觸發(fā)器工作原理。 當(dāng)從表中刪除一條記錄時(shí)，

29、DELETE 觸發(fā)器自動(dòng)觸發(fā)執(zhí)行，系統(tǒng)創(chuàng)立一個(gè)deleted 表，被刪除的記錄移到 deleted 表中。 3UPDATE 觸發(fā)器工作原理。 數(shù)據(jù)更新可以看成是由刪除一條舊記錄的 DELETE 語(yǔ)句和插入一條新記錄的 INSERT 語(yǔ)句組成的。 8.8 觸發(fā)器的工作機(jī)制 8.9 觸發(fā)器的創(chuàng)立方法 創(chuàng)立觸發(fā)器前，應(yīng)該注意以下事項(xiàng)：創(chuàng)立觸發(fā)器的權(quán)限默認(rèn)授予定義觸發(fā)器表的所有者、并且該權(quán)限不可以轉(zhuǎn)讓。觸發(fā)器不返回任何結(jié)果，所以不要在觸發(fā)器定義中包含 SELECT 語(yǔ)句或者變量賦值，如果必須在觸發(fā)器中進(jìn)行變量賦值，那么應(yīng)該在觸發(fā)器的開(kāi)頭使用 SET NOCOUNT 語(yǔ)句以防止返回任何結(jié)果集。觸發(fā)器為數(shù)

30、據(jù)庫(kù)對(duì)象，其名稱(chēng)必須遵循標(biāo)識(shí)符的命名規(guī)那么。只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)立觸發(fā)器。語(yǔ)法格式：CREATE TRIGGER trigger_name /*創(chuàng)立觸發(fā)器關(guān)鍵字和觸發(fā)器名稱(chēng)ON table | view /*觸發(fā)器表或觸發(fā)器視圖 WITH ,.n FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS sql_statement /*觸發(fā)條件和操作 := ENCRYPTION EXECUTE AS Clause 參數(shù)說(shuō)明：WITH ENCRYPTION：對(duì) CREATE TRIGGER 語(yǔ)句的文本進(jìn)行模糊處理。使用 WITH ENCRYPTION 可以防止將觸發(fā)器作為 SQL Server 復(fù)制的一局部進(jìn)行發(fā)布。EXECUTE AS： 指定用于執(zhí)行該觸發(fā)器的平安上下文。FOR | AFTER ：AFTER 指定 DML 觸發(fā)器僅在觸發(fā) SQL 語(yǔ)句中指定的所有操作都已成功執(zhí)行