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

Page History: Purchasing Vendor (table)

Compare Page Revisions

4 3 2 1 0 Current 4 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: 2010/02/11 09:06

wikibot ¶

Table	Purchasing.Vendor
Description	Companies from whom Adventure Works Cycles purchases parts or other goods.

Columns ¶

Column	Data Type	Nullable	Default	Description
VendorID	int	not null		Primary key for Vendor records.
AccountNumber	dbo.AccountNumber	not null		Vendor account (identification) number.
Name	dbo.Name	not null		Company name.
CreditRating	tinyint	not null		1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatus	dbo.Flag	not null	((1))	0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlag	dbo.Flag	not null	((1))	0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURL	nvarchar(1024)	null		Vendor URL.
ModifiedDate	datetime	not null	(getdate())	Date and time the record was last updated.

Primary Key ¶

Primary Key	Columns
PK_Vendor_VendorID	VendorID

Indexes ¶

Index	Type	Columns
AK_Vendor_AccountNumber	Unique	AccountNumber

Check Constraints ¶

Check Constraint	Expression	Description
CK_Vendor_CreditRating	(CreditRating>=(1) AND CreditRating<=(5))	Check constraint CreditRating BETWEEN (1) AND (5)

Detail Tables ¶

Detail Table	Column	Referencing Column
Purchasing.ProductVendor	VendorID	VendorID
Purchasing.PurchaseOrderHeader	VendorID	VendorID
Purchasing.VendorAddress	VendorID	VendorID
Purchasing.VendorContact	VendorID	VendorID

Triggers ¶

Triggers	Type
dVendor	INSTEAD OF DELETE

Trigger dVendor

CREATE TRIGGER [Purchasing].[dVendor] ON [Purchasing].[Vendor] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        DECLARE @DeleteCount int;

        SELECT @DeleteCount = COUNT(*) FROM deleted;
        IF @DeleteCount > 0 
        BEGIN
            RAISERROR
                (N'Vendors cannot be deleted. They can only be marked as not active.', -- Message
                10, -- Severity.
                1); -- State.

        -- 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.AccountNumber
Data Type	Type	dbo.Flag
Data Type	Type	dbo.Name
Schema	Schema	Purchasing
Execute	Procedure	dbo.uspLogError	Trigger	dVendor
Execute	Procedure	dbo.uspPrintError	Trigger	dVendor

Dependencies ¶

Reference Type	Object Type	Referencing Object
Select	View	Purchasing.vVendor