Editing Purchasing.Vendor (table)

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''Table
| Purchasing.Vendor
|- valign="top"
| '''Description
| Companies from whom Adventure Works Cycles purchases parts or other goods.
|-
|}


=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description
		
|- valign="top"
| VendorID
| int
| not null
| 
| Primary key for Vendor records.
|- valign="top"
| AccountNumber
| dbo.AccountNumber
| not null
| 
| Vendor account (identification) number.
|- valign="top"
| Name
| dbo.Name
| not null
| 
| Company name.
|- valign="top"
| CreditRating
| tinyint
| not null
| 
| 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
|- valign="top"
| PreferredVendorStatus
| dbo.Flag
| not null
| ((1))
| 0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
|- valign="top"
| ActiveFlag
| dbo.Flag
| not null
| ((1))
| 0 = Vendor no longer used. 1 = Vendor is actively used.
|- valign="top"
| PurchasingWebServiceURL
| nvarchar(1024)
| null
| 
| Vendor URL.
|- valign="top"
| ModifiedDate
| datetime
| not null
| (getdate())
| Date and time the record was last updated.
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key
| '''Columns
|-
| PK_Vendor_VendorID
| VendorID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index
| '''Type
| '''Columns
		
|-
| AK_Vendor_AccountNumber
| Unique
| AccountNumber
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint
| '''Expression
| '''Description
		
|-
| CK_Vendor_CreditRating
| ([CreditRating]>=(1) AND [CreditRating]<=(5))
| Check constraint [CreditRating] BETWEEN (1) AND (5)
|}

=== Detail Tables ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column
		
|-
| [[Purchasing.ProductVendor_(table)|Purchasing.ProductVendor]]
| VendorID
| VendorID
|-
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
| VendorID
| VendorID
|-
| [[Purchasing.VendorAddress_(table)|Purchasing.VendorAddress]]
| VendorID
| VendorID
|-
| [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]]
| VendorID
| VendorID
|}

=== Triggers ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| dVendor
| INSTEAD OF DELETE
|}

	Trigger dVendor
<pre>
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;
</pre>
		
=== References ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Dependency Type
| '''Object Type
| '''Referenced Object

| '''Child Type
| '''Child Object

|-
| Data Type
| Type
| [[dbo.AccountNumber_(type)|dbo.AccountNumber]]

| 
| 
|-
| Data Type
| Type
| [[dbo.Flag_(type)|dbo.Flag]]

| 
| 
|-
| Data Type
| Type
| [[dbo.Name_(type)|dbo.Name]]

| 
| 
|-
| Schema
| Schema
| [[Purchasing_(schema)|Purchasing]]

| 
| 
|-
| Execute
| Procedure
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]

| Trigger
| dVendor
|-
| Execute
| Procedure
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]

| Trigger
| dVendor
|}

=== Dependencies ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Reference Type
| '''Object Type
| '''Referencing Object

|-
| Select
| View
| [[Purchasing.vVendor_(view)|Purchasing.vVendor]]

|}


== automatically generated ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''Table
| Purchasing.Vendor
|- valign="top"
| '''Description
| Companies from whom Adventure Works Cycles purchases parts or other goods.
|-
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description / PK / Index
		
|- valign="top"
| VendorID
| int
| not null
| 
| Primary key for Vendor records.<br />PK_Vendor_VendorID
|- valign="top"
| AccountNumber
| AccountNumber
| not null
| 
| Vendor account (identification) number.<br />AK_Vendor_AccountNumber
|- valign="top"
| Name
| Name
| not null
| 
| Company name.<br />
|- valign="top"
| CreditRating
| tinyint
| not null
| 
| 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average<br />
|- valign="top"
| PreferredVendorStatus
| Flag
| not null
| (1)
| 0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.<br />
|- valign="top"
| ActiveFlag
| Flag
| not null
| (1)
| 0 = Vendor no longer used. 1 = Vendor is actively used.<br />
|- valign="top"
| PurchasingWebServiceURL
| nvarchar(1024)
| null
| 
| Vendor URL.<br />
|- valign="top"
| ModifiedDate
| datetime
| not null
| (GETDATE())
| Date and time the record was last updated.<br />
|}



{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Detail Table
| '''Column
| '''Referencing Column
		
|-
| [[Purchasing.ProductVendor_(table)|Purchasing.ProductVendor]]
| VendorID
| VendorID
|-
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
| VendorID
| VendorID
|-
| [[Purchasing.VendorAddress_(table)|Purchasing.VendorAddress]]
| VendorID
| VendorID
|-
| [[Purchasing.VendorContact_(table)|Purchasing.VendorContact]]
| VendorID
| VendorID
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| dVendor
| INSTEAD OF DELETE
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Dependency Type
| '''Object Type
| '''Referenced Object

| '''Child Type
| '''Child Object

|-
| Data Type
| Type
| [[dbo.AccountNumber_(type)|dbo.AccountNumber]]

| 
| 
|-
| Data Type
| Type
| [[dbo.Flag_(type)|dbo.Flag]]

| 
| 
|-
| Data Type
| Type
| [[dbo.Name_(type)|dbo.Name]]

| 
| 
|-
| Schema
| Schema
| [[Purchasing_(schema)|Purchasing]]

| 
| 
|-
| Execute
| Procedure
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]

| Trigger
| dVendor
|-
| Execute
| Procedure
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]

| Trigger
| dVendor
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Reference Type
| '''Object Type
| '''Referencing Object

|-
| Select
| View
| [[Purchasing.vVendor_(view)|Purchasing.vVendor]]

|}