Difference between revisions of "Purchasing.Vendor (table)"
| Line 2: | Line 2: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| − | |- | + | |- valign="top" |
| '''Table | | '''Table | ||
| Purchasing.Vendor | | Purchasing.Vendor | ||
| Line 8: | Line 8: | ||
| '''Description | | '''Description | ||
| Companies from whom Adventure Works Cycles purchases parts or other goods. | | Companies from whom Adventure Works Cycles purchases parts or other goods. | ||
| − | |||
|} | |} | ||
| − | |||
=== Columns === | === Columns === | ||
| Line 20: | Line 18: | ||
| '''Default | | '''Default | ||
| '''Description | | '''Description | ||
| − | + | ||
|- valign="top" | |- valign="top" | ||
| VendorID | | VendorID | ||
| Line 76: | Line 74: | ||
| '''Primary Key | | '''Primary Key | ||
| '''Columns | | '''Columns | ||
| − | |- | + | |- valign="top" |
| PK_Vendor_VendorID | | PK_Vendor_VendorID | ||
| VendorID | | VendorID | ||
| Line 87: | Line 85: | ||
| '''Type | | '''Type | ||
| '''Columns | | '''Columns | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| AK_Vendor_AccountNumber | | AK_Vendor_AccountNumber | ||
| Unique | | Unique | ||
| Line 100: | Line 98: | ||
| '''Expression | | '''Expression | ||
| '''Description | | '''Description | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| CK_Vendor_CreditRating | | CK_Vendor_CreditRating | ||
| ([CreditRating]>=(1) AND [CreditRating]<=(5)) | | ([CreditRating]>=(1) AND [CreditRating]<=(5)) | ||
| Line 113: | Line 111: | ||
| '''Column | | '''Column | ||
| '''Referencing Column | | '''Referencing Column | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| [[Purchasing.ProductVendor_(table)|Purchasing.ProductVendor]] | | [[Purchasing.ProductVendor_(table)|Purchasing.ProductVendor]] | ||
| VendorID | | VendorID | ||
| VendorID | | VendorID | ||
| − | |- | + | |- valign="top" |
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | | [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]] | ||
| VendorID | | VendorID | ||
| VendorID | | VendorID | ||
| − | |- | + | |- valign="top" |
| [[Purchasing.VendorAddress_(table)|Purchasing.VendorAddress]] | | [[Purchasing.VendorAddress_(table)|Purchasing.VendorAddress]] | ||
| VendorID | | VendorID | ||
| VendorID | | VendorID | ||
| − | |- | + | |- valign="top" |
| [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]] | | [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]] | ||
| VendorID | | VendorID | ||
| Line 135: | Line 133: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
| − | | ''' | + | | '''Trigger |
| '''Type | | '''Type | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| dVendor | | dVendor | ||
| INSTEAD OF DELETE | | INSTEAD OF DELETE | ||
|} | |} | ||
| − | + | ==== Trigger dVendor ==== | |
<pre> | <pre> | ||
CREATE TRIGGER [Purchasing].[dVendor] ON [Purchasing].[Vendor] | CREATE TRIGGER [Purchasing].[dVendor] ON [Purchasing].[Vendor] | ||
| Line 188: | Line 186: | ||
END; | END; | ||
</pre> | </pre> | ||
| − | + | ||
=== References === | === References === | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| Line 199: | Line 197: | ||
| '''Child Object | | '''Child Object | ||
| − | |- | + | |- valign="top" |
| Data Type | | Data Type | ||
| Type | | Type | ||
| Line 206: | Line 204: | ||
| | | | ||
| | | | ||
| − | |- | + | |- valign="top" |
| Data Type | | Data Type | ||
| Type | | Type | ||
| Line 213: | Line 211: | ||
| | | | ||
| | | | ||
| − | |- | + | |- valign="top" |
| Data Type | | Data Type | ||
| Type | | Type | ||
| Line 220: | Line 218: | ||
| | | | ||
| | | | ||
| − | |- | + | |- valign="top" |
| Schema | | Schema | ||
| Schema | | Schema | ||
| Line 227: | Line 225: | ||
| | | | ||
| | | | ||
| − | |- | + | |- valign="top" |
| Execute | | Execute | ||
| Procedure | | Procedure | ||
| Line 234: | Line 232: | ||
| Trigger | | Trigger | ||
| dVendor | | dVendor | ||
| − | |- | + | |- valign="top" |
| Execute | | Execute | ||
| Procedure | | Procedure | ||
| Line 250: | Line 248: | ||
| '''Referencing Object | | '''Referencing Object | ||
| − | |- | + | |- valign="top" |
| Select | | Select | ||
| View | | View | ||
Latest revision as of 23:17, 23 June 2010
Contents
wikibot[edit]
| Table | Purchasing.Vendor |
| Description | Companies from whom Adventure Works Cycles purchases parts or other goods. |
Columns[edit]
| 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[edit]
| Primary Key | Columns |
| PK_Vendor_VendorID | VendorID |
Indexes[edit]
| Index | Type | Columns |
| AK_Vendor_AccountNumber | Unique | AccountNumber |
Check Constraints[edit]
| Check Constraint | Expression | Description |
| CK_Vendor_CreditRating | ([CreditRating]>=(1) AND [CreditRating]<=(5)) | Check constraint [CreditRating] BETWEEN (1) AND (5) |
Detail Tables[edit]
| Detail Table | Column | Referencing Column |
| Purchasing.ProductVendor | VendorID | VendorID |
| Purchasing.PurchaseOrderHeader | VendorID | VendorID |
| Purchasing.VendorAddress | VendorID | VendorID |
| Purchasing.VendorContact | VendorID | VendorID |
Triggers[edit]
| Trigger | Type |
| dVendor | INSTEAD OF DELETE |
Trigger dVendor[edit]
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[edit]
| 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[edit]
| Reference Type | Object Type | Referencing Object |
| Select | View | Purchasing.vVendor |
automatically generated[edit]
| 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 |
