大型數(shù)據(jù)庫(kù)應(yīng)用與維護(hù)以SQLServer2012為例上次內(nèi)容回顧回顧7.1存儲(chǔ)過(guò)程7.1.1存儲(chǔ)過(guò)程的定義7.1.2存儲(chǔ)過(guò)程的優(yōu)點(diǎn)7.1.3存儲(chǔ)過(guò)程的分類(lèi)7.1.4常用的系統(tǒng)存儲(chǔ)過(guò)程7.1.5創(chuàng)建與調(diào)用存儲(chǔ)過(guò)程7.1.6管理存儲(chǔ)過(guò)程7.1.7存儲(chǔ)過(guò)程提高存儲(chǔ)過(guò)程內(nèi)容提問(wèn)4、帶輸出參數(shù)的存儲(chǔ)過(guò)程，在參數(shù)類(lèi)型后面要加哪個(gè)關(guān)鍵字？（1分）5、常見(jiàn)的存儲(chǔ)過(guò)程管理有哪些？（1分）3、常見(jiàn)的用戶自定義存儲(chǔ)過(guò)程有哪幾類(lèi)？（1分）回顧1、存儲(chǔ)過(guò)程是什么？（1分）2、使用存儲(chǔ)過(guò)程有什么優(yōu)勢(shì)？（1分）6、存儲(chǔ)過(guò)程提高中我們講了哪些知識(shí)點(diǎn)？（1分）上節(jié)內(nèi)容總結(jié)回顧存儲(chǔ)過(guò)程（StoredProcedure）：是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL語(yǔ)句集。1、創(chuàng)建存儲(chǔ)過(guò)程

CreateProc[edure]<存儲(chǔ)過(guò)程名>[<@參數(shù)名1>…]

AS<表達(dá)式>2、執(zhí)行存儲(chǔ)過(guò)程

Exec[ute]<存儲(chǔ)過(guò)程名>[<@參數(shù)名1>…]3、刪除存儲(chǔ)過(guò)程

DROPPROCEDURE<存儲(chǔ)過(guò)程名>預(yù)編譯指定名稱(chēng)、參數(shù)可選創(chuàng)建存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程完成任務(wù)刪除存儲(chǔ)過(guò)程不用了新知識(shí)導(dǎo)入導(dǎo)入問(wèn)題：“課程改名的問(wèn)題”？創(chuàng)建存儲(chǔ)過(guò)程進(jìn)行Update操作提示不能更新是否該課程是否被使用手動(dòng)執(zhí)行存儲(chǔ)過(guò)程能否自動(dòng)執(zhí)行？？《企業(yè)級(jí)數(shù)據(jù)庫(kù)的安裝、配置和管理》《SQLServer數(shù)據(jù)庫(kù)應(yīng)用與維護(hù)》新知識(shí)導(dǎo)入導(dǎo)入小故事：“青蛙與開(kāi)水”or“火箭發(fā)射”提問(wèn)：兩個(gè)小故事有什么共同特點(diǎn)？“加熱”--》青蛙起跳“點(diǎn)火”--》火箭發(fā)射本講主要內(nèi)容第20講觸發(fā)器的定義和類(lèi)型觸發(fā)器的設(shè)計(jì)與應(yīng)用

知識(shí)目標(biāo)能力目標(biāo)重點(diǎn)：觸發(fā)器的應(yīng)用

難點(diǎn)：觸發(fā)器的設(shè)計(jì)

7.2觸發(fā)器第20講

觸發(fā)器17.2觸發(fā)器7.2.1觸發(fā)器的定義與類(lèi)型7.2.2INSERTED表與DELETED表7.2.3創(chuàng)建觸發(fā)器7.2.4觸發(fā)器的設(shè)計(jì)與應(yīng)用7.2.1觸發(fā)器的定義與類(lèi)型7.2.1

觸發(fā)器的定義與類(lèi)型1.觸發(fā)器的定義觸發(fā)器（trigger）是SQLserver提供給程序員和數(shù)據(jù)分析員來(lái)保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲(chǔ)過(guò)程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動(dòng)，而是由事件來(lái)觸發(fā)，比如當(dāng)對(duì)一個(gè)表進(jìn)行操作（insert，delete，update）時(shí)就會(huì)激活它執(zhí)行。想一想：上學(xué)期講過(guò)的數(shù)據(jù)完整性有哪些？7.2.1

觸發(fā)器的定義與類(lèi)型2.觸發(fā)器的類(lèi)型SQLServer包括三種常規(guī)類(lèi)型的觸發(fā)器：DML觸發(fā)器、DDL觸發(fā)器和登錄觸發(fā)器。動(dòng)一動(dòng)：請(qǐng)查詢DML、DDL和登錄觸發(fā)器三者分別指的是？有什么區(qū)別？DML觸發(fā)器Insert觸發(fā)器Delete觸發(fā)器Update觸發(fā)器

7.2.1

