第6章 創(chuàng)建和管理數(shù)據(jù)庫(kù)對(duì)象

第6章創(chuàng)建(chuàngjiàn)和管理數(shù)據(jù)庫(kù)對(duì)象計(jì)算機(jī)系胡駿共八十五頁(yè)數(shù)據(jù)庫(kù)的對(duì)象(duìxiàng)數(shù)據(jù)庫(kù)的對(duì)象包括索引、視圖、觸發(fā)器、存儲(chǔ)過(guò)程、關(guān)系圖、用戶定義的數(shù)據(jù)類型、用戶定義的函數(shù)。索引加快查詢速度視圖控制用戶對(duì)數(shù)據(jù)的訪問(wèn)觸發(fā)器對(duì)表進(jìn)行插入(chārù)、更改、刪除操作時(shí)自動(dòng)運(yùn)行的命令存儲(chǔ)過(guò)程完成特定功能的命令。用戶定義的數(shù)據(jù)類型豐富數(shù)據(jù)類型的種類，方面用戶使用用戶定義的函數(shù)靈活的為各種計(jì)算提供函數(shù)支持。共八十五頁(yè)6.1創(chuàng)建和管理(guǎnlǐ)索引SQL訪問(wèn)(fǎngwèn)表中數(shù)據(jù)的方式：①表掃描方式②遍歷索引共八十五頁(yè)SQL訪問(wèn)表中數(shù)據(jù)(shùjù)的方式共八十五頁(yè)使用(shǐyòng)索引的優(yōu)缺點(diǎn)優(yōu)點(diǎn)：加快查詢搜索數(shù)據(jù)的速度缺點(diǎn)：若對(duì)索引列上數(shù)據(jù)進(jìn)行刪除、更新、插入操作所耗費(fèi)的時(shí)間比沒(méi)有(méiyǒu)索引時(shí)要長(zhǎng)。結(jié)論：小心設(shè)定索引。共八十五頁(yè)6.1.1索引的設(shè)計(jì)原則(yuánzé)和索引類型1.索引設(shè)計(jì)原則：⑴創(chuàng)建索引對(duì)精確查詢、范圍查詢、以及外鍵約束都是有利的。⑵一個(gè)表中有較多索引，影響數(shù)據(jù)的添加、更新(gēngxīn)、刪除時(shí)的性能。⑶小型表不需要索引，因?yàn)楸闅v表所花費(fèi)的時(shí)間會(huì)比遍歷索引短。共八十五頁(yè)6.1.1索引的設(shè)計(jì)原則(yuánzé)和索引類型⑷在頻繁搜索的字段上建立(jiànlì)索引：①主鍵②外鍵③經(jīng)常進(jìn)行范圍搜索、精確搜索的字段④按關(guān)鍵字排序的字段共八十五頁(yè)6.1.1索引(suǒyǐn)的設(shè)計(jì)原則和索引(suǒyǐn)類型⑸不需要索引的字段①查詢中很少涉及(shèjí)的字段②有大量重復(fù)值的字段③更新性能比查詢性能更重要的列④定義TEXT、NTEXT或IMAGE數(shù)據(jù)類型的字段⑹對(duì)外鍵創(chuàng)建索引時(shí)先創(chuàng)建聚集索引在創(chuàng)建非聚集索引。共八十五頁(yè)6.1.1索引(suǒyǐn)的設(shè)計(jì)原則和索引(suǒyǐn)類型2.索引(suǒyǐn)的類型聚集索引：基于數(shù)據(jù)行的鍵值在表內(nèi)排序和存儲(chǔ)這些數(shù)據(jù)行。非聚集索引：具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引的最低行包含非聚集索引的鍵值，并且每個(gè)鍵值項(xiàng)都有指針指向包含該鍵值的數(shù)據(jù)行。共八十五頁(yè)共八十五頁(yè)共八十五頁(yè)何時(shí)使用(shǐyòng)聚集索引或非聚集索引動(dòng)作描述使用聚集索引使用非聚集索引列經(jīng)常被分組排序應(yīng)應(yīng)返回某范圍內(nèi)的數(shù)據(jù)應(yīng)不應(yīng)一個(gè)或極少不同值不應(yīng)不應(yīng)小數(shù)目的不同值應(yīng)不應(yīng)大數(shù)目的不同值不應(yīng)應(yīng)頻繁更新的列不應(yīng)應(yīng)外鍵列應(yīng)應(yīng)主鍵列應(yīng)應(yīng)頻繁修改索引列不應(yīng)應(yīng)共八十五頁(yè)測(cè)試聚集(jùjí)索引和非聚集(jùjí)索引性能現(xiàn)搭建(dājiàn)好測(cè)試平臺(tái)：學(xué)生基本情況表中有100萬(wàn)條記錄。運(yùn)行程序：select*from學(xué)生基本情況表where出生日期>‘1980-3-20’①當(dāng)在出生日期上建立非聚集索引時(shí)用時(shí)：53763毫秒（54秒）

