Sales.Individual (table)

From dbscript Online Help

Contents 1 wikibot 1.1 Columns 1.2 Primary Key 1.3 Indexes 1.4 Foreign Keys 1.5 Triggers 1.6 References 1.7 Dependencies 2 automatically generated

wikibot

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

Columns

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

Primary Key	Columns
PK_Individual_CustomerID	CustomerID

Indexes

Index	Type	Columns
PXML_Individual_Demographics		Demographics
XMLPATH_Individual_Demographics		Demographics
XMLPROPERTY_Individual_Demographics		Demographics
XMLVALUE_Individual_Demographics		Demographics

Foreign Keys

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

Triggers

Triggers	Type
iuIndividual	ON INSERT UPDATE

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

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

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

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