觸發(fā)器的定義與類(lèi)型3.觸發(fā)器的特點(diǎn)不能直接調(diào)用，只有在對(duì)觸發(fā)器表的數(shù)據(jù)進(jìn)行更改時(shí)，才自動(dòng)執(zhí)行；不能傳遞和接受參數(shù)；觸發(fā)器可以實(shí)施更為復(fù)雜的數(shù)據(jù)完整性約束。7.2.2INSERTED表與DELETED表7.2.2INSERTED表與DELETED表觸發(fā)器觸發(fā)時(shí)：系統(tǒng)自動(dòng)在內(nèi)存中創(chuàng)建deleted表或inserted；只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除。7.2.2INSERTED表與DELETED表1.inserted表臨時(shí)保存了插入或更新后的記錄行；可以從inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求；如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾插入操作。2.deleted表臨時(shí)保存了刪除或更新前的記錄行；可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求；如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾刪除操作。7.2.2INSERTED表與DELETED表修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄更新(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和deleted表存放的信息7.2.3創(chuàng)建觸發(fā)器7.2.3創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的語(yǔ)法：CREATETRIGGER

trigger_nameONtable_name[WITHENCRYPTION]FOR[DELETE,INSERT,UPDATE]AS

T-SQL語(yǔ)句GOWITHENCRYPTION表示加密觸發(fā)器定義的SQL文本DELETE,INSERT,UPDATE指定觸發(fā)器的類(lèi)型7.2.3INSERT觸發(fā)器的工作原理：transInfocardIDtransTypetransMoney1001000210010002存入300存入500insertedcardIDtransTypetransMoney10010001

支取200transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200插入記錄行觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作創(chuàng)建觸發(fā)器deletedcardIDtransTypetransMoney10010001支取

2007.2.3DELETE觸發(fā)器的工作原理：transInfocardIDtransTypetransMoney1001000210010002存入300存入500刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作創(chuàng)建觸發(fā)器7.2.3UPDATE觸發(fā)器的工作原理：Deleted（更新前的數(shù)據(jù)）customerNamecardIDcurrentMoney李四100000021bankcustomerName

cardID

currentMoney張三

100100011000

李四100000021刪除記錄行向deleted表中插入被刪除的副本檢查deleted和inserted表中的數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作李四

1000000220001向inserted表中插入被添加的副本Inserted（更新后的數(shù)據(jù)）customerNamecardIDcurrentMoney李四1000000220001插入記錄行創(chuàng)建觸發(fā)器7.2.3DML中三類(lèi)觸發(fā)器的工作原理小結(jié)創(chuàng)建觸發(fā)器INSERT觸發(fā)器工作原理DELETE觸發(fā)器工作原理UPDATE觸發(fā)器工作原理執(zhí)行insert插入語(yǔ)句，在表中插入數(shù)據(jù)行觸發(fā)insert觸發(fā)器，向inserted表中插入新行的備份（副本）觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作執(zhí)行delete刪除語(yǔ)句，刪除表中的數(shù)據(jù)行觸發(fā)delete觸發(fā)器，向deleted表中插入被刪除的副本觸發(fā)器檢查deleted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作執(zhí)行update更新語(yǔ)句觸發(fā)update觸發(fā)器，刪除原有的數(shù)據(jù)，將數(shù)據(jù)備份到deleted表中再插入新行，將數(shù)據(jù)備份到inserted表中。檢查deleted和inserted表中的數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作7.2.4觸發(fā)器的設(shè)計(jì)與應(yīng)用7.2.4觸發(fā)器的設(shè)計(jì)與應(yīng)用問(wèn)題：“課程改名的問(wèn)題”？“拋問(wèn)題，留懸念”“解問(wèn)題，去懸念”

創(chuàng)建存儲(chǔ)過(guò)程進(jìn)行Update操作提示不能更新是否該課程是否被使用手動(dòng)執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建UPDATE觸發(fā)器回滾更新操作是該課程是否被使用當(dāng)進(jìn)行UPDATE操作的時(shí)候，會(huì)自動(dòng)調(diào)用UPDATE觸發(fā)器7.2.4觸發(fā)器的設(shè)計(jì)與應(yīng)用例：設(shè)計(jì)一個(gè)觸發(fā)器（

SC_update

），當(dāng)向課程表中修改課程名稱(chēng)時(shí)觸發(fā)該觸發(fā)器，如果該課程正在被選修，則課程名不能進(jìn)行修改。CREATETRIGGERSC_updateON課程表FORUPDATEAS

beginIF(SELECTCOUNT(*)FROMinserted,選課表

WHERE選課表.課程號(hào)=inserted.課程號(hào))>0

ROLLBACKTRANSACTION

end--指定所監(jiān)控的表--指定觸發(fā)事件類(lèi)型--執(zhí)行代碼if

update(課程名)begin

if

exists(select

*

from

inserted

inner

join

選課表

on

inserted.課程號(hào)=選課表.課程號(hào))

rollback

transactionend7.2.4觸發(fā)器的設(shè)計(jì)與應(yīng)用課程號(hào)課程名教師周課時(shí)數(shù)備注1軟件工程吳老師42ASP程序設(shè)計(jì)黃老師43COM技術(shù)黃老師24Delphi涂老師6課程表選課表學(xué)號(hào)課程號(hào)成績(jī)1031231199103123126010312313501031231488在UPDATE觸發(fā)器執(zhí)行時(shí)，產(chǎn)生兩個(gè)特殊的臨時(shí)表，inserted表和deleted表，這兩個(gè)表在結(jié)構(gòu)上與觸發(fā)器所關(guān)聯(lián)的表一樣，它們可用于觸發(fā)器的條件測(cè)試。7.2.4觸發(fā)器的設(shè)計(jì)與應(yīng)用CREATETRIGGERSC_updateON課程表FORUPDATEAS

begin