②當(dāng)在出生日期上建立聚集索引時(shí)用時(shí)：2423毫秒（2秒）想一想為什么？共八十五頁(yè)怎樣測(cè)量查詢(cháxún)時(shí)間？declare@ddatetimeset@d=getdate()select*from學(xué)生基本(jīběn)情況表where出生日期>‘1980-3-20‘select[執(zhí)行時(shí)間(毫秒)]=Datediff(ms,@d,getdate())共八十五頁(yè)6.1.2創(chuàng)建(chuàngjiàn)索引1索引向?qū)?企業(yè)(qǐyè)管理器3查詢分析器4企業(yè)管理器中索引優(yōu)化向?qū)Ч舶耸屙?yè)利用T-SQL語(yǔ)言(yǔyán)創(chuàng)建、查看、修改索引創(chuàng)建索引語(yǔ)法格式(géshi)：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}查看索引語(yǔ)法格式：sp_helpindextable_name修改索引名稱sp_renameold_index_name,new_index_name,‘index’刪除索引dropindexindex_name

共八十五頁(yè)利用(lìyòng)T-SQL語(yǔ)言創(chuàng)建、查看、修改索引在學(xué)生基本情況表中對(duì)學(xué)號(hào)建立聚集(jùjí)索引Createclusteredindexxs_聚集on學(xué)生基本情況（學(xué)號(hào)）把上述索引名稱改為學(xué)生索引sp_rename‘學(xué)生基本情況表.xs_聚集’,’學(xué)生索引’,‘index’查看學(xué)生基本情況表中索引信息sp_helpindex學(xué)生基本情況表刪除學(xué)生索引dropindex學(xué)生基本情況表.學(xué)生索引

共八十五頁(yè)6.2創(chuàng)建(chuàngjiàn)和管理視圖視圖作為一種基本的數(shù)據(jù)庫(kù)對(duì)象，是查詢一個(gè)表或多個(gè)表的另一種方法，它是通過(guò)把預(yù)先定義的查詢存儲(chǔ)在數(shù)據(jù)庫(kù)中，然后就可以再查詢語(yǔ)句中調(diào)用(diàoyòng)它。提示：①對(duì)于查詢的表我們把它叫做基表

