Purchasing.Vendor (table)

From dbscript Online Help

Contents 1 wikibot 1.1 Columns 1.2 Primary Key 1.3 Indexes 1.4 Check Constraints 1.5 Detail Tables 1.6 Triggers 1.7 References 1.8 Dependencies 2 automatically generated

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

automatically generated

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

Column	Data Type	Nullable	Default	Description / PK / Index
VendorID	int	not null		Primary key for Vendor records. PK_Vendor_VendorID
AccountNumber	AccountNumber	not null		Vendor account (identification) number. AK_Vendor_AccountNumber
Name	Name	not null		Company name.
CreditRating	tinyint	not null		1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatus	Flag	not null	(1)	0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlag	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.

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

Triggers	Type
dVendor	INSTEAD OF DELETE

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

Reference Type	Object Type	Referencing Object
Select	View	Purchasing.vVendor