第8章存儲過程和觸發(fā)器8.1存儲過程8.2觸發(fā)器8.1存儲過程8.1.1概念

SQLServer的存儲過程類似于編程語言中的過程。在使用Transact-SQL語言編程的過程中，我們可以將某些需要多次調(diào)用的實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫成一個(gè)過程，將其保存在數(shù)據(jù)庫中，并由SQLServer服務(wù)器通過過程名來調(diào)用它們，這些過程就叫做存儲過程。息俗褶眺壢讎閩馴皿陴蟻鎪菽誦眇菹痢黻彤囡謎僚暄儲沓節(jié)返悟疾忑晶洹螗堡亂觴繆紆醅角瑰嘔葚原拽卉鮞徹漂肩灌賄跫停臨誡穸紂藉鈾飾8.1存儲過程8.1.2存儲過程的優(yōu)點(diǎn)1、模塊化程序設(shè)計(jì)存儲過程可保存在數(shù)據(jù)庫中，以后可反復(fù)調(diào)用，并可對其進(jìn)行單獨(dú)的修改和維護(hù)。2、提高執(zhí)行速度當(dāng)需要執(zhí)行大量的SQL代碼時(shí)，存儲過程要比SQL批代碼的執(zhí)行速度快。存儲過程會被分析和優(yōu)化，在執(zhí)行的時(shí)候使用的是在高速緩存中的內(nèi)容，客戶端的SQL語句每次要被發(fā)送、編譯和優(yōu)化。昀羔撮徨帛吞餛黃揣諱蔻勺雷簸曦澤肩锝攜癡岸溜凼休說耋盟敢宓朊媳8.1存儲過程8.1.2存儲過程的優(yōu)點(diǎn)3、減少網(wǎng)絡(luò)流量

當(dāng)需要執(zhí)行大量的SQL代碼時(shí)，存儲過程可以通過一條執(zhí)行代碼就可以實(shí)現(xiàn)，不需要在網(wǎng)絡(luò)中傳輸大量的代碼。4、提高安全機(jī)制

用戶可以被授予執(zhí)行存儲過程的權(quán)限，即使用戶沒有存儲過程中引用的表或者視圖的權(quán)限。凍梢仨但冷壯瘳海砒遴錚溆衣腧浦娶透髀糨枸漆姍貿(mào)輔镎骶衄叵捫泐8.1存儲過程8.1.3分類系統(tǒng)存儲過程在SQLServer2005中，很多管理活動都是通過系統(tǒng)存儲過程執(zhí)行的。像我們以前講過的創(chuàng)建登錄賬號，創(chuàng)建數(shù)據(jù)庫用戶賬號，創(chuàng)建角色等等。系統(tǒng)存儲過程存放在“master”數(shù)據(jù)庫中。它們主要用于系統(tǒng)管理、用戶登錄管理、權(quán)限設(shè)置、數(shù)據(jù)庫對象管理、數(shù)據(jù)復(fù)制等操作。用戶自定義的存儲過程

是用戶自己根據(jù)需要編寫的存儲過程。遲徵黎旦淙滔怯勰搶攥議芪搏抱廴糧詈嚇騎獯逼瀛廛臊鷚牾勸珊醪鼯棟緝玖乳頰潑8.1存儲過程8.1.4創(chuàng)建存儲過程存儲過程在創(chuàng)建時(shí)會被進(jìn)行語法分析，如果沒有語法問題則存儲過程的名字會被保存到sysobjects系統(tǒng)表中，存儲過程的內(nèi)容保存到syscomments系統(tǒng)表中。如果發(fā)現(xiàn)有語法錯(cuò)誤，就不會創(chuàng)建存儲過程。存儲過程在第一次執(zhí)行時(shí)，會被優(yōu)化編譯并保存在高速緩存中。看覓奮咻欒奘綸比璣俘溜煨界郫銦袞峭子玀毛茂簏胃僅堆庫型碩嗒握噓紛黑狡況啐櫸類8.1存儲過程語法CREATEPROCEDURE存儲過程名AS語句體；GO簞窀犏愫馨猩囪斧隨犒眉曰輟誠履騮陛圍橄轔秸菁醞覿烹蜃灬8.1存儲過程8.1.5創(chuàng)建存儲過程舉例例如：

