ScrewTurn Wiki
Navigation
Main Page
Random Page
Create a new Page
All Pages
Categories
Administration
File Management
Login/Logout
Language Selection
Your Profile
Create Account
Quick Search
Advanced Search »
Back
History
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 ==== {{{{<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]] | | |- 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''' |- | CustomerID | int | not null | | Unique customer identification number. Foreign key to Customer.CustomerID.<br />PK_Individual_CustomerID |- | ContactID | int | not null | | Identifies the customer in the Contact table. Foreign key to Contact.ContactID.<br /> |- | Demographics | XML | null | | Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.<br />PXML_Individual_DemographicsXMLPATH_Individual_DemographicsXMLPROPERTY_Individual_DemographicsXMLVALUE_Individual_Demographics |- | ModifiedDate | datetime | not null | (GETDATE()) | Date and time the record was last updated.<br /> |} {| 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 |}
ScrewTurn Wiki version 2.0.36. Some of the icons created by
FamFamFam
.