第05章創(chuàng)建與使用表

SQLServer數(shù)據(jù)庫應(yīng)用技術(shù)第5章創(chuàng)建與使用表

2023/2/51SQLServer2005第5章創(chuàng)建與使用表在關(guān)系數(shù)據(jù)庫中，每個(gè)關(guān)系都對(duì)應(yīng)為一張表，表是數(shù)據(jù)庫的最主要對(duì)象，是信息世界實(shí)體或?qū)嶓w間聯(lián)系的數(shù)據(jù)表示，是用來存儲(chǔ)與操作數(shù)據(jù)的邏輯結(jié)構(gòu)，使用數(shù)據(jù)庫時(shí)，絕大多數(shù)時(shí)間都是在與表打交道。因此掌握SQLServer表的相關(guān)知識(shí)與相關(guān)操作是非常重要的。本章圍繞表對(duì)象主要介紹如下內(nèi)容：表的概念、如何設(shè)計(jì)表、如何創(chuàng)建和修改表、表信息的交互式查詢與維護(hù)、刪除表等。目錄5.1關(guān)于表5.2設(shè)計(jì)表5.3創(chuàng)建和修改表5.4表信息的交互式查詢與維護(hù)5.5刪除表練習(xí)題5上機(jī)實(shí)習(xí)45.1關(guān)于表5.1.1表的基礎(chǔ)知識(shí)5.1.2數(shù)據(jù)完整性基礎(chǔ)知識(shí)5.1.3表的分類表是包含SQLServer2005數(shù)據(jù)庫中的所有形式數(shù)據(jù)的數(shù)據(jù)庫對(duì)象。每個(gè)表代表一類對(duì)其用戶有意義的對(duì)象。例如，在AdventureWorks

數(shù)據(jù)庫中有包含關(guān)于雇員、庫存、采購訂單和客戶數(shù)據(jù)的表。表定義是一個(gè)列集合。數(shù)據(jù)在表中的組織方式與在電子表格中相似，都是按行和列的格式組織的。每一行代表一條唯一的記錄，每一列代表記錄中的一個(gè)字段。例如，在包含公司雇員數(shù)據(jù)的表中，每一行代表一名雇員，各列分別代表該雇員的信息，如雇員編號(hào)、姓名、地址、職位以及家庭電話號(hào)碼等。5.1.1表的基礎(chǔ)知識(shí)AdventureWorks示例DB中的HumanResources.Department表:圖5-1Department表數(shù)據(jù)5.1.1表的基礎(chǔ)知識(shí)用戶通過交互方式或使用T-SQL語句來使用表中的數(shù)據(jù)，例如:USEAdventureWorks;SELECTc.FirstName,c.LastName--查詢姓為“Smith.”的雇員FROMHumanResources.EmployeeeJOINPerson.ContactcONe.ContactID=c.ContactIDWHEREc.LastName='Smith';GODELETEPurchasing.PurchaseOrderDetailWHEREPurchaseOrderDetailID=732;--刪除一條訂單詳細(xì)記錄GO--以下添加一條新的工作崗位記錄INSERTINTOHumanResources.Shift([Name],StartTime,EndTime)VALUES('Flex','1900-01-01','1900-01-01');GOUPDATEPerson.ContactSETLastName='Smith'FROMPerson.Contactc,HumanResources.EmployeeeWHEREc.ContactID=e.ContactIDANDe.EmployeeID=116;--修改一個(gè)雇員的姓名5.1.1表的基礎(chǔ)知識(shí)指定表域的第一步是確定列數(shù)據(jù)類型。域是列中允許的值的集合。域不僅包括強(qiáng)制數(shù)據(jù)類型的概念，還包括列中允許的值。例如，Production.Product表中的Color列的域包括nvarchar

數(shù)據(jù)類型和大小限制（15個(gè)字符）。該域還可指定列中所允許的字符串，如Red、Blue、Green、Yellow、Brown、Black、White、Teal、Gray和Silver。列可以接受空值，也可以拒絕空值。在數(shù)據(jù)庫中，NULL是一個(gè)特殊值，表示未知值的概念。列中除了具有數(shù)據(jù)類型和大小屬性之外，還有其它屬性。其它屬性是保證數(shù)據(jù)庫中數(shù)據(jù)完整性和表的引用完整性的重要部分。它們是：約束、規(guī)則、默認(rèn)值和DML觸發(fā)器等。

5.1.2數(shù)據(jù)完整性基礎(chǔ)知識(shí)

