Sqlserver技術(shù)知識點之二十

1、Sqlserver技術(shù)知識點之二十系統(tǒng)存儲過程就是系統(tǒng)創(chuàng)建的存儲過程，目的在于能夠方便地從系統(tǒng)表中查詢信息或完成與更新數(shù)據(jù)庫表相關(guān)的管理任務(wù)或其它的系統(tǒng)管理任務(wù)。系統(tǒng)過程以“sp_"_為開頭，在Master數(shù)據(jù)庫中創(chuàng)建并保存在該數(shù)據(jù)庫中，為數(shù)據(jù)庫管理者所有。一些系統(tǒng)過程只能由系統(tǒng)管理員使用，而有些系統(tǒng)過程通過授權(quán)可以被其它用戶所使用。系統(tǒng)存儲過程主要包括以下幾類：（這里主要給出每類系統(tǒng)過程中經(jīng)常使用的系統(tǒng)過程）日強春嚙力sp_coLiJLiinssp_fl£eyisp_pkeyssp_Sen.er_info復(fù)制類存得過程sp_sproc_clunins<p_«

2、;>taTntLCEi5p_stored_procedure,sp_tnble_priik乎ssp_oble$sp_adddi stpub lisher sp_adddistributor sp_help_a genrjjiofilesp_he lp_pub lie ation_acces s s.p_help article$p_addpullsubscription_agent $p_addsubscription sp_3ddsubscnber_schedule sp_helppubli cation sp_diopsubsciibers.p_changedistpublisher

3、 sp_cliangedistributiondb sp_lmk_publicati.on sp_droparticle sp_dropdistributionclb sp_droppub he ation$p_droprole sp_dropf o lemember ，p_dropSurver sp_dropsrxTolemeniber ，p_ dropuser s.p_grantdbaccess sp_grantlogin sp he Ip db fix edro le s.p_help group sp_hdp link edsn'loginsp_addarticlesp_add

4、distributiondbsp_addpublicationsp_addpublication_snapshotsp_addpubhsher70sp-addpullsubscnptionsp_helpdistpublishersp_addsubscribersp_helpdistributiondbsp_helpdistributorsp_helppulhubscnptionspjielprephcatioiidboptionsp_helpsubscriptionsp_changedistributer_passwoid$p_refreshsubscnpiions§p_dropdi

5、stpubhahei$p_dropdis.tnbutoisp_droppullsubscription安全管理類存儲過程sp_addaliassp_addapprolesp_addgroupsp_addiinkedsnioginsp_addloginsp_a(ldremoteloein$p_addrolespaddrolemembersp_addSurversp_a(idsnTolemeinbersp_addusersp-approlepasswordsp_change_users_login$p_changedbownersp-ehangegroupsp_changeobjeciowners

6、pjielpsmolesp_helpsnToleniembersp-defaultlauguagesp_denyloginsp_dropaliassp_helploginssp_helpntgroup$pJielprenioteloginsp-helprole$phelprolemembersp_dbfixedrolepeniiiss>ionsp_defaultdbsp_dropremoteloginsp_helpusersp_pawwords.p_remoteoptionmp_r ev okel-o pin sp_droploginspindexesfsp_1 mke d S er/e

7、rs sp-pninnrykeybsp_dropgroupsp_drcplinkedsn'login.令布式查詢存儲過隹sp_addlitikedS<rveisp;idcllinked.sr/10£iusp_cata,10Esp_fbreienkeys在以后的章節(jié)中，我們會使用或講解這些存儲i"上在上面幾節(jié)我們介紹了一般意義的存儲過程，即用戶自定義的存儲過程和系統(tǒng)存儲過程。本節(jié)將介紹一種特殊的存儲過程，即觸發(fā)器。在余下各節(jié)中我們將對觸發(fā)器的概念、作用以及對其的使用方法作詳盡介紹，使讀者了解如何定義觸發(fā)器，創(chuàng)建和使用各種不同復(fù)雜程度的觸發(fā)器。12.5.1 觸發(fā)器

8、的概念及作用觸發(fā)器是一種特殊類型的存儲過程，它不同于我們前面介紹過的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名字而被直接調(diào)用。當(dāng)對某一表進行諸如UPDATEINSERT、DELETE這些操作時，SQLServer就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。除此之外，觸發(fā)器還有其它許多不同的功能：(1) 強化約束(Enforcerestriction)觸發(fā)器能夠?qū)崿F(xiàn)比CHECKS句更為復(fù)雜的約束。(2) 跟蹤變化Auditi

9、ngchanges觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許數(shù)據(jù)庫中未經(jīng)許可的指定更新和變化。(3)級聯(lián)運行(Cascadedoperation)。觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動地級聯(lián)影響整個數(shù)據(jù)庫的各項內(nèi)容。例如，某個表上的觸發(fā)器中包含有對另外一個表的數(shù)據(jù)操作(如刪除，更新，插入)而該操作又導(dǎo)致該表上觸發(fā)器被觸發(fā)。(4)存儲過程的調(diào)用(Storedprocedureinvocation)。為了響應(yīng)數(shù)據(jù)庫更新觸，發(fā)器可以調(diào)用一個或多個存儲過程，甚至可以通過外部過程的調(diào)用而在DBMS數(shù)據(jù)庫管理系統(tǒng))本身之外進行操作。由此可見，觸發(fā)器可以解決高級形式的業(yè)務(wù)規(guī)則或復(fù)雜行為限制以及實現(xiàn)定制記錄等一