②視圖來(lái)自于一個(gè)基表還是幾個(gè)基表對(duì)視圖中的操作有絕對(duì)的影響共八十五頁(yè)6.2.1視圖(shìtú)1.視圖的概念是一種虛擬的表或存儲(chǔ)查詢，它只包含表的一部分，其內(nèi)容有查詢需求定義。2.視圖的作用①返回用戶需要的數(shù)據(jù)②是數(shù)據(jù)庫(kù)查詢簡(jiǎn)便直觀③方便數(shù)據(jù)的導(dǎo)出④實(shí)現(xiàn)(shíxiàn)對(duì)創(chuàng)建視圖的內(nèi)部表進(jìn)行數(shù)據(jù)修改共八十五頁(yè)6.2.2創(chuàng)建(chuàngjiàn)視圖1.使用(shǐyòng)企業(yè)管理器創(chuàng)建視圖見(jiàn)錄像2.使用T-SQL語(yǔ)言創(chuàng)建視圖略共八十五頁(yè)6.2.3查看(chákàn)視圖1.使用企業(yè)管理器查看(chákàn)視圖共八十五頁(yè)6.2.3查看(chákàn)視圖2.使用存儲(chǔ)過(guò)程(guòchéng)查看視圖共八十五頁(yè)6.2.4修改、刪除(shānchú)及重命名視圖1.修改視圖ALTERVIEW班級(jí)視圖ASSELECT專業(yè)代碼(dàimǎ),系部名稱,專業(yè)名稱FROMdbo.班級(jí)2.重命名視圖SP_RENAME‘班級(jí)視圖’,‘班級(jí)視圖列表’3.刪除視圖DROPVIEW班級(jí)視圖列表共八十五頁(yè)6.2.5使用視圖操作(cāozuò)表數(shù)據(jù)1.使用視圖檢索(jiǎnsuǒ)數(shù)據(jù)可以在視圖中查詢數(shù)據(jù)SELECT班級(jí)名稱FROM班級(jí)視圖WHERE班級(jí)名稱=‘計(jì)算機(jī)網(wǎng)絡(luò)’共八十五頁(yè)6.2.5使用(shǐyòng)視圖操作表數(shù)據(jù)討論在視圖中添加、修改、刪除數(shù)據(jù)的時(shí)候(shíhou)，分為兩種情況：1視圖來(lái)自于一個(gè)基表2視圖來(lái)自于幾個(gè)不同的基表共八十五頁(yè)視圖(shìtú)與單個(gè)基表之間的關(guān)系①基表數(shù)據(jù)更改后，視圖數(shù)據(jù)會(huì)更改嗎？見(jiàn)錄像實(shí)驗(yàn)結(jié)果：基表數(shù)據(jù)更改后，視圖數(shù)據(jù)也更改。②視圖中數(shù)據(jù)更改后，基表數(shù)據(jù)會(huì)更改嗎？見(jiàn)錄像實(shí)驗(yàn)結(jié)果：視圖數(shù)據(jù)更改后，基表數(shù)據(jù)也會(huì)更改。③視圖更改后，其他(qítā)引用這個(gè)視圖的視圖會(huì)更改嗎？實(shí)驗(yàn)結(jié)果：會(huì)（自己試驗(yàn)）共八十五頁(yè)視圖(shìtú)與單個(gè)基表之間的關(guān)系④修改視圖中的數(shù)據(jù)，基表中的數(shù)據(jù)跟著進(jìn)行修改嗎？會(huì)（自己試驗(yàn)(shìyàn)）。⑤刪除視圖中的數(shù)據(jù)，基表中的數(shù)據(jù)會(huì)跟著刪除嗎？會(huì)（自己試驗(yàn)）。共八十五頁(yè)視圖(shìtú)與多個(gè)基表之間的關(guān)系共八十五頁(yè)視圖與多個(gè)(duōɡè)基表之間的關(guān)系共八十五頁(yè)視圖(shìtú)與多個(gè)基表之間的關(guān)系①基表數(shù)據(jù)更改后，視圖數(shù)據(jù)會(huì)更改嗎？實(shí)驗(yàn)結(jié)果(jiēguǒ)：基表數(shù)據(jù)更改后，視圖數(shù)據(jù)也更改。②視圖更改后，其他引用這個(gè)視圖的視圖會(huì)更改嗎？實(shí)驗(yàn)結(jié)果：會(huì)（自己試驗(yàn)）共八十五頁(yè)視圖與多個(gè)(duōɡè)基表之間的關(guān)系③視圖中數(shù)據(jù)(shùjù)更改后，基表數(shù)據(jù)(shùjù)會(huì)更改嗎？實(shí)驗(yàn)結(jié)果：視圖數(shù)據(jù)更改后，多個(gè)基表數(shù)據(jù)也會(huì)更改。共八十五頁(yè)視圖(shìtú)與多個(gè)基表之間的關(guān)系④向視圖(shìtú)中插入數(shù)據(jù)，基表中的數(shù)據(jù)跟著進(jìn)行修改嗎？不會(huì)（自己試驗(yàn)）。共八十五頁(yè)視圖與多個(gè)(duōɡè)基表之間的關(guān)系⑤刪除視圖(shìtú)中的數(shù)據(jù)，基表中的數(shù)據(jù)會(huì)跟著刪除嗎？不會(huì)（自己試驗(yàn)）。共八十五頁(yè)總結(jié)(zǒngjié)查詢插入刪除更新視圖與單個(gè)基表能查詢視圖中插入數(shù)據(jù)影響到基表視圖中刪除數(shù)據(jù)影響到基表視圖中更新數(shù)據(jù)影響到基表視圖與多個(gè)基表能查詢視圖中插入數(shù)據(jù)不成立視圖中刪除不成功視圖中更新數(shù)據(jù)影響到基表共八十五頁(yè)總結(jié)(zǒngjié)不能對(duì)視圖某些列進(jìn)行數(shù)據(jù)操作。不允許改變是某個(gè)計(jì)算的結(jié)果的列，例如包括計(jì)算值、內(nèi)建函數(shù)或行聚合函數(shù)的列