SQLServer2005的表可分為：用戶基本表、已分區(qū)表、臨時(shí)表與系統(tǒng)表四類。用戶基本表：是存放用戶數(shù)據(jù)的標(biāo)準(zhǔn)表，是數(shù)據(jù)庫中最基本、最主要的對(duì)象。5.1.3表的分類-用戶基本表已分區(qū)表:表按數(shù)據(jù)水平劃分為多個(gè)單元，并分布到DB中的多個(gè)文件組中。在維護(hù)整個(gè)集合的完整性時(shí)，使用分區(qū)可以快速而有效地訪問或管理數(shù)據(jù)子集，從而使大型表或索引更易于管理。在分區(qū)方案下，將數(shù)據(jù)從OLTP加載到OLAP系統(tǒng)中這樣的操作只需幾秒鐘，而不是像在早期版本中那樣需要幾分鐘或幾小時(shí)。對(duì)數(shù)據(jù)子集執(zhí)行的維護(hù)操作也將更有效，因?yàn)樗鼈兊哪繕?biāo)只是所需的數(shù)據(jù)，而不是整個(gè)表。已分區(qū)表支持所有與設(shè)計(jì)和查詢標(biāo)準(zhǔn)表關(guān)聯(lián)的屬性和功能，包括約束、默認(rèn)值、標(biāo)識(shí)和時(shí)間戳值、觸發(fā)器和索引。分區(qū)表主要用于：1）表中包含或可能包含以不同方式使用的許多數(shù)據(jù)；2）對(duì)表的查詢或更新沒有按照預(yù)期的方式執(zhí)行，或者維護(hù)開銷超出了預(yù)定義的維護(hù)期。5.1.3表的分類-已分區(qū)表臨時(shí)表：臨時(shí)表有兩種類型：本地臨時(shí)表和全局臨時(shí)表。本地臨時(shí)表:本地臨時(shí)表的名稱以單個(gè)數(shù)字符號(hào)(#)打頭在與首次創(chuàng)建或引用表時(shí)相同的SQLServer實(shí)例連接期間，本地臨時(shí)表只對(duì)于創(chuàng)建者是可見的。當(dāng)用戶與SQLServer實(shí)例斷開連接后，將刪除本地臨時(shí)表。全局臨時(shí)表:

全局臨時(shí)表的名稱以兩個(gè)數(shù)字符號(hào)(##)打頭在創(chuàng)建后對(duì)任何用戶和任何連接都是可見的，當(dāng)引用該表的所有用戶都與SQLServer實(shí)例斷開連接后，將刪除全局臨時(shí)表。

5.1.3表的分類-臨時(shí)表系統(tǒng)表：SQLServer將定義服務(wù)器配置及其定義所有表的數(shù)據(jù)存儲(chǔ)在一組特殊的表中，這組表稱為系統(tǒng)表。除非通過專用的管理員連接，否則用戶無法直接查詢或更新系統(tǒng)表。也可以通過目錄視圖查看系統(tǒng)表中的信息。重要提示：SQLServer2005DatabaseEngine系統(tǒng)表已作為只讀視圖實(shí)現(xiàn)，目的是為了保證SQLServer2005中的向后兼容性。無法直接使用這些系統(tǒng)表中的數(shù)據(jù)。建議通過使用目錄視圖訪問SQLServer元數(shù)據(jù)。5.1.3表的分類-系統(tǒng)表5.2設(shè)計(jì)表5.2.1表計(jì)劃指南5.2.2表的列數(shù)據(jù)類型5.2.3自動(dòng)編號(hào)列和全局唯一標(biāo)識(shí)符列5.2.4計(jì)算列5.2.5強(qiáng)制數(shù)據(jù)完整性在設(shè)計(jì)數(shù)據(jù)庫時(shí)，必須先確定數(shù)據(jù)庫所需的表、每個(gè)表中數(shù)據(jù)的類型以及可以訪問每個(gè)表的用戶,先規(guī)劃出表的特征：表要包含的數(shù)據(jù)的類型；表中的列數(shù)，每一列中數(shù)據(jù)的類型和長度（如果必要）；哪些列允許空值；是否要使用以及何處使用約束、默認(rèn)設(shè)置和規(guī)則；所需索引的類型，哪里需要索引，哪些列是主鍵，哪些是外鍵。創(chuàng)建表的最有效的方法是同時(shí)定義表中所需的所有內(nèi)容。這些內(nèi)容包括表的數(shù)據(jù)限制和其它組件。在創(chuàng)建和操作表后，將對(duì)表進(jìn)行更為細(xì)致的設(shè)計(jì)。創(chuàng)建表的有用方法是：創(chuàng)建基表，向其中添加一些數(shù)據(jù)，并使用這個(gè)基表一段時(shí)間。這種方法使您可以在添加各種約束、索引、默認(rèn)設(shè)置、規(guī)則和其它對(duì)象形成最終設(shè)計(jì)之前，發(fā)現(xiàn)哪些事務(wù)最常用，哪些數(shù)據(jù)經(jīng)常輸入。5.2.1表計(jì)劃指南設(shè)計(jì)表時(shí)首先要執(zhí)行的操作之一是為每個(gè)列指定數(shù)據(jù)類型：使用SQLServer2005系統(tǒng)數(shù)據(jù)類型；創(chuàng)建基于系統(tǒng)數(shù)據(jù)類型的別名數(shù)據(jù)類型；從在.NETFramework公共語言運(yùn)行時(shí)中創(chuàng)建的類型中創(chuàng)建用戶定義類型。SQLServer2005實(shí)行強(qiáng)制數(shù)據(jù)完整性，系統(tǒng)、別名和用戶定義類型可用于強(qiáng)制數(shù)據(jù)完整性，輸入或更改的數(shù)據(jù)必須符合原始CREATETABLE語句中指定的類型。例如，無法在定義為datetime的列中存儲(chǔ)姓氏，因?yàn)閐atetime列只接受有效日期。通常，將數(shù)值數(shù)據(jù)存儲(chǔ)在數(shù)字列中，尤其以后必須計(jì)算數(shù)值數(shù)據(jù)時(shí)。5.2.2表的列數(shù)據(jù)類型對(duì)于每個(gè)表，均可創(chuàng)建一個(gè)包含系統(tǒng)生成的序號(hào)值的標(biāo)識(shí)符列，該序號(hào)值以唯一方式標(biāo)識(shí)表中的每一行。標(biāo)識(shí)符列在其所定義的表中包含的值通常是唯一的。這意味著包含標(biāo)識(shí)符列的其它表可包含與另一個(gè)表所用的標(biāo)識(shí)值相同的標(biāo)識(shí)值。自動(dòng)編號(hào)列的一般列定義：列名[int]IDENTITY(1,1)NOTNULL。(1,1)代表種子和增量都為1。每個(gè)表中均可創(chuàng)建一個(gè)全局唯一標(biāo)識(shí)符列，該列中包含在全球聯(lián)網(wǎng)的所有計(jì)算機(jī)中不重復(fù)的值。合并來自多個(gè)DB的相似數(shù)據(jù)時(shí)，通常需要保證列包含全局唯一值。SQLServer2005使用GUID列進(jìn)行合并復(fù)制和事務(wù)復(fù)制，同時(shí)更新訂閱，以確保表的多個(gè)副本中的各行是唯一標(biāo)識(shí)的。必須為uniqueidentifier數(shù)據(jù)類型。全局唯一標(biāo)識(shí)符列的一般定義：列名[uniqueidentifier]ROWGUIDCOLNULLDEFAULT(newid())*GUID：GloballyUniqueIdentifier5.2.3自動(dòng)編號(hào)列和全局唯一標(biāo)識(shí)符列5.2.3自動(dòng)編號(hào)列和全局唯一標(biāo)識(shí)符列計(jì)算列由可以使用同一表中的其它列的表達(dá)式計(jì)算得來。該表達(dá)式可以是非計(jì)算列的列名、常量、函數(shù)、變量，也可以是用一個(gè)或多個(gè)運(yùn)算符連接的這些元素的任意組合。表達(dá)式不能為子查詢。例如，在AdventureWorks示例數(shù)據(jù)庫中，Sales.SalesOrderHeader表的TotalDue列有以下定義：TotalDueASSubtotal+TaxAmt+Freight。5.2.4計(jì)算列5.2.4計(jì)算列計(jì)劃和創(chuàng)建表要求標(biāo)識(shí)列的有效值，并確定強(qiáng)制列中數(shù)據(jù)完整性的方式。SQLServer2005提供了下列機(jī)制來強(qiáng)制列中數(shù)據(jù)的完整性：PRIMARYKEY約束、FOREIGNKEY約束、UNIQUE約束、CHECK約束、DEFAULT定義、允許空值。5.2.5強(qiáng)制數(shù)據(jù)完整性數(shù)據(jù)完整性基礎(chǔ)數(shù)據(jù)完整性是衡量數(shù)據(jù)庫中數(shù)據(jù)質(zhì)量好壞的一種標(biāo)志，是確保數(shù)據(jù)庫中數(shù)據(jù)一致、正確和符合企業(yè)規(guī)則的一種思想，是使無序的數(shù)據(jù)條理化，確保正確數(shù)據(jù)被存放在正確的位置的一種手段。數(shù)據(jù)完整性的類型實(shí)體完整性實(shí)體（或表）完整性要求表中的所有行都有一個(gè)惟一的標(biāo)識(shí)符，稱為主鍵值值域完整性域（或列）完整性是指對(duì)列指定一組有效的值并決定是否可為空值引用完整性引用完整性確保主鍵（在被引用表中）和外鍵（在引用表中）之間的關(guān)系得到保持用戶定義完整性主要是體現(xiàn)實(shí)際運(yùn)用的業(yè)務(wù)規(guī)則。實(shí)施數(shù)據(jù)完整性的途徑實(shí)體完整性PrimaryKey,UniqueKey,UniqueIndex,IdentityColumn(同一表的元組之間)值域完整性Default,Check,ForeignKey,DataType,Rule（列本身）引用完整性ForeignKey,Check,Triggers,Procedure（不同表的元組之間）用戶定義完整性Rule,Triggers,Procedures,CREATETABLE中的全部列級(jí)和表級(jí)約束使用約束實(shí)施數(shù)據(jù)完整性約束的用途是限制輸入到表中的值的范圍。約束的分類：按實(shí)現(xiàn)類型分：PRIMARYKEY，F(xiàn)OREIGNKEY,CHECK等；按作用級(jí)別分：列級(jí)約束，表級(jí)約束SP_HELPCONSTRAINTtable_name，查看表上所有約束的情況。創(chuàng)建和刪除約束的方法CREATETABLE在創(chuàng)建表時(shí)創(chuàng)建約束

ALTERTABLE在一個(gè)已有的表上創(chuàng)建約束ALTERTABLEtable_nameADD

CONSTRAINTcons_name{具體的約束}ALTERTABLEtable_nameDROPCONSTRAINT

cons_namePRIMARYKEY約束PRIMARYKEY約束在表中定義了一個(gè)惟一標(biāo)識(shí)每一列的主鍵 語法：[CONSTRAINT約束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}應(yīng)用PRIMARYKEY約束的注意事項(xiàng)每張表只能有一個(gè)PRIMARYKEY約束輸入的值必須是惟一的不允許空值將在指定列上創(chuàng)建惟一索引PRIMARYKEY約束（續(xù)）PRIMARYKEY約束示例在Customers表上創(chuàng)建PRIMARYKEY約束，指明表的主鍵值是CustomerID，并且創(chuàng)建非聚集索引以強(qiáng)制約束USENorthwindALTERTABLEdbo.CustomersADDCONSTRAINTPK_CustomersPRIMARYKEYNONCLUSTERED(CustomerID)PRIMARYKEY約束（續(xù)）DROPTABLEtest1CREATETABLEtest1(job_idSMALLINTIDENTITY(1,1)CONSTRAINTcns_prkPRIMARYKEYCLUSTERED,job_descVARCHAR(50)NOTNULLDEFAULT'NewPosition')列級(jí)約束PRIMARYKEY約束（續(xù)）DROPTABLEtest2CREATETABLEtest2(event_typeINT,event_timeDATETIME,event_siteCHAR(50),event_descCHAR(1024),CONSTRAINTevent_keyPRIMARYKEY(event_type,event_time))表級(jí)約束UNIQUE約束UNIQUE約束指明列中的任意兩行不能有相同的值

語法：[CONSTRAINT約束名]UNIQUE[CLUSTERED|NONCLUSTERED]{(列[,...n])}應(yīng)用UNIQUE約束的注意事項(xiàng)允許一個(gè)空值在一個(gè)表上允許多個(gè)UNIQUE約束可在一個(gè)或者多個(gè)列上定義是通過一個(gè)惟一索引強(qiáng)制約束的UNIQUE約束（續(xù)）UNIQUE約束示例在Suppliers表的公司名列上創(chuàng)建UNIQUE約束USENorthwindALTERTABLEdbo.SuppliersADDCONSTRAINTU_CompanyNameUNIQUENONCLUSTERED(CompanyName)UNIQUE約束（續(xù)）DROPTABLEtest2CREATETABLEtest2(event_idINTPRIMARYKEY,event_typeINT,event_timeDATETIME,event_siteCHAR(50),event_descCHAR(1024),CONSTRAINTunique_eventUNIQUE(event_type,event_time))UNIQUE約束（續(xù)）PRIMARYKEY和UNIQUE約束聲明PRIMARYKEY或UNIQUE約束的結(jié)果只是自動(dòng)創(chuàng)建了一個(gè)指定列上的惟一索引，通過惟一索引來確保值的惟一性可空性PRIMARYKEY的各個(gè)列必須聲明為NOTNULL，而UNIQUE的各個(gè)列可以聲明為允許NULL值在惟一索引中，認(rèn)為所有的NULL值是相等的索引的屬性PRIMARYKEY約束所創(chuàng)建的惟一索引默認(rèn)為CLUSTERED，除非表中另外一列已經(jīng)聲明為CLUSTEREDUNIQUE約束所創(chuàng)建的惟一索引默認(rèn)為NONCLUSTERED選擇鍵保持鍵的長度盡可能短，必要時(shí)可另外創(chuàng)建一個(gè)代替鍵不要使用float或real數(shù)據(jù)類型的列作為主鍵CHECK約束限制輸入到指定列的值只能為某些特定值

語法：[CONSTRAINT約束名]CHECK(邏輯表達(dá)式)

[WITHCHECK|WITHNOCHECK]兩種強(qiáng)制域完整性的方法：CHECK約束和規(guī)則CHECK約束定義了一個(gè)表達(dá)式，若數(shù)據(jù)修改語句使得表達(dá)式值為FALSE的話，將拒絕語句執(zhí)行規(guī)則的功能和CHECK約束基本相同，除了語法不同，能力稍弱。規(guī)則是為了向下兼容而保留的規(guī)則可定義一次，然后對(duì)多個(gè)列分別綁定；而CHECK約束則需要對(duì)每個(gè)列定義。但CHECK約束的功能略強(qiáng)一些（例如引用同行中其他列的值和調(diào)用系統(tǒng)函數(shù)等）CHECK約束（續(xù)）CHECK約束示例為Employees表中的BrithDate增加CHECK約束，使出生日期處于可接受的日期范圍內(nèi)USENorthwind

ALTERTABLEdbo.Employees

ADD

CONSTRAINTCK_birthdate

CHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate())FOREIGNKEY約束FOREIGNKEY約束：定義到同表或其他表中具有PRIMARYKEY或者UNIQUE約束的列的引用語法：[CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])]具有FOREIGNKEY約束的列的取值范圍只能是被引用的列的列值FOREIGNKEY約束（續(xù)）FOREIGNKEY約束的示例使用FOREIGNKEY約束，確保Orders表中的客戶標(biāo)識(shí)與Customers表中的有效的客戶標(biāo)識(shí)相關(guān)聯(lián)USENorthwindALTERTABLEdbo.OrdersADDCONSTRAINTFK_Orders_CustomersFOREIGNKEY(CustomerID)REFERENCESdbo.Customers(CustomerID)級(jí)聯(lián)引用完整性FOREIGNKEY約束包含一個(gè)CASCADE選項(xiàng)，允許對(duì)一個(gè)定義了UNIQUE或者PRIMARYKEY約束的列的值的修改自動(dòng)傳播到引用它的外鍵上，這個(gè)動(dòng)作稱為級(jí)聯(lián)引用完整性

