Difference between revisions of "Sales.Individual (table)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''Table | Sales.Individual |- |} {| border="1" cellpadding="5" cellspacing="0" style="...)
 
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== wikibot ==
 
== wikibot ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- valign="top"
 +
| '''Table
 +
| 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"
 +
|- style="background:silver"
 +
| '''Column
 +
| '''Data Type
 +
| '''Nullable
 +
| '''Default
 +
| '''Description
 +
 +
|- valign="top"
 +
| CustomerID
 +
| int
 +
| not null
 +
|
 +
| Unique customer identification number. Foreign key to Customer.CustomerID.
 +
|- valign="top"
 +
| ContactID
 +
| int
 +
| not null
 +
|
 +
| Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
 +
|- valign="top"
 +
| Demographics
 +
| xml
 +
| null
 +
|
 +
| Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
 +
|- valign="top"
 +
| ModifiedDate
 +
| datetime
 +
| not null
 +
| (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"
 +
|- style="background:silver"
 +
| '''Relation
 +
| '''Column
 +
| '''Referenced Column
 +
 +
|- valign="top"
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
| ContactID
 +
| ContactID
 +
|- valign="top"
 +
| [[Sales.Customer_(table)|Sales.Customer]]
 +
| CustomerID
 +
| CustomerID
 +
|}
 +
 +
=== Triggers ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Trigger
 +
| '''Type
 +
 +
|- valign="top"
 +
| iuIndividual
 +
| 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 ==
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 5: Line 258:
 
| '''Table
 
| '''Table
 
| Sales.Individual
 
| Sales.Individual
 +
|- valign="top"
 +
| '''Description
 +
| Demographic data about customers that purchase Adventure Works products online.
 
|-
 
|-
 
|}
 
|}
Line 15: Line 271:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description / PK / Index
−
| '''Index
 
 
 
−
|-
+
|- valign="top"
 
| CustomerID
 
| CustomerID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_Individual_CustomerID
+
| Unique customer identification number. Foreign key to Customer.CustomerID.<br />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.<br />
−
|
+
|- valign="top"
−
|-
 
 
| Demographics
 
| Demographics
−
| xml
+
| XML
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.<br />PXML_Individual_DemographicsXMLPATH_Individual_DemographicsXMLPROPERTY_Individual_DemographicsXMLVALUE_Individual_Demographics
−
| PXML_Individual_DemographicsXMLPATH_Individual_DemographicsXMLPROPERTY_Individual_DemographicsXMLVALUE_Individual_Demographics
+
|- valign="top"
−
|-
 
 
| ModifiedDate
 
| ModifiedDate
 
| datetime
 
| datetime
 
| not null
 
| not null
−
| (getdate())
+
| (GETDATE())
−
|  
+
| Date and time the record was last updated.<br />
−
|
 
 
|}
 
|}
  
Line 75: Line 326:
 
| iuIndividual
 
| iuIndividual
 
| ON INSERT UPDATE
 
| ON INSERT UPDATE
 +
|}
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
| '''Child Type
 +
| '''Child Object
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Sales_(schema)|Sales]]
 +
 +
|
 +
|
 +
|-
 +
| Data Type
 +
| XML Schema Collection
 +
| [[Sales.IndividualSurveySchemaCollection_(xml_schema_collection)|Sales.IndividualSurveySchemaCollection]]
 +
 +
|
 +
|
 +
|-
 +
| Update
 +
| Table
 +
| [[Sales.Individual_(table)|Sales.Individual]]
 +
 +
| Trigger
 +
| iuIndividual
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.Store_(table)|Sales.Store]]
 +
 +
| Trigger
 +
| iuIndividual
 +
|}
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 +
| '''Referencing Object
 +
 +
| '''Child Type
 +
