Sales Store (table)

wikibot ¶

Table	Sales.Store
Description	Customers (resellers) of Adventure Works products.

Columns ¶

Column	Data Type	Nullable	Default	Description
CustomerID	int	not null		Primary key. Foreign key to Customer.CustomerID.
Name	dbo.Name	not null		Name of the store.
SalesPersonID	int	null		ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID.
Demographics	xml	null		Demographic informationg about the store such as the number of employees, annual sales and store type.
rowguid	uniqueidentifier	not null	(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	not null	(getdate())	Date and time the record was last updated.

Primary Key ¶

Primary Key	Columns
PK_Store_CustomerID	CustomerID

Indexes ¶

Index	Type	Columns
AK_Store_rowguid	Unique	rowguid
IX_Store_SalesPersonID		SalesPersonID
PXML_Store_Demographics		Demographics

Foreign Keys ¶

Relation	Column	Referenced Column
Sales.Customer	CustomerID	CustomerID
Sales.SalesPerson	SalesPersonID	SalesPersonID

Detail Tables ¶

Detail Table	Column	Referencing Column
Sales.StoreContact	CustomerID	CustomerID

Triggers ¶

Trigger	Type
iStore	ON INSERT

Trigger iStore ¶

CREATE 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
        -- Only allow the Customer to be a Store OR Individual
        IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Individual] 
            ON inserted.[CustomerID] = [Sales].[Individual].[CustomerID]) 
        BEGIN
            -- Rollback any active or uncommittable transactions
            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION;
            END
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

References ¶

Dependency Type	Object Type	Referenced Object	Child Type	Child Object
Data Type	Type	dbo.Name
Schema	Schema	Sales
Data Type	XML Schema Collection	Sales.StoreSurveySchemaCollection
Select	Table	Sales.Individual	Trigger	iStore
Execute	Procedure	dbo.uspLogError	Trigger	iStore
Execute	Procedure	dbo.uspPrintError	Trigger	iStore

Dependencies ¶

Reference Type	Object Type	Referencing Object	Child Type	Child Object
Select	View	Sales.vStoreWithDemographics
Select	Table	Sales.Individual	Trigger	iuIndividual