語法：[CONSTRAINT約束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])].

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企圖刪除或者更新被其他表的外鍵所引用的鍵都將引發(fā)一個(gè)錯(cuò)誤，對(duì)數(shù)據(jù)的改變會(huì)被回滾。NOACTION是默認(rèn)值CASCADE：若父表中的行變化了，則引用表中相應(yīng)的行也自動(dòng)變化級(jí)聯(lián)引用完整性(續(xù))USEpubsGODROPTABLEf;DROPTABLEp;CREATETABLEp(aINTNOTNULLPRIMARYKEY)CREATETABLEf(aINTNOTNULL,bINTNOTNULL,PRIMARYKEY(a,b),FOREIGNKEY(a)REFERENCESp(a)ONDELETECASCADE)INSERTINTOp(a)VALUES(10);INSERTINTOp(a)VALUES(20);INSERTINTOf(a,b)VALUES(10,10);INSERTINTOf(a,b)VALUES(20,20);SELECT*FROMf;DELETEFROMpWHEREa=10;//修改成UPDATE的測(cè)試?yán)?？？SELECT*FROMf;級(jí)聯(lián)引用完整性(續(xù))使用規(guī)則規(guī)則指定了能插入列的可接受的值。它確保數(shù)據(jù)在指定值域內(nèi)，匹配某個(gè)模式，或者匹配指定列表中的項(xiàng)。

語法：創(chuàng)建：CREATERULE規(guī)則AS條件表達(dá)式

刪除：DROPRULE規(guī)則[,...n]

綁定規(guī)則：sp_bindrule

分離已綁定的規(guī)則：sp_unbindrule關(guān)于規(guī)則的注意事項(xiàng)規(guī)則定義可以包含任何在WHERE子句中有效的表達(dá)式一個(gè)列或者用戶定義數(shù)據(jù)類型只能被一個(gè)規(guī)則綁定EXAMPLEDROPRULEcountry_rule;DROPTABLEtest;GOCREATETABLEtest(authorCHAR(20),nationVARCHAR(20));GOCREATERULEcountry_ruleAS@countryIN('CHINA','USA','GERMANY','FRANCE')GOEXECSP_BINDRULEcountry_rule,'test.nation'INSERTINTOtest(author,nation)VALUES('Zhang','CHINA');--INSERTINTOtest(author,nation)VALUES('Wang','JAPANESE');SELECT*FROMtest;EXECSP_UNBINDRULE