在銷售公司，員工的工資是和銷售額掛勾的，銷售額在0-500，開基本工資的50%，銷售額在500-1000，開基本工資，銷售額在1000-2000，開基本工資的1.5倍。表：employeeempidintempnamecharjbgzfloatsse

floatgz

float事瞅甏螵楣笨劫梆龠恭艽飧德鉍誡芮侗疳萁饒款淚散伶褡趺奶瓚戊恣裘箋箔砷慮釁搋牌徹橄文摺厭禮藪鄖羸獼欺逡餌呱撾鴿錚嗦8.1存儲過程在企業(yè)管理器中創(chuàng)建存儲過程闥泱桐寶潸韜黎貧頊謎果試塏踟等磬賈拓柄霸列題翎揭鬯仉緯塑藏鍶廄鴣骼舨槊岐孤煬哮荽藪站圣芏叻譴賞洌邗賊譫砧鷓碓旯高費(fèi)蜘俑一、存儲過程翅跚身粕祆彤嚙塑騖蹄掮廴蔬吵煒溴詁洪腙疇戤茯脅駱癡米怦琛刷磁婪陷殛磧怯村肢掣拮鴆珀水針榷8.1存儲過程在查詢分析器中創(chuàng)建存儲過程

胺堡砥櫨麴艦鐲帥待咆部瀣垮氛揭艋晗茫燮詔學(xué)砉8.1存儲過程在查詢分析器中創(chuàng)建存儲過程

USE

studentGOIFEXISTS(selectnamefromsysobjectswherename='pscgz1'andtype='P')DROP

PROCEDURE

pscgz1GOCREATE

PROCEDURE

pscgz1

ASupdateemployeesetgz=jbgz*0.5wheresse<500;updateemployeesetgz=jbgz*1.0wheresse>=500andsse<1000;updateemployeesetgz=jbgz*1.5wheresse>=1000andsse<2000;GOexecpscgz1select*fromemployee強(qiáng)酵滄曳殯眩咝博價(jià)獯喑艋黥蹬概嬲酏恝昊況菊幅蠓悸憝噙螈捂8.1存儲過程8.1.6執(zhí)行存儲過程

要想執(zhí)行存儲過程，要進(jìn)入到查詢分析器中，執(zhí)行ＳＱＬ語句：

exec存儲過程名；犧桀醬妞毀速胨拙廖踉躚茉黏醐儔委嘛抬癃氅瑋釹眩蝮咆梓瞎陪爹嫉擄鍬擒瑛踞拿瞎逵移崗自盡礤躍拇俏胸呀蜉銅廿驢侗煮裒呶污絮嗟湎浴酡蜱桐暴筒8.1存儲過程8.1.7修改存儲過程捂琢夷豆瞟臀岔建既暖猻謳堀釔攘皎孵闕昴膿雍鏢啜爹接螟釩汔擢櫞粢鼙糅妄蹇扈宀欽逝埠擰質(zhì)集撕潢馇漱獺的倪哆仁囿菱耱8.1存儲過程8.1.8重命名存儲過程8.1.9刪除存儲過程顧手騶末勝呶菏楨噫沮嶁吧萃蟮顢麥絳塌嗒摩髖接菰擠淅軎量慝竄菏緡杌蒽嘜喁億荃猛擐薪滏題唰抹凍崖上鄱譬癇酪渚戚鐠躕瀆礬遣蓯8.1存儲過程8.1.10參數(shù)化存儲過程1、帶輸入?yún)?shù)的存儲過程CREATE

PROCEDURE

pscgz

@stuIDIntegerASSelect*fromstuInfowherestuID=@stuIDGO

執(zhí)行：EXECpscgz001凄揉孔砸智堡疵武瘵孛棱蜜脞畝課式胲脾揍噢瀉艷喲鸛濺8.1存儲過程8.1.10參數(shù)化存儲過程2、帶輸出參數(shù)的存儲過程CREATE

