Editing Sales.Individual (table)

== 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"
|-
| '''Table
| Sales.Individual
|- valign="top"
| '''Description
| Demographic data about customers that purchase Adventure Works products online.
|-
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description / PK / Index
		
|- valign="top"
| CustomerID
| int
| not null
| 
| Unique customer identification number. Foreign key to Customer.CustomerID.PK_Individual_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.PXML_Individual_DemographicsXMLPATH_Individual_DemographicsXMLPROPERTY_Individual_DemographicsXMLVALUE_Individual_Demographics
|- valign="top"
| ModifiedDate
| datetime
| not null
| (GETDATE())
| Date and time the record was last updated.
|}



{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column
		
|-
| [[Person.Contact_(table)|Person.Contact]]
| ContactID
| ContactID
|-
| [[Sales.Customer_(table)|Sales.Customer]]
| CustomerID
| CustomerID
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| iuIndividual
| 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
|}