10、些方面的問題。例如，觸發(fā)器能夠找出某一表在數(shù)據(jù)修改前后狀態(tài)發(fā)生的差異，并根據(jù)這種差異執(zhí)行一定的處理。此外一個表的同一類型(INSERT、UPDATEDELETE)的多個觸發(fā)器能夠?qū)ν环N數(shù)據(jù)操作采取多種不同的處理?？傮w而言，觸發(fā)器性能通常比較低。當(dāng)運行觸發(fā)器時，系統(tǒng)處理的大部分時間花費在參照其它表的這一處理上，因為這些表既不在內(nèi)存中也不在數(shù)據(jù)庫設(shè)備上，而刪除表和插入表總是位于內(nèi)存中。可見觸發(fā)器所參照的其它表的位置決定了操作要花費的時間長短。12.5.2觸發(fā)器的種類SQLServer2000支持兩種類型的觸發(fā)器：AFTER觸發(fā)器和INSTEADOF觸發(fā)器。其中AFTER觸發(fā)器即為SQLServe

11、r2000版本以前所介紹的觸發(fā)器。該類型觸發(fā)器要求只有執(zhí)行某一操作(INSERTUPDATEDELET)E之后，觸發(fā)器才被觸發(fā)，且只能在表上定義?？梢詾獒槍Ρ淼耐徊僮鞫x多個觸發(fā)器。對于AFTER觸發(fā)器，可以定義哪一個觸發(fā)器被最先觸發(fā)，哪一個被最后觸發(fā)，通常使用系統(tǒng)過程sp_settriggerorder來完成此任務(wù)。INSTEADOF觸發(fā)器表示并不執(zhí)行其所定義的操作(INSERTUPDATEDELETE),而僅是執(zhí)行觸發(fā)器本身。既可在表上定義INSTEADOF觸發(fā)器，也可以在視圖上定義INSTEADOF觸發(fā)器，但對同一操作只能定義一個INSTEADOF觸發(fā)器。上面介紹了有關(guān)觸發(fā)器的概念、作

12、用和一些基本問題，下面我們將分別介紹在MSSQLServer中如何用SQLServer管理工具EnterpriseManager和Transaction_SQL來創(chuàng)建觸發(fā)器。在創(chuàng)建觸發(fā)器以前必須考慮到以下幾個方面：?CREATETRIGGE嚙句必須是批處理的第一個語句；?表的所有者具有創(chuàng)建觸發(fā)器的缺省權(quán)限，表的所有者不能把該權(quán)限傳給其它用戶；?觸發(fā)器是數(shù)據(jù)庫對象，所以其命名必須符合命名規(guī)則；?盡管在觸發(fā)器的SQL語句中可以參照其它數(shù)據(jù)庫中的對象，但是，觸發(fā)器只能創(chuàng)建在當(dāng)前數(shù)據(jù)庫中；?雖然觸發(fā)器可以參照視圖或臨時表，但不能在視圖或臨時表上創(chuàng)建觸發(fā)器，而只能在基表或在創(chuàng)建視圖的表上創(chuàng)建觸發(fā)器；?一

13、個觸發(fā)器只能對應(yīng)一個表，這是由觸發(fā)器的機制決定的；?盡管TRUNCATTABLE語句如同沒有WHER耿句的DELETE語句，但是由于TRUNCATTABLE語句沒有被記入日志，所以該語句不能觸發(fā)DELETE型觸發(fā)器；?WRITETEXT句不能觸發(fā)INSERT或UPDATE的觸發(fā)器。當(dāng)創(chuàng)建一個觸發(fā)器時，必須指定觸發(fā)器的名字，在哪一個表上定義觸發(fā)器，激活觸發(fā)器的修改語句，如INSERTDELETEUPDATE當(dāng)然兩個或三個不同的修改語句也可以都觸發(fā)同一個觸發(fā)器，如INSERT和UPDATE語句都能激活同一個觸發(fā)器。12.6.1 用管理工具EnterpriseManger創(chuàng)建觸發(fā)器其操作步驟如下：1

14、 .啟動EnterpriseManger,登錄到指定的服務(wù)器上。2 .展開數(shù)據(jù)庫，然后展開要在其上創(chuàng)建觸發(fā)器的表所在的數(shù)據(jù)庫，然后單擊該表。3 .右擊鼠標(biāo)，在彈出菜單中選擇ALLTasks,然后單擊ManageTriggers4 .在名字框中選擇new,在文本框中輸入觸發(fā)器文本，如圖12-5所示。5 .單擊CheckSyntax檢查語句是否正確。6 .單擊Apply,在Name下拉列表中會有新創(chuàng)建的觸發(fā)器名字。7 .單擊OK關(guān)閉窗口創(chuàng)建成功。吐“ II HfrE= 1 叼.語句不t和,seiaTiR#1ymirjknnrm.lrir-ifpmi,-Etad-tanonlJn"：,Up