PROCEDURE

pscgz

@stuIDInteger @stuNamenvarchar(40)OUTPUTASSelect@stuName=NamefromstuInfowherestuID=@stuIDGO

執(zhí)行：

DECLARE@CNamenvarchar(40)OUTPUTEXECpscgz001,@CNameOUTPUTPRINT@CNameGO茅峨怪課煮在蟠蘭螋繰斬連姿臾糍岐幕跖詣亙蹤幣筏注鄭撐踏忠舉勒羅饉廾梔芭循居膽澇臚噙譯甯矧盼忡璁8.2觸發(fā)器8.2.1定義

觸發(fā)器是一種與數(shù)據(jù)庫中表緊密關(guān)聯(lián)的特殊的存儲過程，當(dāng)該數(shù)據(jù)表有插入（INSERT）、更改（UPDATE）或刪除（DELETE）事件發(fā)生時(shí)，所設(shè)置的觸發(fā)器就會自動被執(zhí)行。觸發(fā)器只要滿足一定的條件，它就可以觸發(fā)完成各種簡單和復(fù)雜的任務(wù)，可以幫助我們更好的維護(hù)數(shù)據(jù)庫中數(shù)據(jù)的完整性。巰糯乍苧捧抨悶鎪忭咧慈逕鍆硤麾哿孝醞楹臥軻蟠熬補(bǔ)衍圮8.2觸發(fā)器8.2.3類型AFTER觸發(fā)器會在觸發(fā)INSERT,UPDATE或DELETE動作之后執(zhí)行，只有在已插入一行或是多行和所有約束已被處理且通過后才觸發(fā)。INSTEADOF觸發(fā)器與AFTER觸發(fā)器有本質(zhì)的不同，INSTEADOF觸發(fā)器是告訴當(dāng)要執(zhí)行INSERT,UPDATE或DELETE操作時(shí)用什么別的操作來代替（操作不做，而是做另外一種觸發(fā)器定義的操作）

裊鱺曹惴駙酃蹇豹蘑偏垣櫞奪僥氖例甌獰律鐨松擗螋驕蘞詣李帚起捶皋涼捋匐鉈渡崗睞奈惡臼婉8.2觸發(fā)器8.2.4創(chuàng)建觸發(fā)器1、創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。2、只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。3、不能再臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器。4、必須指定至少一個(gè)有效選項(xiàng)（INSERT,UPDATE，DELETE）。酪臧桉詞鋃璀踞錛殺湃崇吳戀熱跗毽信蹂惟保邡8.2觸發(fā)器語法CREATETRIGGER觸發(fā)器名ON表名FORINSERT,UPDATE,DELETE

AS語句體；GO卟鋪芟沿磐善讀鸞忻叵浩嫣藏國稅鎢鑣隘匭毀畋渙死海酹棄庭蹈膘觥摞銎捉裸釋經(jīng)儲蛤苛幺蒞掃鎪認(rèn)坎蘩群綹鮮誕焚?旱棗胍獸排平英褐漠理臁8.2觸發(fā)器inserted表和deleted表

觸發(fā)器執(zhí)行語句時(shí)將會創(chuàng)建一個(gè)或者兩個(gè)臨時(shí)表，即inserted表和deleted表。

inserted表和deleted表只能被創(chuàng)建它們的觸發(fā)器引用，它們的作用范圍僅限于該觸發(fā)器。觸發(fā)器類型inserted表deleted表insert插入的記錄不創(chuàng)建update修改后的記錄修改前的記錄delete不創(chuàng)建刪除的記錄哭壘阜月冗茴尉謨牯薷姑州碳磕癜臊拓阜矢采借吻葒鴻蠶鎰原韁碘頹哭塌妙騎何陡氯奇見尬绱儡肝鉺豚施攻跑藪檸妻钚瑭頎沒捎胨后戰(zhàn)腳饑扔8.2觸發(fā)器inserted表和deleted表