| '''Child Object
 +
 +
|-
 +
| Select
 +
| View
 +
| [[Sales.vIndividualCustomer_(view)|Sales.vIndividualCustomer]]
 +
 +
|
 +
|
 +
|-
 +
| Select
 +
| View
 +
| [[Sales.vIndividualDemographics_(view)|Sales.vIndividualDemographics]]
 +
 +
|
 +
|
 +
|-
 +
| Select
 +
| SQL table-valued-function
 +
| [[dbo.ufnGetContactInformation_(function)|dbo.ufnGetContactInformation]]
 +
 +
|
 +
|
 +
|-
 +
| Update
 +
| Table
 +
| [[Sales.Individual_(table)|Sales.Individual]]
 +
 +
| Trigger
 +
| iuIndividual
 +
|-
 +
| Update
 +
| Table
 +
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 +
 +
| Trigger
 +
| iduSalesOrderDetail
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.Store_(table)|Sales.Store]]
 +
 +
| Trigger
 +
| iStore
 
|}
 
|}

Latest revision as of 00:17, 24 June 2010

wikibot[edit]

Table Sales.Individual
Description Demographic data about customers that purchase Adventure Works products online.

Columns[edit]

Column Data Type Nullable Default Description
CustomerID int not null Unique customer identification number. Foreign key to Customer.CustomerID.
ContactID int not null Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
Demographics xml null Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.

Primary Key[edit]

Primary Key Columns
PK_Individual_CustomerID CustomerID

Indexes[edit]

Index Type Columns
PXML_Individual_Demographics Demographics
XMLPATH_Individual_Demographics Demographics
XMLPROPERTY_Individual_Demographics Demographics
XMLVALUE_Individual_Demographics Demographics

Foreign Keys[edit]

Relation Column Referenced Column
Person.Contact ContactID ContactID
Sales.Customer CustomerID CustomerID

Triggers[edit]

Trigger Type
iuIndividual ON INSERT UPDATE

Trigger iuIndividual[edit]

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;

References[edit]

Dependency Type Object Type Referenced Object Child Type Child Object
Schema Schema Sales
Data Type XML Schema Collection Sales.IndividualSurveySchemaCollection
Update Table Sales.Individual Trigger iuIndividual
Select Table Sales.Store Trigger iuIndividual

Dependencies[edit]

Reference Type Object Type Referencing Object Child Type Child Object
Select View Sales.vIndividualCustomer
Select View Sales.vIndividualDemographics
Select SQL table-valued-function dbo.ufnGetContactInformation
Update Table Sales.Individual Trigger iuIndividual
Update Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail
Select Table Sales.Store Trigger iStore


automatically generated[edit]

Table Sales.Individual
Description Demographic data about customers that purchase Adventure Works products online.


Column Data Type Nullable Default Description / PK / Index
CustomerID int not null Unique customer identification number. Foreign key to Customer.CustomerID.
PK_Individual_CustomerID
ContactID int not null Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
Demographics XML null Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
PXML_Individual_DemographicsXMLPATH_Individual_DemographicsXMLPROPERTY_Individual_DemographicsXMLVALUE_Individual_Demographics
ModifiedDate datetime not null (GETDATE()) Date and time the record was last updated.


Relation Column Referenced Column
Person.Contact ContactID ContactID
Sales.Customer CustomerID CustomerID


Triggers Type
iuIndividual ON INSERT UPDATE


Dependency Type Object Type Referenced Object Child Type Child Object
Schema Schema Sales
Data Type XML Schema Collection Sales.IndividualSurveySchemaCollection
Update Table Sales.Individual Trigger iuIndividual
Select Table Sales.Store Trigger iuIndividual


Reference Type Object Type Referencing Object Child Type Child Object
Select View Sales.vIndividualCustomer
Select View Sales.vIndividualDemographics
Select SQL table-valued-function dbo.ufnGetContactInformation
Update Table Sales.Individual Trigger iuIndividual
Update Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail
Select Table Sales.Store Trigger iStore
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.Individual_(table)&oldid=2666"