若修改影響了在視圖中未引用的列，也有可能引起錯(cuò)誤。例如往視圖中插入行的時(shí)候，其他(qítā)未被引用的列不允許空值且沒(méi)有默認(rèn)值共八十五頁(yè)6.3創(chuàng)建和管理(guǎnlǐ)存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的定義：將固定的操作使用控制流語(yǔ)句編寫(biānxiě)出來(lái)經(jīng)過(guò)預(yù)編譯存儲(chǔ)在數(shù)據(jù)庫(kù)中，在需要運(yùn)行時(shí)進(jìn)行調(diào)用，以實(shí)現(xiàn)某個(gè)特定的任務(wù)。優(yōu)點(diǎn)：①允許模塊化的程序設(shè)計(jì)②更快的執(zhí)行速度③有效降低網(wǎng)絡(luò)流量④較好的安全機(jī)制共八十五頁(yè)6.3創(chuàng)建(chuàngjiàn)和管理存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的分類：系統(tǒng)存儲(chǔ)過(guò)程：sp_rename，sp_help，……用戶自定義存儲(chǔ)過(guò)程：自己編寫

擴(kuò)展(kuòzhǎn)存儲(chǔ)過(guò)程：僅存在于MASTER數(shù)據(jù)庫(kù)中。用于外部程序編寫好放在SQL服務(wù)器上共八十五頁(yè)6.3.1創(chuàng)建存儲(chǔ)(cúnchǔ)過(guò)程1.使用T-SQL語(yǔ)言(yǔyán)創(chuàng)建存儲(chǔ)過(guò)程CREATEPROCEDUREprocedure_nameASsql_statement[…n]

共八十五頁(yè)使用(shǐyòng)T-SQL語(yǔ)言創(chuàng)建存儲(chǔ)過(guò)程簡(jiǎn)單應(yīng)用：createprocedure打印(dǎyìn)

asdeclare@varchar(10)set@var='你好'select@vargo共八十五頁(yè)注意(zhùyì)①不能將CREATEPROCEDURE語(yǔ)句與其他(qítā)SQL語(yǔ)句組合到單個(gè)批處理中。

共八十五頁(yè)注意(zhùyì)②創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫(kù)所有者，該所有者可以將此權(quán)限授予其他用戶(yònghù)。在某數(shù)據(jù)庫(kù)下有兩個(gè)用戶a和b在a用戶下創(chuàng)建了一個(gè)存儲(chǔ)過(guò)程名稱叫PROC現(xiàn)在a用戶下把使用此存儲(chǔ)過(guò)程的權(quán)利授予bgrantexecuteonctob共八十五頁(yè)注意(zhùyì)可對(duì)存儲(chǔ)過(guò)程進(jìn)行(jìnxíng)加密，這樣別人無(wú)論用什么方法也看不到你的腳本（程序）createprocedure打印withencryptionasdeclare@varchar(10)set@var='你好'select@vargo共八十五頁(yè)創(chuàng)建帶輸入(shūrù)參數(shù)的存儲(chǔ)過(guò)程輸入?yún)?shù)是指由調(diào)用程序(chéngxù)向存儲(chǔ)過(guò)程傳遞的參數(shù)。它們?cè)趧?chuàng)建存儲(chǔ)過(guò)程語(yǔ)句中被定義，而在執(zhí)行該存儲(chǔ)過(guò)程中給出相應(yīng)的變量值。語(yǔ)法:{@parameter

data_type}[=default]參數(shù)：@parameter：參數(shù)名，必須以@符號(hào)為前綴。data_type：參數(shù)的數(shù)據(jù)類型說(shuō)明。default：如果執(zhí)行存儲(chǔ)過(guò)程時(shí)未提供該參數(shù)值，則使用默認(rèn)值。共八十五頁(yè)創(chuàng)建帶輸入?yún)?shù)(cānshù)的存儲(chǔ)過(guò)程例：創(chuàng)建一個(gè)(yīɡè)指定姓名學(xué)生成績(jī)單的存儲(chǔ)過(guò)程。createprocedure成績(jī)存儲(chǔ)過(guò)程