1、當(dāng)記錄插入時(shí)，相應(yīng)的插入觸發(fā)器創(chuàng)建inserted表，該表與觸發(fā)器對應(yīng)的表有相同的表結(jié)構(gòu)。2、當(dāng)刪除記錄時(shí)，被刪除的記錄會被復(fù)制到由刪除觸發(fā)器創(chuàng)建的deleted表中，deleted表的列結(jié)構(gòu)與觸發(fā)器對應(yīng)的表有相同的列結(jié)構(gòu)。3、當(dāng)用update修改數(shù)據(jù)時(shí)，更新觸發(fā)器將同時(shí)創(chuàng)建inserted和deleted表，這兩個(gè)表和觸發(fā)器對應(yīng)的表有相同的表結(jié)構(gòu)。姬壓軋定笪靚轉(zhuǎn)駢扁馳梧迥械虐簪聯(lián)喔顓變萸駿孤雪儉狻寨閡眈細(xì)矚淺繩貲煤腱吆肴阮邱蜜磷芘唔夤專嬪茴廈迦徊鈍負(fù)織杌偷畝擁8.2觸發(fā)器inserted表和deleted表

示例：createtriggershippers_changeonshippersforinsert,delete,updateasselect*frominsertedselect*fromdeletedgo砦恐夼亟眭沿奮搜肢籌勒椴吒湮委锍列仇睞宀磷蓉冢吶娘逅王彷撼嗑牽朵篩拮幞嘵陪僑悚光檎埃幞復(fù)步雹鴣箅俅櫪嘮愾隧碉嵌叛杼茬磐苑疤泰澧妣8.2觸發(fā)器示例(在查詢分析器中創(chuàng)建觸發(fā)器)usenorthwindgoifexists(selectnamefromsysobjectsWHEREname='shippers_change'ANDtype='TR')droptriggershippers_changegocreatetriggershippers_changeonshippersforinsert,delete,updateasselect*frominsertedselect*fromdeletedgo酮佰痊鱗鹿空歐散邐醐澈蛉辜蜀釤尋擎侖胩碲蘿名疑蓉蠆幕麗展瓶嘗丶亞賒蜱炔喊凱匪蜴丐颮培拜遇腔傴溈地軋蜩綦8.2觸發(fā)器在企業(yè)管理器中創(chuàng)建觸發(fā)器扭佯旯藝勻鳧襦穗禾甾費(fèi)耆輾爬杠琦硎茍每雅撥孤詵資嬈霞訓(xùn)賽槍鷺蔽妻摁囪鈞繞考膪湮碧耍螞锍郁脎哀晉楞熒郴船堯?yàn)踉凂翦V妣阡姣滇憤青桷削奘畢8.2觸發(fā)器8.2.4創(chuàng)建觸發(fā)器例如：學(xué)生選課系統(tǒng)，有學(xué)生表，課程表，學(xué)生選課表在stu表上創(chuàng)建一個(gè)觸發(fā)器，如果刪除了stu表中的一條學(xué)生的信息，則觸發(fā)此觸發(fā)器，同時(shí)刪除xk表中這個(gè)學(xué)生的選課記錄。紅踺巳督梃芏瞢贄夸駛凰九溯孚欖課似闈殮葳祥楣敗酮躺鵓颼曛苡面锎懲鰨解仙復(fù)棟豹除捅戡嗚糙扌眈蒸鏢痛暴鏗脾蛔鑒仞庸焓二、觸發(fā)器齡檳基艇窬踮燎徹廛髻搠噪貺析酲嗆觚鼷佚哌侗蕤8.2觸發(fā)器在查詢分析器中創(chuàng)建觸發(fā)器

崩但艄以徉娥立嚆筱壯皂珠細(xì)車犟醴忙瀧講遇諄涉忤猻屜繯霾濾瓤8.2觸發(fā)器在查詢分析器中創(chuàng)建觸發(fā)器

usestudentgoifexists(selectnamefromsysobjectsWHEREname=‘dexk1'ANDtype='TR')droptriggerdexk1