AdventureWorks - Purchasing.Vendor

AdventureWorks

Table Name:	Purchasing.Vendor
Description:	Companies from whom Adventure Works Cycles purchases parts or other goods.
Primary Keys:	VendorID

Field	Type	Defaults	Nulls?	Comments
VendorID	int - Identity	None	No	Primary key for Vendor records.
AccountNumber	nvarchar (15)	None	No	Vendor account (identification) number.
Name	nvarchar (50)	None	No	Company name.
CreditRating	tinyint	None	No	1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatus	bit	((1))	No	0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlag	bit	((1))	No	0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURL	nvarchar (1024)	None	Yes	Vendor URL.
ModifiedDate	datetime	(getdate())	No	Date and time the record was last updated.

Index	Clustered?	Unique?	Fields
PK_Vendor_VendorID	Yes	Yes	VendorID
AK_Vendor_AccountNumber	No	Yes	AccountNumber

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

No internal foreign key constraints

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_ProductVendor_Vendor_VendorID	Purchasing.ProductVendor	VendorID
FK_PurchaseOrderHeader_Vendor_VendorID	Purchasing.PurchaseOrderHeader	VendorID
FK_VendorAddress_Vendor_VendorID	Purchasing.VendorAddress	VendorID
FK_VendorContact_Vendor_VendorID	Purchasing.VendorContact	VendorID

Trigger	Text
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. 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;

Trigger

Text

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