'test.nation'使用默認(rèn)值默認(rèn)值：若插入數(shù)據(jù)的時(shí)候沒有指定某列的值，則此列綁定的默認(rèn)值將提供一個(gè)默認(rèn)值。

語法：創(chuàng)建：CREATEDEFAULT默認(rèn)值A(chǔ)S常量表達(dá)式 刪除：DROPDEFAULT默認(rèn)值[,...n] 綁定一個(gè)默認(rèn)： sp_bindefault 解除默認(rèn)值綁定：sp_unbindefaultDEFAULT約束（續(xù)）應(yīng)用DEFAULT約束的注意事項(xiàng)（續(xù)）允許使用一些系統(tǒng)提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER及CURRENT_TIMESTAMP有可能會(huì)和CHECK約束沖突為具有PRIMARYKEY或UNIQUE約束的列指定默認(rèn)值是沒有意義的常量值外面可以加或者不加括號(hào)，字符或者日期常量必須加上單引號(hào)或雙引號(hào)在一個(gè)INSERT語句中對(duì)一個(gè)列DEFAULT約束（續(xù)）DEFAULT約束示例為Northwind數(shù)據(jù)庫中的Customers表的ContactName列創(chuàng)建DEFAULT約束，當(dāng)INSERT語句中此列的值沒有提供的時(shí)候，自動(dòng)使用“UNKNOWN”作為它的值USENorthwind

ALTERTABLEdbo.Customers

ADD

CONSTRAINTDF_contactnameDEFAULT'UNKNOWN'

FORContactName使用約束的注意事項(xiàng)可直接在表上創(chuàng)建、更改和刪除約束，而不必刪除并重建表應(yīng)該在應(yīng)用程序和事務(wù)內(nèi)建立錯(cuò)誤檢查邏輯，以測(cè)試是否違反了約束SQLServer里的約束只是“最后防線”當(dāng)給表添加約束的時(shí)候，SQLServer將檢查現(xiàn)有數(shù)據(jù)是否違反約束建議創(chuàng)建約束的時(shí)候指定名稱，否則系統(tǒng)將為約束自動(dòng)產(chǎn)生復(fù)雜的名稱名稱必須惟一，且符合SQLServer標(biāo)識(shí)符的規(guī)則查看約束的信息系統(tǒng)存儲(chǔ)過程：sp_helpconstraint、sp_help信息模式視圖：check_constraints、referential_constraints、table_constraints系統(tǒng)表：syscomments、sysreferences、sysconstraints5.3創(chuàng)建和修改表

5.3.1用戶架構(gòu)分離5.3.2創(chuàng)建表5.3.3實(shí)踐表的完整性5.3.4修改表5.3創(chuàng)建和修改表

數(shù)據(jù)通常存儲(chǔ)于永久表中，不過您也可以創(chuàng)建臨時(shí)表。表存儲(chǔ)于數(shù)據(jù)庫文件中，任何擁有所需權(quán)限的用戶都可以對(duì)其進(jìn)行操作，除非已將其刪除。每個(gè)表至多可定義1024列。表和列的名稱必須遵守標(biāo)識(shí)符的規(guī)定，在特定表中必須是唯一的，但同一數(shù)據(jù)庫的不同表中可使用相同的列名。盡管對(duì)于每一個(gè)架構(gòu)(Schema)在一個(gè)數(shù)據(jù)庫內(nèi)表的名稱必須是唯一的，但如果為每張表指定了不同的架構(gòu)，則可以創(chuàng)建多個(gè)具有相同名稱的表。SQLServer2005切斷數(shù)據(jù)庫用戶和架構(gòu)之間的隱式連接。架構(gòu)是形成單個(gè)命名空間的數(shù)據(jù)庫實(shí)體的集合。命名空間是一個(gè)集合，其中每個(gè)元素的名稱都是唯一的。架構(gòu)獨(dú)立于創(chuàng)建它們的數(shù)據(jù)庫用戶而存在?？梢栽诓桓募軜?gòu)名稱的情況下轉(zhuǎn)讓架構(gòu)的所有權(quán)。在架構(gòu)中創(chuàng)建具有用戶友好名稱的對(duì)象，指示對(duì)象的功能。例如，accounting.ap.sandra.balance，accounting.ap.invoice.balance等架構(gòu)。完全限定的對(duì)象名為：server.database.schema.object5.3.1用戶架構(gòu)分離將架構(gòu)與數(shù)據(jù)庫用戶分離對(duì)管理員和開發(fā)人員而言有下列好處：1）多個(gè)用戶可以通過角色成員身份或Windows組成員身份擁有一個(gè)架構(gòu)。這擴(kuò)展了允許角色和組擁有對(duì)象的用戶熟悉的功能。2）極大地簡化了刪除數(shù)據(jù)庫用戶的操作。3）刪除數(shù)據(jù)庫用戶不需要重命名該用戶架構(gòu)所包含的對(duì)象,也不再需要修改和測(cè)試顯式引用這些對(duì)象的應(yīng)用程序。4）多個(gè)用戶可以共享一個(gè)默認(rèn)架構(gòu)以進(jìn)行統(tǒng)一名稱解析。5）開發(fā)人員通過共享默認(rèn)架構(gòu)可以將共享對(duì)象存儲(chǔ)在為特定應(yīng)用程序?qū)ｉT創(chuàng)建的架構(gòu)中，而不是DBO架構(gòu)中。6）可以更大的粒度管理架構(gòu)和架構(gòu)包含的對(duì)象的權(quán)限。7）默認(rèn)架構(gòu):用于解析未使用其完全限定名稱引用的對(duì)象的名稱。首先檢查的是調(diào)用數(shù)據(jù)庫用戶所擁有的架構(gòu)，然后是DBO擁有的架構(gòu)。每個(gè)用戶都有一個(gè)默認(rèn)架構(gòu)，用于指定服務(wù)器在解析對(duì)象的名稱時(shí)將要搜索的第一個(gè)架構(gòu)?？梢允褂肅REATEUSER和ALTERUSER的DEFAULT_SCHEMA選項(xiàng)設(shè)置和更改默認(rèn)架構(gòu),缺省默認(rèn)為DBO架構(gòu)。5.3.1用戶架構(gòu)分離

SQLServer2005提供了兩種方法創(chuàng)建數(shù)據(jù)庫表:(1)利用ManagementStudio交互式創(chuàng)建表；(2)利用T-SQL語句中的createtable命令創(chuàng)建表。

5.3.2創(chuàng)建表

--利用ManagementStudio創(chuàng)建表圖5-4創(chuàng)建表結(jié)構(gòu)對(duì)話框5.3.2創(chuàng)建表

--利用ManagementStudio創(chuàng)建表圖5-5表設(shè)計(jì)器菜單與表設(shè)計(jì)器工具欄5.3.2創(chuàng)建表