@namechar(10)asselect*from學(xué)生成績(jī)視圖

where姓名=@name執(zhí)行存儲(chǔ)過(guò)程：EXEC成績(jī)存儲(chǔ)過(guò)程‘張三’或者：EXEC成績(jī)存儲(chǔ)過(guò)程@name=‘張三’共八十五頁(yè)創(chuàng)建帶輸出參數(shù)(cānshù)的存儲(chǔ)過(guò)程從存儲(chǔ)過(guò)程(guòchéng)中返回一個(gè)或多個(gè)值。語(yǔ)法：

@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值]OUTPUT@參數(shù)名：存儲(chǔ)過(guò)程的輸出參數(shù)名，必須以@符號(hào)為前綴。數(shù)據(jù)類型：該參數(shù)的數(shù)據(jù)類型說(shuō)明。OUTPUT：指明輸出參數(shù)。注意，輸出參數(shù)必須位于所有輸入?yún)?shù)說(shuō)明之后。共八十五頁(yè)創(chuàng)建(chuàngjiàn)帶輸出參數(shù)的存儲(chǔ)過(guò)程例：從下圖所示的視圖(shìtú)中查詢指定姓名同學(xué)的各科平均分。共八十五頁(yè)創(chuàng)建帶輸出參數(shù)(cānshù)的存儲(chǔ)過(guò)程CreateprocedurePROC_查詢成績(jī)(@NAMECHAR(10),@平均分intoutput)Asset@平均分=(selectavg(成績(jī))from學(xué)生(xuésheng)成績(jī)視圖where姓名=@name)執(zhí)行：共八十五頁(yè)思考題創(chuàng)建一個(gè)存儲(chǔ)過(guò)程(guòchéng)能向下面的表中添加一條記錄，并寫出執(zhí)行語(yǔ)句共八十五頁(yè)6.3.2查看(chákàn)修改刪除存儲(chǔ)過(guò)程1.查看存儲(chǔ)過(guò)程sp_helpsp_helptext2.修改(xiūgǎi)存儲(chǔ)過(guò)程ALTERPROCEDUREprocedure_nameas3.刪除存儲(chǔ)過(guò)程dropprocedureprocedure_name4.重命名存儲(chǔ)過(guò)程sp_renameold_name,new_name共八十五頁(yè)6.4創(chuàng)建(chuàngjiàn)和管理觸發(fā)器觸發(fā)器是什么?觸發(fā)器是一類特殊(tèshū)的存儲(chǔ)過(guò)程，它們之間的主要區(qū)別在于，觸發(fā)器不允許用戶調(diào)用，當(dāng)對(duì)表進(jìn)行插入、刪除、修改操作時(shí)由系統(tǒng)自動(dòng)調(diào)用并執(zhí)行，而存儲(chǔ)過(guò)程必須通過(guò)用戶的調(diào)用才能執(zhí)行。共八十五頁(yè)6.4.1觸發(fā)器作用(zuòyòng)觸發(fā)器設(shè)計(jì)的初衷是：實(shí)現(xiàn)數(shù)據(jù)庫(kù)中相關(guān)表的級(jí)聯(lián)修改；但是級(jí)聯(lián)引用完整性中的級(jí)聯(lián)更新選項(xiàng)很好的完成了這項(xiàng)任務(wù)。雖然設(shè)計(jì)的初衷被另外的方法很好很方便的實(shí)現(xiàn)，并不表示觸發(fā)器不具備(jùbèi)應(yīng)用的價(jià)值，相反在數(shù)據(jù)庫(kù)中觸發(fā)器應(yīng)用越來(lái)越廣泛。共八十五頁(yè)6.4.1觸發(fā)器作用(zuòyòng)其他作用：①檢查數(shù)據(jù)輸入的正確性：說(shuō)明：在數(shù)據(jù)庫(kù)中檢查數(shù)據(jù)輸入的正確性，可以使用CHECK約束，或者其他約束；但是CHECK約束有其本身的缺陷(quēxiàn)：不能參照其他表中的數(shù)據(jù)。共八十五頁(yè)說(shuō)明(shuōmíng)這里只能用觸發(fā)器不能用CHECK約束(yuēshù)，因?yàn)镃HECK約束(yuēshù)只能規(guī)定商品銷售數(shù)量在某個(gè)確定值之內(nèi)，而這里這里銷售數(shù)量的最大值是隨著庫(kù)存量變化而變化的此時(shí)要規(guī)定銷售數(shù)量不大于庫(kù)存數(shù)量怎么辦？商品庫(kù)存數(shù)量隨時(shí)在跟新共八十五頁(yè)6.4.1觸發(fā)器作用(zuòyòng)②檢查數(shù)據(jù)修改的正確性：當(dāng)對(duì)表中受觸發(fā)器保護(hù)的數(shù)據(jù)修改時(shí)，觸發(fā)器不但會(huì)自動(dòng)更新其他表與其相關(guān)的數(shù)據(jù)，還可以自動(dòng)檢查這些數(shù)據(jù)，只要有一個(gè)不符合條件，則修改數(shù)據(jù)失敗。如對(duì)于工資表中工資數(shù)據(jù)的更改規(guī)定不可以超過(guò)40%，使用觸發(fā)器可以檢查變更前后的數(shù)據(jù)如果幅度超過(guò)40%，那么回滾該操作(cāozuò)事務(wù)。共八十五頁(yè)6.4.2創(chuàng)建(chuàngjiàn)觸發(fā)器CREATETRIGGER觸發(fā)器名ON{表|視圖}{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][...n]|IF(COLUMNS_UPDATED(){位操作符}更新位標(biāo)志){比較(bǐjiào)運(yùn)算符}列的位標(biāo)志[,…n]}]SQL語(yǔ)句[…n]共八十五頁(yè)6.4.2創(chuàng)建(chuàngjiàn)觸發(fā)器觸發(fā)器的類型：

