Welcome Guest, you are in: <root> • Login

Page History: Sales Individual (table)

Compare Page Revisions

4 3 2 1 0 Current 4 3 2 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: 2010/06/24 01:10

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 ¶

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

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