--利用ManagementStudio創(chuàng)建表CREATETABLE[database_name.[schema_name].|schema_name.]table_name({<column_definition>|<computed_column_definition>}[<table_constraint>][,...n])[ON{partition_scheme_name(partition_column_name)|filegroup|"default"}][{TEXTIMAGE_ON{filegroup|"default"}][;]<column_definition>::=column_name<data_type>[COLLATEcollation_name][NULL|NOTNULL][[CONSTRAINTconstraint_name]DEFAULTconstant_expression]|[IDENTITY[(seed,increment)][NOTFORREPLICATION]][ROWGUIDCOL][<column_constraint>[...n]]<datatype>::=[type_schema_name.]type_name[(precision[,scale]|max|[{CONTENT|DOCUMENT}]xml_schema_collection)]5.3.2創(chuàng)建

--利用createtable命令創(chuàng)建表<column_constraint>::=[CONSTRAINTconstraint_name]{{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED][WITHFILLFACTOR=fillfactor|WITH(<index_option>[,...n])][ON{partition_scheme_name(partition_column_name)|filegroup|"default"}]|[FOREIGNKEY]REFERENCES[schema_name.]referenced_table_name[(ref_column)][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][NOTFORREPLICATION]|CHECK[NOTFORREPLICATION](logical_expression)}<computed_column_definition>::=column_nameAScomputed_column_expression5.3.2創(chuàng)建

--利用createtable命令創(chuàng)建表例5-1創(chuàng)建雇員信息表的createtable命令如下：createtableemployee(empidintnotnullprimarykeyclustered,namevarchar(20)NOTNULL,sexchar(2)NULL,birthdaydatetimenull,hire_datedatetimeNOTNULLDEFAULT(getdate()),professional_titlevarchar(10)null,salarymoneynullcheck(salaryisnullorsalary>=450),memontextnull)。5.3.2創(chuàng)建表

--利用createtable命令創(chuàng)建表例5-2創(chuàng)建一個(gè)學(xué)生信息表的createtable命令如下：（注意該命令執(zhí)行時(shí)，需要class班級(jí)表與speciality專業(yè)表已存在，或者先暫時(shí)把兩外碼參照子句去掉后執(zhí)行。）CREATETABLEstudent(snochar(8)NOTNULLPRIMARYKEY,snamechar(20)NOTNULL,sclasschar(10)NULLDEFAULT('本科'),ssexchar(2)NULLDEFAULT('男')CHECK(ssex='男'orssex='女'),sdhvarchar(14)NULL,scsrqdatetimeNULL,srxrqdatetimeNULL,saddrvarchar(50)NULL,smemovarchar(200)NULL,spnochar(4)NULL,csnochar(4)NULL,CONSTRAINTFK_student_classFOREIGNKEY(csno)REFERENCESclass(csno),CONSTRAINTFK_student_specialityFOREIGNKEY(spno)REFERENCESspeciality(spno))5.3.2創(chuàng)建表

--利用createtable命令創(chuàng)建表1)添加、修改或刪除列。例如列名、長度、數(shù)據(jù)類型、精度、小數(shù)位數(shù)以及為空性均可進(jìn)行修改，不過有一些限制而已；2)分區(qū)表可以將其重新分區(qū)，也可以添加或刪除單個(gè)分區(qū)；3)可以添加或刪除PRIMARYKEY約束和FOREIGNKEY約束；4)可以添加或刪除UNIQUE和CHECK以及DEFAULT定義和對(duì)象；5)可使用IDENTITY或ROWGUIDCOL添加或刪除標(biāo)識(shí)符列。6)表及表中所選定的列已注冊(cè)為全文索引。7)表的名稱或架構(gòu)也可以更改。執(zhí)行此操作時(shí)，還必須更改使用該表的舊名稱或架構(gòu)的所有觸發(fā)器、存儲(chǔ)過程、T-SQL腳本或其它程序代碼中表的名稱。5.3.4修改表

圖5-6表設(shè)計(jì)器菜單與表設(shè)計(jì)器工具欄5.3.4修改表--

在ManagementStudio中交互方式修改表ALTERTABLE[database_name.[schema_name].|schema_name.]table_name{ALTERCOLUMNcolumn_name{[type_schema_name.]type_name[({precision[,scale]|max|xml_schema_collection})][NULL|NOTNULL][COLLATEcollation_name]|{ADD|DROP}{ROWGUIDCOL|PERSISTED}}|[WITH{CHECK|NOCHECK}]ADD{<column_definition>|<computed_column_definition>|<table_constraint>}[,...n]|DROP{[CONSTRAINT]constraint_name[WITH(<drop_clustered_constraint_option>[,...n])]|COLUMNcolumn_name}[,...n]|[WITH{CHECK|NOCHECK}]{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,...n]}|{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,...n]}|SWITCH[PARTITIONsource_partition_number_expression]TO[schema_name.]target_table[PARTITIONtarget_partition_number_expression]}[;]5.3.4修改表

--T-SQL命令方式修改表例5-3添加新列,本例將添加一個(gè)允許空值的列，而且沒有通過DEFAULT定義提供的值。在該新列中，每一行都將有NULL值。CREATETABLEtb1(column_aINT);ALTERTABLEtb1ADDcolumn_bVARCHAR(20)NULL;EXECsp_helptb1;--查詢表結(jié)構(gòu)例5-4刪除列，本例將修改一個(gè)表以刪除列。ALTERTABLEtb1DROPCOLUMNcolumn_b;5.3.4修改表

--T-SQL命令方式修改表例5-5更改列的數(shù)據(jù)類型，本例將表中列的數(shù)據(jù)類型由INT更改為DECIMAL。ALTERTABLEtb1ALTERCOLUMNcolumn_aDECIMAL(5,2);例5-6添加包含約束的列，本例給列column_b添加一個(gè)UNIQUE約束。ALTERTABLEtb1ADDCONSTRAINTtb1_uniqueUNIQUE(column_b);5.3.4修改表

--T-SQL命令方式修改表2）重命名命令SP_RENAMEsp_rename[@objname=]'object_name',[@newname=]'new_name'[,[@objtype=]'object_type']例5-16重命名表，本例將SalesTerritory表重命名為SalesTerr，然后再恢復(fù)為SalesTerritory。USEAdventureWorks;EXECsp_rename'Sales.SalesTerritory','SalesTerr';GOEXECsp_rename'Sales.SalesTerr','SalesTerritory';--再改還來5.3.4修改表

--T-SQL命令方式修改表例5-17重命名列，本例將SalesTerritory表中的TerritoryID列重命名為TerrID，查看后再恢復(fù)原列名。USEAdventureWorks;EXECsp_rename'Sales.SalesTerritory.TerritoryID','TerrID','COLUMN';select*fromSales.SalesTerritory--檢查重命名后的情況GOEXECsp_rename'Sales.SalesTerritory.TerrID','TerritoryID','COLUMN';--再恢復(fù)原名5.3.4修改表

--T-SQL命令方式修改表3）更改表的架構(gòu)：ALTERSCHEMAALTERSCHEMA實(shí)現(xiàn)在架構(gòu)之間傳輸對(duì)象，語法為：ALTERSCHEMAschema_nameTRANSFERobject_name例5-18本例通過將表Address從架構(gòu)Person傳輸?shù)紿umanResources架構(gòu)來修改該架構(gòu)，然后再恢復(fù)回來。USEAdventureWorks;--如下把表Address從架構(gòu)Person傳輸?shù)紿umanResources架構(gòu)ALTERSCHEMAHumanResourcesTRANSFERPerson.Address;GO--如下把表Address從架構(gòu)HumanResources傳輸回Person架構(gòu)，恢復(fù)原樣ALTERSCHEMAPersonTRANSFERHumanResources.Address;5.3.4修改表

--T-SQL命令方式修改表5.4表信息的交互式查詢與維護(hù)

--查看表元信息需要查看表中的數(shù)據(jù),顯示表的依賴關(guān)系來確定哪些對(duì)象（如視圖、存儲(chǔ)過程和觸發(fā)器）是由表決定的。在更改表時(shí)，相關(guān)對(duì)象可能會(huì)受到影響。查看表的定義：sp_help；查看表中的數(shù)據(jù)：SELECT命令；獲取有關(guān)表的信息：select*fromsys.tables；獲取有關(guān)表列的信息：select*fromsys.columns；查看表的依賴關(guān)系：select*fromsys.sql_dependencies。COLUMNPROPERTY返回有關(guān)列或過程參數(shù)的信息，其語法:COLUMNPROPERTY(id,column,property)

