ScrewTurn Wiki

Edit

wikibot

TableSales.Individual
DescriptionDemographic data about customers that purchase Adventure Works products online.

Edit

Columns

ColumnData TypeNullableDefaultDescription
CustomerIDintnot null Unique customer identification number. Foreign key to Customer.CustomerID.
ContactIDintnot null Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
Demographicsxmlnull Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Edit

Primary Key

Primary KeyColumns
PK_Individual_CustomerIDCustomerID

Edit

Indexes

IndexTypeColumns
PXML_Individual_Demographics Demographics
XMLPATH_Individual_Demographics Demographics
XMLPROPERTY_Individual_Demographics Demographics
XMLVALUE_Individual_Demographics Demographics

Edit

Foreign Keys

RelationColumnReferenced Column
Person.ContactContactIDContactID
Sales.CustomerCustomerIDCustomerID

Edit

Triggers

TriggerType
iuIndividualON INSERT UPDATE

Edit

Trigger iuIndividual

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' 
            0.00 
            ' 
        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 0.00 
            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;

Edit

References

Dependency TypeObject TypeReferenced ObjectChild TypeChild Object
SchemaSchemaSales  
Data TypeXML Schema CollectionSales.IndividualSurveySchemaCollection  
UpdateTableSales.IndividualTriggeriuIndividual
SelectTableSales.StoreTriggeriuIndividual

Edit

Dependencies

Reference TypeObject TypeReferencing ObjectChild TypeChild Object
SelectViewSales.vIndividualCustomer  
SelectViewSales.vIndividualDemographics  
SelectSQL table-valued-functiondbo.ufnGetContactInformation  
UpdateTableSales.IndividualTriggeriuIndividual
UpdateTableSales.SalesOrderDetailTriggeriduSalesOrderDetail
SelectTableSales.StoreTriggeriStore

Edit

automatically generated

TableSales.Individual
DescriptionDemographic data about customers that purchase Adventure Works products online.

ColumnData TypeNullableDefaultDescription / PK / Index
CustomerIDintnot null Unique customer identification number. Foreign key to Customer.CustomerID.
PK_Individual_CustomerID
ContactIDintnot null Identifies the customer in the Contact table. Foreign key to Contact.ContactID.
DemographicsXMLnull Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
PXML_Individual_DemographicsXMLPATH_Individual_DemographicsXMLPROPERTY_Individual_DemographicsXMLVALUE_Individual_Demographics
ModifiedDatedatetimenot null(GETDATE())Date and time the record was last updated.



RelationColumnReferenced Column
Person.ContactContactIDContactID
Sales.CustomerCustomerIDCustomerID

TriggersType
iuIndividualON INSERT UPDATE

Dependency TypeObject TypeReferenced ObjectChild TypeChild Object
SchemaSchemaSales
Data TypeXML Schema CollectionSales.IndividualSurveySchemaCollection
UpdateTableSales.IndividualTriggeriuIndividual
SelectTableSales.StoreTriggeriuIndividual

Reference TypeObject TypeReferencing ObjectChild TypeChild Object
SelectViewSales.vIndividualCustomer
SelectViewSales.vIndividualDemographics
SelectSQL table-valued-functiondbo.ufnGetContactInformation
UpdateTableSales.IndividualTriggeriuIndividual
UpdateTableSales.SalesOrderDetailTriggeriduSalesOrderDetail
SelectTableSales.StoreTriggeriStore

ScrewTurn Wiki version 2.0.36. Some of the icons created by FamFamFam.