15、dateiWtbtE_irf-i&fe_H稿*HMruFtmafireeldti«&5dIrT1nrI 1.ZU22Q12-5*SQLit旬*%12.6.2 用CREATETRIGGER命令創(chuàng)建觸發(fā)器其語法規(guī)則如卜、CREATETRIGGERtriggw'uamEOXEbleJvuwWITHENCRYPTION(TOK|AFTEKZTEADOFDELETEJfINSERTtUPDATE)|WI1HAPPENDNOTFOKREPLICATIONASqlstat&mciktu(TOR|AFTERINSTE.WOF>INSERTJVPDATEWITHA

16、PPEXDNOTFORREPLICATION|ASIFUPDATE(cohimn)11AND|ORLPBATE(column>【it|IF(COLUMNS_LPDAIED<)tirwi_Qptiator)updated_bitinas.k)ompaiL5on_operafoijcoliiinubiTmask31sql_£tatem»nl.各參數(shù)的說明如下：?trigger_name是用戶要創(chuàng)建的觸發(fā)器的名字觸發(fā)器的名字，必須符合MSSQLServer的命名規(guī)則，且其名字在當(dāng)前數(shù)據(jù)庫中必須是惟一的。?Table是與用戶創(chuàng)建的觸發(fā)器相關(guān)聯(lián)的表的名字，并且該表已經(jīng)存在

17、。?WITHENCRYPTION表示對包含有CREATETRIGGER：本的syscomments表進行加密。?AFTER表示只有在執(zhí)行了指定的操作（INSERTDELETEUPDATE5之后觸發(fā)器才被激活，執(zhí)行觸發(fā)器中的SQL語句。若使用關(guān)鍵字FOR則表示為AFTER觸發(fā)器，且該類型觸發(fā)器僅能在表上創(chuàng)建。?INSTEADOF請參看“12.8INSTEADOF觸發(fā)器”?DELETE,INSERT,UPDATE關(guān)鍵字用來指明哪種數(shù)據(jù)操作將激活觸發(fā)器。至少要指明一個選項，在觸發(fā)器的定義中三者的順序不受限制，且各選項要用逗號隔開。?WITHAPPEND表明增加另外一個已存在某一類型觸發(fā)器。只有在兼容

18、性水平(指某一數(shù)據(jù)庫行為與以前版本的MSSQLServer兼容程度)不大于65時才使用該選項。NOTFORREPLICATION表明當(dāng)復(fù)制處理修改與觸發(fā)器相關(guān)聯(lián)的表時，觸發(fā)器不能被執(zhí)行。AS是觸發(fā)器將要執(zhí)行的動作。Sql_statement是包含在觸發(fā)器中的條件語句或處理語句。觸發(fā)器的條件語句定義了另外的標(biāo)準(zhǔn)來決定將被執(zhí)行的INSERTDELETE、UPDATE語句是否激活觸發(fā)器。IFUPDATE(column)用來測定對某一確定列是插入操作還是更新操作，但不與刪除操作用在一起。IF(COLUMNSJUPDATED)僅在INSERT和UPDATE類型的觸發(fā)器中使用，用其來檢查所涉及的列是被更新

19、還是被插入。Bitwise_operatorj是在比較中使用的位邏輯運算符。Pdated_bitmask是那些被更新或插入的列的整形位掩碼。例如，如果表T包括C1,C2,C3,C4,C5五列。為了確定是否只有C2列被修改，可用2來做位掩碼，如果想確定是否C1,C2,C3,C4都被修改，可用14來做位掩碼。Comparison_operator是一比較操作符用"="表示檢查在updated_bitmask中定義的所有列是否都被更新，用">"表示檢查是否在updated_bitmask中定義的某些列被更新。Column_bitmask指那些被檢查是否被更新的列的位掩碼。IJTT2-?!在titles表上創(chuàng)建個插入、更新類型的觸發(fā)器.這個觸發(fā)器的名稱為創(chuàng)建觸發(fā)沸的語句如白-Kieffer'js.ditld-ntitlesfordelete,updatereturn從以上的介紹中我們可以看出觸發(fā)器具有強大的功能，那么MSSQLServer是如何管理觸發(fā)器來完成這些任務(wù)呢？下面我們將對其工作原理及實現(xiàn)做較為詳細(xì)的介紹。每個觸發(fā)器有兩個特殊的表：插入表和刪除表。這兩個表是邏輯表，并且這兩個表是由系統(tǒng)管理的，存儲在內(nèi)存中，不是存儲在數(shù)據(jù)庫中，因此不允許用戶直接對其修改。這兩個表的結(jié)構(gòu)總