。例5-19本例返回LastName列的長度。SELECTCOLUMNPROPERTY(OBJECT_ID('Person.Contact'),'LastName','PRECISION')AS'Col_Length';5.4表信息的交互式查詢與維護(hù)

--查看表格數(shù)據(jù)信息返回本節(jié)首頁5.4表信息的交互式查詢與維護(hù)

--查看表數(shù)據(jù)信息返回本節(jié)首頁圖5-7添加、編輯、刪除表記錄5.4表信息的交互式查詢與維護(hù)

--查看表與其他對(duì)象依賴關(guān)系（3）查看表格與其它數(shù)據(jù)庫對(duì)象的依賴關(guān)系：打開某數(shù)據(jù)庫的“數(shù)據(jù)庫關(guān)系圖”，能直觀地創(chuàng)建某表與其它表間的依賴關(guān)系。如圖5-9是SC、S與C的依賴關(guān)系。還可以在表上鼠標(biāo)右鍵菜單→“查看依賴關(guān)系”菜單，在出現(xiàn)的“對(duì)象依賴關(guān)系”對(duì)話框中查看某表依賴的對(duì)象或依賴于的對(duì)象，圖略。圖5-9SC、S與C的依賴關(guān)系5.4表信息的交互式查詢與維護(hù)

--對(duì)表查詢圖5-10查詢表的菜單、工具欄與快捷菜單5.4表信息的交互式查詢與維護(hù)

--對(duì)表查詢5.5刪除表有些情況下必須刪除表：例如，要在數(shù)據(jù)庫中實(shí)現(xiàn)一個(gè)新的設(shè)計(jì)或釋放空間時(shí)。刪除表后，該表的結(jié)構(gòu)定義、數(shù)據(jù)、全文索引、約束和索引都從數(shù)據(jù)庫中永久刪除；存儲(chǔ)空間被釋放。如果要?jiǎng)h除通過FOREIGNKEY和UNIQUE或PRIMARYKEY約束相關(guān)聯(lián)的表，則必須先刪除具有FOREIGNKEY約束的表。如果要?jiǎng)h除FOREIGNKEY約束中引用的表但不能刪除整個(gè)外鍵表，則必須刪除FOREIGNKEY約束。如果要?jiǎng)h除表中的所有數(shù)據(jù)但不刪除表本身，則可以截?cái)嘣摫??？墒褂肨RUNCATETABLE刪除所有行。5.5刪除表（1）利用ManagementStudio刪除表（2）利用droptable語句刪除表droptable語句可以刪除一個(gè)表和表中的數(shù)據(jù)及其與表有關(guān)的所有索引、觸發(fā)器、約束、和權(quán)限規(guī)范等。droptable語句的語法形式為：DROPTABLE<table_name>例5-20刪除表employee的命令為：droptableemployee。在系統(tǒng)表中，不能使用DROPTABLE語句。清空表可用TRUNCATETABLE命令，如TRUNCATETABLEemployee;練習(xí)題5

1、簡述數(shù)據(jù)庫表的構(gòu)成，數(shù)據(jù)庫表中的行有次序嗎？2、創(chuàng)建表的實(shí)質(zhì)就是定義表結(jié)構(gòu)以及約束等屬性，簡述列屬性設(shè)置。3、使用IDENTITY列時(shí)應(yīng)注意什么？4、NULL代表什么含義？將其與其它值進(jìn)行比較會(huì)產(chǎn)生什么結(jié)果？5、SQLServer2005中有幾種類型的表？請(qǐng)簡單說明之。6、SQLServer2005中的臨時(shí)表分為本地（或局部）臨時(shí)表和全局臨時(shí)表，請(qǐng)簡單敘述一下它們的區(qū)別？7、什么是數(shù)據(jù)完整性？數(shù)據(jù)完整性分為哪幾類？如何實(shí)施？上機(jī)實(shí)習(xí)4

實(shí)驗(yàn)?zāi)康?/p>

1）了解表、字段、類型、表完整性等的概念；2）實(shí)踐如何設(shè)計(jì)、創(chuàng)建和修改、交互式查詢、刪除表等基本操作。

實(shí)驗(yàn)內(nèi)容

1、創(chuàng)建數(shù)據(jù)庫DingBao及其三表（報(bào)紙編碼表、報(bào)紙訂閱表和顧客編碼表）用你掌握的某種方法，創(chuàng)建訂報(bào)管理子系統(tǒng)的數(shù)據(jù)庫DingBao，在DingBao數(shù)據(jù)庫中用交互式界面操作方法或CREATETABLE創(chuàng)建如下三表的表結(jié)構(gòu)（表名及字段名使用括號(hào)中給出的英文名），并完成三表所示內(nèi)容的輸入，根據(jù)需要可自行設(shè)計(jì)輸入更多的表記錄。創(chuàng)建表結(jié)構(gòu)時(shí)要求滿足：(1)報(bào)紙編碼表(PAPER)以報(bào)紙編號(hào)(pno)為主鍵；(2)顧客編碼表(CUSTOMER)以顧客編號(hào)(cno)為主鍵；(3)報(bào)紙訂閱表（CP）以報(bào)紙編號(hào)(pno)與顧客編號(hào)(cno)為主鍵，訂閱份數(shù)(num)的缺省值為1。上機(jī)實(shí)習(xí)4

表A1報(bào)紙編碼表(PAPER)表A2報(bào)紙訂閱表（CP）報(bào)紙編號(hào)(pno)報(bào)紙名稱(pna)單價(jià)(ppr)000001人民日?qǐng)?bào)12.5000002解放軍報(bào)14.5000003光明日?qǐng)?bào)10.5000004青年報(bào)11.5000005揚(yáng)子晚報(bào)18.5顧客編號(hào)(cno)報(bào)紙編號(hào)(pno)訂閱份數(shù)(num)00010000012000100000240001000005600020000012000200000320002000005200030000032000300000440004000001100040000033000400000520005000003400050000021000500000430005000005500050000014表A3顧客編碼表(CUSTOMER)顧客編號(hào)(cno)顧客姓名(cna)顧客地址(adr)0001李濤無錫市解放東路123號(hào)0002錢金浩無錫市人民西路234號(hào)0003鄧杰無錫市惠河路270號(hào)0004朱海紅無錫市中山東路432號(hào)0005歐陽陽文無錫市中山東路532號(hào)

2、實(shí)踐“5.3.3實(shí)踐表的完整性”一節(jié)中陳述的各完整性措施，在掌握命令操作的同時(shí)，也能掌握界面操作的方法，即在ManagementStudio中實(shí)踐各種完整性的創(chuàng)建與完整性的約束。

3、參照“12.4.3數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計(jì)”一節(jié)，在數(shù)據(jù)庫KCGL中創(chuàng)建的各表。9.2DML觸發(fā)器9.2.1DML觸發(fā)器概述9.2.2設(shè)計(jì)DML觸發(fā)器9.2.3實(shí)現(xiàn)和管理DML

