Latest revision |
Your text |
Line 2: |
Line 2: |
| | | |
| {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
− | |- valign="top" | + | |- |
| | '''Table | | | '''Table |
| | Sales.Individual | | | Sales.Individual |
− | |- valign="top" | + | |- |
− | | '''Description
| |
− | | Demographic data about customers that purchase Adventure Works products online.
| |
| |} | | |} |
| | | |
− | === Columns ===
| + | |
| {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
| |- style="background:silver" | | |- style="background:silver" |
Line 17: |
Line 15: |
| | '''Nullable | | | '''Nullable |
| | '''Default | | | '''Default |
− | | '''Description | + | | '''PK |
− | | + | | '''Index |
− | |- valign="top" | + | |
| + | |- |
| | CustomerID | | | CustomerID |
| | int | | | int |
| | not null | | | not null |
| | | | | |
− | | Unique customer identification number. Foreign key to Customer.CustomerID. | + | | PK_Individual_CustomerID |
− | |- valign="top" | + | | |
| + | |- |
| | ContactID | | | ContactID |
| | int | | | int |
| | not null | | | not null |
| | | | | |
− | | Identifies the customer in the Contact table. Foreign key to Contact.ContactID. | + | | |
− | |- valign="top" | + | | |
| + | |- |
| | Demographics | | | Demographics |
| | xml | | | xml |
| | null | | | null |
| | | | | |
− | | Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis. | + | | |
− | |- valign="top" | + | | PXML_Individual_DemographicsXMLPATH_Individual_DemographicsXMLPROPERTY_Individual_DemographicsXMLVALUE_Individual_Demographics |
| + | |- |
| | ModifiedDate | | | ModifiedDate |
| | datetime | | | datetime |
| | not null | | | not null |
| | (getdate()) | | | (getdate()) |
− | | Date and time the record was last updated. | + | | |
| + | | |
| |} | | |} |
| | | |
− | === Primary Key ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Primary Key
| |
− | | '''Columns
| |
− | |- valign="top"
| |
− | | PK_Individual_CustomerID
| |
− | | CustomerID
| |
− | |}
| |
| | | |
− | === Indexes ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Index
| |
− | | '''Type
| |
− | | '''Columns
| |
| | | |
− | |- valign="top"
| |
− | | PXML_Individual_Demographics
| |
− | |
| |
− | | Demographics
| |
− | |- valign="top"
| |
− | | XMLPATH_Individual_Demographics
| |
− | |
| |
− | | Demographics
| |
− | |- valign="top"
| |
− | | XMLPROPERTY_Individual_Demographics
| |
− | |
| |
− | | Demographics
| |
− | |- valign="top"
| |
− | | XMLVALUE_Individual_Demographics
| |
− | |
| |
− | | Demographics
| |
− | |}
| |
− |
| |
− | === Foreign Keys ===
| |
| {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
| |- style="background:silver" | | |- style="background:silver" |
Line 86: |
Line 55: |
| | '''Column | | | '''Column |
| | '''Referenced Column | | | '''Referenced Column |
− | | + | |
− | |- valign="top" | + | |- |
| | [[Person.Contact_(table)|Person.Contact]] | | | [[Person.Contact_(table)|Person.Contact]] |
| | ContactID | | | ContactID |
| | ContactID | | | ContactID |
− | |- valign="top" | + | |- |
| | [[Sales.Customer_(table)|Sales.Customer]] | | | [[Sales.Customer_(table)|Sales.Customer]] |
| | CustomerID | | | CustomerID |
Line 97: |
Line 66: |
| |} | | |} |
| | | |
− | === Triggers ===
| + | |
| {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
| |- style="background:silver" | | |- style="background:silver" |
− | | '''Trigger | + | | '''Triggers |
| | '''Type | | | '''Type |
− | | + | |
− | |- valign="top" | + | |- |
| | iuIndividual | | | iuIndividual |
| | ON INSERT UPDATE | | | ON INSERT UPDATE |
| |} | | |} |
− |
| |
− | ==== Trigger iuIndividual ====
| |
− | <pre>
| |
− | CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual]
| |
− | AFTER INSERT, UPDATE NOT FOR REPLICATION AS
| |
− | BEGIN
| |
− | DECLARE @Count int;
| |
− |
| |
− | SET @Count = @@ROWCOUNT;
| |
− | IF @Count = 0
| |
− | RETURN;
| |
− |
| |
− | SET NOCOUNT ON;
| |
− |
| |
− | -- Only allow the Customer to be a Store OR Individual
| |
− | IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store]
| |
− | ON inserted.[CustomerID] = [Sales].[Store].[CustomerID])
| |
− | BEGIN
| |
− | -- Rollback any active or uncommittable transactions
| |
− | IF @@TRANCOUNT > 0
| |
− | BEGIN
| |
− | ROLLBACK TRANSACTION;
| |
− | END
| |
− | END;
| |
− |
| |
− | IF UPDATE([CustomerID]) OR UPDATE([Demographics])
| |
− | BEGIN
| |
− | UPDATE [Sales].[Individual]
| |
− | SET [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
| |
− | <TotalPurchaseYTD>0.00</TotalPurchaseYTD>
| |
− | </IndividualSurvey>'
| |
− | FROM inserted
| |
− | WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]
| |
− | AND inserted.[Demographics] IS NULL;
| |
− |
| |
− | UPDATE [Sales].[Individual]
| |
− | SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
| |
− | insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD>
| |
− | as first
| |
− | into (/IndividualSurvey)[1]')
| |
− | FROM inserted
| |
− | WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]
| |
− | AND inserted.[Demographics] IS NOT NULL
| |
− | AND inserted.[Demographics].exist(N'declare default element namespace
| |
− | "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
| |
− | /IndividualSurvey/TotalPurchaseYTD') <> 1;
| |
− | END;
| |
− | END;
| |
− | </pre>
| |
− |
| |
− | === References ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | | '''Child Type
| |
− | | '''Child Object
| |
− |
| |
− | |- valign="top"
| |
− | | Schema
| |
− | | Schema
| |
− | | [[Sales_(schema)|Sales]]
| |
− |
| |
− | |
| |
− | |
| |
− | |- valign="top"
| |
− | | Data Type
| |
− | | XML Schema Collection
| |
− | | [[Sales.IndividualSurveySchemaCollection_(xml schema collection)|Sales.IndividualSurveySchemaCollection]]
| |
− |
| |
− | |
| |
− | |
| |
− | |- valign="top"
| |
− | | Update
| |
− | | Table
| |
− | | [[Sales.Individual_(table)|Sales.Individual]]
| |
− |
| |
− | | Trigger
| |
− | | iuIndividual
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.Store_(table)|Sales.Store]]
| |
− |
| |
− | | Trigger
| |
− | | iuIndividual
| |
− | |}
| |
− |
| |
− | === Dependencies ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Reference Type
| |
− | | '''Object Type
| |
− | | '''Referencing Object
| |
− |
| |
− | | '''Child Type
| |
− | | '''Child Object
| |
− |
| |
− | |- valign="top"
| |
− | | Select
| |
− | | View
| |
− | | [[Sales.vIndividualCustomer_(view)|Sales.vIndividualCustomer]]
| |
− |
| |
− | |
| |
− | |
| |
− | |- valign="top"
| |
− | | Select
| |
− | | View
| |
− | | [[Sales.vIndividualDemographics_(view)|Sales.vIndividualDemographics]]
| |
− |
| |
− | |
| |
− | |
| |
− | |- valign="top"
| |
− | | Select
| |
− | | SQL table-valued-function
| |
− | | [[dbo.ufnGetContactInformation_(function)|dbo.ufnGetContactInformation]]
| |
− |
| |
− | |
| |
− | |
| |
− | |- valign="top"
| |
− | | Update
| |
− | | Table
| |
− | | [[Sales.Individual_(table)|Sales.Individual]]
| |
− |
| |
− | | Trigger
| |
− | | iuIndividual
| |
− | |- valign="top"
| |
− | | Update
| |
− | | Table
| |
− | | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.Store_(table)|Sales.Store]]
| |
− |
| |
− | | Trigger
| |
− | | iStore
| |
− | |}
| |
− |
| |
− |
| |
| == automatically generated == | | == automatically generated == |
| | | |
Line 259: |
Line 83: |
| | Sales.Individual | | | Sales.Individual |
| |- valign="top" | | |- valign="top" |
| + | |
| | '''Description | | | '''Description |
| | Demographic data about customers that purchase Adventure Works products online. | | | Demographic data about customers that purchase Adventure Works products online. |
| |- | | |- |
| + | |
| |} | | |} |
| | | |
Line 341: |
Line 167: |
| | Schema | | | Schema |
| | Schema | | | Schema |
− | | [[Sales_(schema)|Sales]] | + | | [[Sales_(schema)|.Sales]] |
| | | |
| | | | | |