AdventureWorks

Basic Index   Expanded Index

Table Name:Purchasing.Vendor
Description:Companies from whom Adventure Works Cycles purchases parts or other goods.
Primary Keys:VendorID
FieldTypeDefaultsNulls?Comments
VendorIDint - IdentityNoneNoPrimary key for Vendor records.
AccountNumbernvarchar (15)NoneNoVendor account (identification) number.
Namenvarchar (50)NoneNoCompany name.
CreditRatingtinyintNoneNo1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatusbit((1))No0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlagbit((1))No0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURLnvarchar (1024)NoneYesVendor URL.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_Vendor_VendorIDYesYesVendorID
AK_Vendor_AccountNumberNoYesAccountNumber

Check ConstraintText
CK_Vendor_CreditRating([CreditRating]>=(1) AND [CreditRating]<=(5))

No internal foreign key constraints
Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_ProductVendor_Vendor_VendorIDPurchasing.ProductVendorVendorID
FK_PurchaseOrderHeader_Vendor_VendorIDPurchasing.PurchaseOrderHeaderVendorID
FK_VendorAddress_Vendor_VendorIDPurchasing.VendorAddressVendorID
FK_VendorContact_Vendor_VendorIDPurchasing.VendorContactVendorID

TriggerText
dVendorCREATE 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.
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;