觸發(fā)器返回本節(jié)首頁返回本節(jié)首頁1、了解DML觸發(fā)器兩種機(jī)制強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。觸發(fā)器是特殊的存儲(chǔ)過程，由DBMS自動(dòng)執(zhí)行。SQLServer包括兩大類觸發(fā)器：DDL觸發(fā)器是SQLServer2005的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言(DDL)事件時(shí)將調(diào)用這些觸發(fā)器。當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言(DML)事件時(shí)將調(diào)用DML觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語句、UPDATE語句或DELETE語句。DML觸發(fā)器可以查詢其它表，還可以包含復(fù)雜的T-SQL語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待，錯(cuò)誤發(fā)生，則整個(gè)事務(wù)即自動(dòng)回滾。9.2.1DML觸發(fā)器概述返回本節(jié)首頁DML觸發(fā)器在以下方面非常有用：DML觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。不過，通過級(jí)聯(lián)引用完整性約束可以更有效地進(jìn)行這些更改；DML觸發(fā)器可以防止惡意或錯(cuò)誤的INSERT、UPDATE以及DELETE操作，并強(qiáng)制執(zhí)行比CHECK約束定義的限制更為復(fù)雜的其它限制。與CHECK約束不同，DML觸發(fā)器可以引用其它表中的列。DML觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施；一個(gè)表中的多個(gè)同類DML觸發(fā)器（INSERT、UPDATE或DELETE）允許采取多個(gè)不同的操作來響應(yīng)同一個(gè)修改語句。9.2.1DML觸發(fā)器概述返回本節(jié)首頁2、DML觸發(fā)器的類型AFTER/FOR觸發(fā)器：在INSERT、UPDATE或DELETE語句操作之后執(zhí)行AFTER觸發(fā)器，只能在表上指定該類觸發(fā)器。

INSTEADOF觸發(fā)器：執(zhí)行INSTEADOF觸發(fā)器代替通常的觸發(fā)動(dòng)作。可為一個(gè)或多個(gè)基表的視圖定義INSTEADOF觸發(fā)器，而這些觸發(fā)器能夠擴(kuò)展視圖可支持的更新類型。

CLR觸發(fā)器：CLR觸發(fā)器可以是AFTER觸發(fā)器或INSTEADOF觸發(fā)器。CLR觸發(fā)器還可以是DDL觸發(fā)器。CLR觸發(fā)器將執(zhí)行在托管代碼（在.NETFramework中創(chuàng)建并在SQLServer中上載的程序集的成員）中編寫的方法，而不用執(zhí)行T-SQL存儲(chǔ)過程。9.2.1DML觸發(fā)器概述

--DML觸發(fā)器的類型返回本節(jié)首頁1、DML觸發(fā)器計(jì)劃指南

SQLServer2005提供了兩個(gè)用于設(shè)計(jì)DML觸發(fā)器的選項(xiàng)：1）執(zhí)行INSTEADOF觸發(fā)器代替通常的觸發(fā)操作，可對(duì)帶有一個(gè)或多個(gè)基表的視圖定義INSTEADOF觸發(fā)器，以擴(kuò)展視圖可支持的更新類型；2）在執(zhí)行INSERT、UPDATE或DELETE語句操作之后執(zhí)行AFTER觸發(fā)器。2、指定DML觸發(fā)器何時(shí)激發(fā)1）AFTER觸發(fā)器在處理觸發(fā)操作（INSERT、UPDATE或DELETE）、INSTEADOF觸發(fā)器和約束之后激發(fā)。可通過指定AFTER或FOR來請(qǐng)求AFTER觸發(fā)器。2）INSTEADOF將在處理約束前激發(fā)，以替代觸發(fā)操作。如果表有AFTER觸發(fā)器，它們將在處理約束之后激發(fā)。如果違反了約束，將回滾INSTEADOF觸發(fā)器操作并且不執(zhí)行AFTER觸發(fā)器；3）每個(gè)表或視圖針對(duì)每個(gè)觸發(fā)操作（UPDATE、DELETE和INSERT）可有一個(gè)相應(yīng)的INSTEADOF觸發(fā)器，可有多個(gè)相應(yīng)的AFTER觸發(fā)器。9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁3、DML觸發(fā)器執(zhí)行如果違反了約束，則永遠(yuǎn)不會(huì)執(zhí)行AFTER觸發(fā)器；因此，這些觸發(fā)器不能用于任何可能防止違反約束的處理。執(zhí)行INSTEADOF觸發(fā)器，而不執(zhí)行觸發(fā)操作。

DML觸發(fā)器的性能開銷通常很低。運(yùn)行DML觸發(fā)器所花時(shí)間大都用于引用其它表，這些表可能位于內(nèi)存中，也可能位于數(shù)據(jù)庫設(shè)備上。刪除的表(deleted)和插入的表(inserted)始終位于內(nèi)存中。觸發(fā)器所引用的其它表的位置將確定操作所需的時(shí)間。9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁4、設(shè)計(jì)INSTEADOF觸發(fā)器INSTEADOF觸發(fā)器主要優(yōu)點(diǎn)是可使不能更新的視圖支持更新。基于多個(gè)基表的視圖必須使用INSTEADOF觸發(fā)器來支持引用多個(gè)表中數(shù)據(jù)的插入、更新和刪除操作。INSTEADOF觸發(fā)器另一個(gè)優(yōu)點(diǎn)是可編寫這樣的邏輯代碼：在允許批處理的其它部分成功的同時(shí)拒絕批處理中的某些部分。對(duì)于含有使用DELETE或UPDATE級(jí)聯(lián)操作定義的外鍵的表，不能定義INSTEADOFDELETE和INSTEADOFUPDATE觸發(fā)器。9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁這里先說明，創(chuàng)建觸發(fā)器命令為createtrigger，其語法為：

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]sql_statement[...n]}}9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁CREATETABLEPerson(SSNchar(11)PRIMARYKEY,Namenvarchar(100),Addressnvarchar(100),Birthdatedatetime)CREATETABLEEmployeeTable(EmployeeIDintPRIMARYKEY,SSNchar(11)UNIQUE,Departmentnvarchar(10),Salarymoney,CONSTRAINTFKEmpPerFOREIGNKEY(SSN)REFERENCESPerson(SSN))CREATETABLEPersonDuplicates(SSNchar(11),Namenvarchar(100),Addressnvarchar(100),Birthdatedatetime,InsertSNAMEnchar(100),WhenInserteddatetime)CREATEVIEWEmployee

ASSELECTP.SSNasSSN,Name,Address,Birthdate,EmployeeID,Department,SalaryFROMPersonP,EmployeeTableEWHEREP.SSN=E.SSN9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁INSTEADOF觸發(fā)器將行插入到單個(gè)視圖的多個(gè)基表中。在PersonDuplicates表中記錄了插入具有重復(fù)社會(huì)保障號(hào)的行的嘗試。EmployeeTable中的重復(fù)行將更改為更新語句。CREATETRIGGERIO_Trig_INS_EmployeeONEmployeeINSTEADOFINSERTASBEGINSETNOCOUNTON--檢查人員重復(fù)情況，不重復(fù)則插入IF(NOTEXISTS(SELECTP.SSNFROMPersonP,insertedIWHEREP.SSN=I.SSN))INSERTINTOPersonSELECTSSN,Name,Address,BirthdateFROMinsertedELSE--重復(fù)則日志記錄于“人員重復(fù)表”中

INSERTINTOPersonDuplicatesSELECTSSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()FROMinserted9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁--檢查雇員重復(fù)情況，不重復(fù)則插入IF(NOTEXISTS(SELECTE.SSNFROMEmployeeTableE,insertedWHEREE.SSN=inserted.SSN))INSERTINTOEmployeeTableSELECTEmployeeID,SSN,Department,SalaryFROMinsertedELSE--重復(fù)則改為對(duì)表EmployeeTable做修改操作

UPDATEEmployeeTableSETEmployeeID=I.EmployeeID,Department=I.Department,Salary=I.SalaryFROMEmployeeTableE,insertedIWHEREE.SSN=I.SSNEND9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁5、管理觸發(fā)器安全性默認(rèn)情況下，在調(diào)用觸發(fā)器的用戶的上下文中執(zhí)行DML和DDL觸發(fā)器。觸發(fā)器的調(diào)用方是執(zhí)行使觸發(fā)器運(yùn)行的語句的用戶。例如，如果用戶Mary執(zhí)行可以使DML觸發(fā)器DML_trigMary