①after觸發(fā)器（后觸發(fā)）這種觸發(fā)器將在數(shù)據(jù)(shùjù)操作（insert,delete,update）之后才觸發(fā)，對(duì)變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤則取消或回滾數(shù)據(jù)更改。②insteadof觸發(fā)器（替代觸發(fā)）這種觸發(fā)器將在數(shù)據(jù)操作（insert,delete,update）之前觸發(fā)，轉(zhuǎn)而去執(zhí)行觸發(fā)器所定義的操作共八十五頁(yè)6.4.2創(chuàng)建(chuàngjiàn)觸發(fā)器③for觸發(fā)器（一般觸發(fā)器）這種觸發(fā)器就是after觸發(fā)器。如果在書寫程序的時(shí)候(shíhou)只有for參數(shù)那么系統(tǒng)默認(rèn)是后觸發(fā)器。共八十五頁(yè)注意(zhùyì)觸發(fā)器使用限制CREATETRIGGER必須是批處理中的第一條語(yǔ)句，并且只能應(yīng)用到一個(gè)表中。觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫(kù)中創(chuàng)建(chuàngjiàn)，不過(guò)觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫(kù)的外部對(duì)象。如果一個(gè)表的外鍵在DELETE/UPDATE操作上定義了級(jí)聯(lián)，則不能在該表上定義INSTEADOFDELETE/UPDATE觸發(fā)器。共八十五頁(yè)觸發(fā)器結(jié)構(gòu)(jiégòu)事件(shìjiàn)UPDATEINSERTDELETE條件AFTERINSTEADOF動(dòng)作各種Transact-SQL語(yǔ)句共八十五頁(yè)觸發(fā)器示意圖共八十五頁(yè)共八十五頁(yè)共八十五頁(yè)修改(xiūgǎi)觸發(fā)器名觸發(fā)器語(yǔ)句(yǔjù)共八十五頁(yè)AFTER觸發(fā)器例題(lìtí)此時(shí)要規(guī)定(guīdìng)銷售數(shù)量不大于庫(kù)存數(shù)量，怎么辦？商品庫(kù)存數(shù)量隨時(shí)在跟新共八十五頁(yè)解答(jiědá)共八十五頁(yè)解答(jiědá)CREATETRIGGER銷售觸發(fā)器ON[dbo].[銷售]AFTERUPDATEASdeclare@xsslchar(10),@kcslchar(10)select@xssl=銷售數(shù)量from銷售select@kcsl=商品(shāngpǐn)數(shù)量from庫(kù)存IF@xssl>=@kcslraiserror('銷售數(shù)量不能大于庫(kù)存數(shù)量',16,1)rollbacktransaction共八十五頁(yè)結(jié)果(jiēguǒ)驗(yàn)證圖共八十五頁(yè)INSTEADOF觸發(fā)器例題(lìtí)設(shè)置替代觸發(fā)器，不允許庫(kù)存表進(jìn)行修改和刪除(shānchú)操作。CREATETRIGGER庫(kù)存觸發(fā)器ON[dbo].[庫(kù)存]INSTEADOFUPDATE,DELETEASraiserror(‘不允許更新、刪除庫(kù)存表中記錄',16,10)共八十五頁(yè)結(jié)果(jiēguǒ)驗(yàn)證圖共八十五頁(yè)附加內(nèi)容(nèiróng)：觸發(fā)器使用的臨時(shí)表在創(chuàng)建觸發(fā)器的過(guò)程中，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)自動(dòng)創(chuàng)建deleted,inserted兩個(gè)臨時(shí)表供觸發(fā)器使用。

①deleted表用于存儲(chǔ)被update,delete語(yǔ)句影響的行的副本，當(dāng)系統(tǒng)對(duì)某個(gè)附加了觸發(fā)器的表執(zhí)delete語(yǔ)句的時(shí)候，被刪除的記錄(jìlù)從原表中消失，但會(huì)存儲(chǔ)在deleted表中。而執(zhí)行update語(yǔ)句的時(shí)候，更新后的記錄(jìlù)會(huì)替代原來(lái)舊的記錄(jìlù)，而舊的記錄(jìlù)會(huì)傳輸?shù)絛eleted表中存儲(chǔ)。共八十五頁(yè)附加內(nèi)容(nèiróng)：觸發(fā)器使用的臨時(shí)表②inserted表用來(lái)存儲(chǔ)insert,update命令所影響到行的副本。當(dāng)對(duì)一個(gè)帶有觸發(fā)器的表進(jìn)行insert操作(cāozuò)的時(shí)候，新的記錄不但添加進(jìn)表中而且會(huì)復(fù)制到inserted表中。而進(jìn)行update操作(cāozuò)的時(shí)候，表中原有記錄被更新，而且更新后的記錄會(huì)被存儲(chǔ)在inserted表中。共八十五頁(yè)怎樣(zěnyàng)看到inserted,deleted表？CREATETRIGGERtr1ON銷售(xiāoshòu)FORINSERT,UPDATE,DELETEASSelect*frominsertedSelect*fromdeletedGo共八十五頁(yè)臨時(shí)(línshí)表的應(yīng)用請(qǐng)使用觸發(fā)器實(shí)現(xiàn)(shíxiàn)庫(kù)存表中商品名稱修改，銷售表中上品名稱也跟著修改。修改前修改后共八十五頁(yè)臨時(shí)(línshí)表的應(yīng)用CREATETRIGGER修改姓名ON庫(kù)存afterupdateASDECLARE@spmc1varchar(10),@spmc2varchar(10)SELECT@spmc1=商品名稱FROMdeleted--從deleted表得到被刪除的原商品名稱SELECT@spmc2=商品名稱FROMinserted--從inserted表得到被更新(gēngxīn)的新商品名稱UPDATE庫(kù)存SET商品名稱=@spmc2WHERE商品名稱=@spmc1UPDATE銷售SET商品名稱=@spmc2WHERE商品名稱=@spmc1共八十五頁(yè)練習(xí)(liànxí)請(qǐng)使用觸發(fā)器實(shí)現(xiàn)(shíxiàn)庫(kù)存表和銷售表之間的級(jí)聯(lián)刪除操作。共八十五頁(yè)解答(jiědá)Createtrigger級(jí)聯(lián)刪除on庫(kù)存(kùcún)AfterdeleteAsDeclare@商品名稱char(10)Select@商品名稱=商品名稱fromdeletedDeletefrom銷售Where商品名稱=@商品名稱Go共八十五頁(yè)IFUPDATE(column)參數(shù)(cānshù)的使用

IFUPDATE(column)測(cè)試在指定的列上進(jìn)行(jìnxíng)的INSERT或UPDATE操作，不能用于DELETE操作。

例：創(chuàng)建觸