Sales Individual (table)

Modified on 2011/08/27 11:19 by wikibot Categorized as Uncategorized
== 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
| &nbsp;
| Unique customer identification number. Foreign key to Customer.CustomerID.
|- valign="top"
| ContactID
| int
| not null
| &nbsp;
| Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
|- valign="top"
| Demographics
| xml
| null
| &nbsp;
| 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
| &nbsp;
| Demographics
|- valign="top"
| XMLPATH_Individual_Demographics
| &nbsp;
| Demographics
|- valign="top"
| XMLPROPERTY_Individual_Demographics
| &nbsp;
| Demographics
|- valign="top"
| XMLVALUE_Individual_Demographics
| &nbsp;
| 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 ====
{{{{<nowiki>
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;
</nowiki>}}}}

=== 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]]

| &nbsp;
| &nbsp;
|- valign="top"
| Data Type
| XML Schema Collection
| [[Sales IndividualSurveySchemaCollection (xml schema collection)|Sales.IndividualSurveySchemaCollection]]

| &nbsp;
| &nbsp;
|- 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]]

| &nbsp;
| &nbsp;
|- valign="top"
| Select
| View
| [[Sales vIndividualDemographics (view)|Sales.vIndividualDemographics]]

| &nbsp;
| &nbsp;
|- valign="top"
| Select
| SQL table-valued-function
| [[dbo ufnGetContactInformation (function)|dbo.ufnGetContactInformation]]

| &nbsp;
| &nbsp;
|- 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
|}
devio dbscript ScrewTurn Wiki

Navigation¶

Sales Individual (table)