運(yùn)行的DELETE語句，則DML_trigMary中的代碼將在Mary的用戶特權(quán)上下文中執(zhí)行。希望向數(shù)據(jù)庫或服務(wù)器實(shí)例中引入惡意代碼的用戶可以使用此默認(rèn)行為。下面由用戶JohnDoe創(chuàng)建的DDL觸發(fā)器：

CREATETRIGGERDDL_trigJohnDoeONDATABASEFORALTER_TABLEASGRANTCONTROLSERVERTOJohnDoe;

9.2.2設(shè)計(jì)DML觸發(fā)器返回本節(jié)首頁創(chuàng)建DML觸發(fā)器前應(yīng)考慮下列問題：CREATETRIGGER語句必須是批處理中的第一個(gè)語句，該語句后面的所有其它語句被解釋為CREATETRIGGER語句定義的一部分；創(chuàng)建DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，不能轉(zhuǎn)給其他用戶；DML觸發(fā)器為數(shù)據(jù)庫對(duì)象，其名稱必須遵循標(biāo)識(shí)符的命名規(guī)則；DML觸發(fā)器可引用當(dāng)前DB以外對(duì)象，但只能在當(dāng)前DB中創(chuàng)建它；雖然DML觸發(fā)器可以引用臨時(shí)表，但不能對(duì)臨時(shí)表或系統(tǒng)表創(chuàng)建DML觸發(fā)器。不應(yīng)引用系統(tǒng)表，而應(yīng)使用信息架構(gòu)視圖；對(duì)于含有用DELETE或UPDATE操作定義的外鍵的表，不能定義INSTEADOFDELETE和INSTEADOFUPDATE觸發(fā)器；雖然TRUNCATETABLE語句類似于不帶WHERE子句的DELETE語句（用于刪除所有行），但它并不會(huì)觸發(fā)DELETE觸發(fā)器，因?yàn)門RUNCATETABLE語句沒有記錄；WRITETEXT語句不會(huì)觸發(fā)INSERT或UPDATE觸發(fā)器。9.2.3實(shí)現(xiàn)和管理DML觸發(fā)器返回本節(jié)首頁創(chuàng)建DML觸發(fā)器時(shí)需指定：名稱；定義觸發(fā)器時(shí)所基于的表；觸發(fā)器被觸發(fā)的時(shí)間；激活觸發(fā)器語句為INSERT、UPDATE或DELETE的任意組合。執(zhí)行觸發(fā)器操作的編程語句。一個(gè)表中可有多個(gè)給定類型的AFTER觸發(fā)器，每個(gè)觸發(fā)器只能應(yīng)用于一個(gè)表。一個(gè)表只能具有一個(gè)給定類型的INSTEADOF觸發(fā)器。用定義觸發(fā)器時(shí)所基于的表或視圖的名稱架構(gòu)創(chuàng)建觸發(fā)器。CREATETRIGGER權(quán)限默認(rèn)授予定義觸發(fā)器的表所有者、sysadmin固定服務(wù)器角色以及db_owner和db_ddladmin固定數(shù)據(jù)庫角色的成員，并且不可轉(zhuǎn)讓。如果對(duì)某個(gè)視圖創(chuàng)建INSTEADOF觸發(fā)器，如果視圖所有者不同時(shí)擁有視圖和觸發(fā)器所引用的基表，所有權(quán)鏈將斷開。9.2.3實(shí)現(xiàn)和管理DML觸發(fā)器返回本節(jié)首頁

1、對(duì)DML觸發(fā)器進(jìn)行編程幾乎所有可以編寫成批處理的T-SQL語句都可用于創(chuàng)建DML觸發(fā)器，下列語句除外：ALTERDATABASE、CREATEDATABASE、DROPDATABASE、LOADDATABASE、LOADLOG、RECONFIGURE、RESTOREDATABASE、RESTORELOG。此外，在對(duì)作為觸發(fā)操作的目標(biāo)的表或視圖使用下列T-SQL語句時(shí)，將不允許在DML觸發(fā)器的主體內(nèi)使用這些語句：CREATEINDEX、ALTERINDEX、DROPINDEX、DBCCDBREINDEX、ALTERPARTITIONFUNCTION、DROPTABLE、ALTERTABLE。WITHENCRYPTION子句加密觸發(fā)器定義加密，無法進(jìn)行解密，且任何人都無法進(jìn)行查看，包括觸發(fā)器的所有者和系統(tǒng)管理員。UPDATE()函數(shù)可用于確定INSERT或UPDATE語句是否影響表中的特定列。COLUMNS_UPDATED函數(shù)也可用于檢查INSERT或UPDATE語句更新了表中的哪些列。例9-8使用IFUPDATE()子句測(cè)試數(shù)據(jù)修改。本示例對(duì)表my_table創(chuàng)建了INSERT觸發(fā)器my_trig，并測(cè)試列b是否受任何INSERT嘗試的影響。

CREATETABLEmy_table(a

int

NULL,b

intNULL)CREATETRIGGERmy_trigONmy_tableFORINSERTASIFUPDATE(b)PRINT'列b已修改'9.2.3實(shí)現(xiàn)和管理DML觸發(fā)器返回本節(jié)首頁例9-9使用COLUMNS_UPDATED()函數(shù)測(cè)試數(shù)據(jù)修改，獲得上例類似的結(jié)果。CREATETRIGGERmy_trig2ONmy_tableFORINSERTASIF(COLUMNS_UPDATED()&2=2)PRINT'列b已修改'

DML觸發(fā)器可以引用觸發(fā)器創(chuàng)建時(shí)不存在的表。這稱為“延遲的名稱解析”。如果DML觸發(fā)器引用的對(duì)象已刪除或重命名，則執(zhí)行觸發(fā)器時(shí)會(huì)返回錯(cuò)誤。但是，如果DML觸發(fā)器中引用的對(duì)象被替換為同名對(duì)象，則不必重新創(chuàng)建即可執(zhí)行觸發(fā)器。例如，如果觸發(fā)器trig1引用表test1，在刪除test1表后，創(chuàng)建一個(gè)名為test1的不同的表，則trig1在執(zhí)行時(shí)會(huì)引用新表。9.2.3實(shí)現(xiàn)和管理DML觸發(fā)器返回本節(jié)首頁(1)編程CLR觸發(fā)器SQLServer2005提供了在SQLServer內(nèi)創(chuàng)建數(shù)據(jù)庫對(duì)象的功能，該數(shù)據(jù)庫對(duì)象在.NETFramework公共語言運(yùn)行時(shí)(CLR)中創(chuàng)建的程序集中進(jìn)行編程。可以利用由CLR提供的大量編程模型的數(shù)據(jù)庫對(duì)象包括DML觸發(fā)器、DDL觸發(fā)器、存儲(chǔ)過程、函數(shù)、聚合函數(shù)和類型。在SQLServer中創(chuàng)建CLR觸發(fā)器（DML或DDL）的步驟：以.NET語言將觸發(fā)器定義為一類。然后，使用適當(dāng)?shù)恼Z言編譯器編譯該類，以便在.NETFramework中生成一個(gè)程序集；使用CREATEASSEMBLY語句在SQLServer中注冊(cè)程序集；創(chuàng)建用于引用已注冊(cè)的程序集的觸發(fā)器。創(chuàng)建CLR觸發(fā)器命令也為：CREATETRIGGER9.2.3實(shí)現(xiàn)和管理DML觸發(fā)器返回本節(jié)首頁(2)使用包含COMMIT或ROLLBACKTRANSACTION的DML觸發(fā)器建議不要將COMMITTRANSACTION或COMMITWORK語句放置在觸發(fā)器中。當(dāng)包含ROLLBACKTRANSACTION語句的觸發(fā)器在批處理中執(zhí)行時(shí)，它們會(huì)取消整個(gè)批處理。在SQLServer2005中，也會(huì)返回錯(cuò)誤。