AdventureWorks

Basic Index   Expanded Index

Table Name:Sales.Individual
Description:Demographic data about customers that purchase Adventure Works products online.
Primary Keys:CustomerID
FieldTypeDefaultsNulls?Comments
CustomerIDintNoneNoUnique customer identification number. Foreign key to Customer.CustomerID.
ContactIDintNoneNoIdentifies the customer in the Contact table. Foreign key to Contact.ContactID.
DemographicsxmlNoneYesPersonal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_Individual_CustomerIDYesYesCustomerID

No check constraints
Internal Foreign Key ConstraintAffected FieldSource Table
FK_Individual_Contact_ContactIDContactIDPerson.Contact
FK_Individual_Customer_CustomerIDCustomerIDSales.Customer

No primary keys as foreign key constraints
TriggerText
iuIndividualCREATE 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;
IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store]
ON inserted.[CustomerID] = [Sales].[Store].[CustomerID])
BEGIN
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;