AdventureWorks

Basic Index   Expanded Index

Table Name:Sales.Store
Description:Customers (resellers) of Adventure Works products.
Primary Keys:CustomerID
FieldTypeDefaultsNulls?Comments
CustomerIDintNoneNoPrimary key. Foreign key to Customer.CustomerID.
Namenvarchar (50)NoneNoName of the store.
SalesPersonIDintNoneYesID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID.
DemographicsxmlNoneYesDemographic informationg about the store such as the number of employees, annual sales and store type.
rowguiduniqueidentifier(newid())NoROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_Store_CustomerIDYesYesCustomerID
AK_Store_rowguidNoYesrowguid
IX_Store_SalesPersonIDNoNoSalesPersonID

No check constraints
Internal Foreign Key ConstraintAffected FieldSource Table
FK_Store_Customer_CustomerIDCustomerIDSales.Customer
FK_Store_SalesPerson_SalesPersonIDSalesPersonIDSales.SalesPerson

Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_StoreContact_Store_CustomerIDSales.StoreContactCustomerID

TriggerText
iStoreCREATE TRIGGER [Sales].[iStore] ON [Sales].[Store]
AFTER INSERT AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Individual]
ON inserted.[CustomerID] = [Sales].[Individual].[CustomerID])
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;