Purchasing Vendor (table)

wikibot

TablePurchasing.Vendor
DescriptionCompanies from whom Adventure Works Cycles purchases parts or other goods.

Columns

ColumnData TypeNullableDefaultDescription
VendorIDintnot null Primary key for Vendor records.
AccountNumberdbo.AccountNumbernot null Vendor account (identification) number.
Namedbo.Namenot null Company name.
CreditRatingtinyintnot null 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatusdbo.Flagnot null((1))0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlagdbo.Flagnot null((1))0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURLnvarchar(1024)null Vendor URL.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_Vendor_VendorIDVendorID

Indexes

IndexTypeColumns
AK_Vendor_AccountNumberUniqueAccountNumber

Check Constraints

Check ConstraintExpressionDescription
CK_Vendor_CreditRating(CreditRating>=(1) AND CreditRating<=(5))Check constraint CreditRating BETWEEN (1) AND (5)

Detail Tables

Detail TableColumnReferencing Column
Purchasing.ProductVendorVendorIDVendorID
Purchasing.PurchaseOrderHeaderVendorIDVendorID
Purchasing.VendorAddressVendorIDVendorID
Purchasing.VendorContactVendorIDVendorID

Triggers

TriggerType
dVendorINSTEAD 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 TypeObject TypeReferenced ObjectChild TypeChild Object
Data TypeTypedbo.AccountNumber  
Data TypeTypedbo.Flag  
Data TypeTypedbo.Name  
SchemaSchemaPurchasing  
ExecuteProceduredbo.uspLogErrorTriggerdVendor
ExecuteProceduredbo.uspPrintErrorTriggerdVendor

Dependencies

Reference TypeObject TypeReferencing Object
SelectViewPurchasing.vVendor