• lsrep17 Documentation
  • Tables [Purchasing].[Vendor]
    Companies from whom Adventure Works Cycles purchases parts or other goods.
    PropertyValue
    CollationSQL_Latin1_General_CP1_CI_AS
    Row Count (~)104
    Created13:14:19 14 marca 2012
    Last Modified13:14:55 14 marca 2012
    KeyNameData TypeMax Length (Bytes)Allow NullsDefaultDescription
    Cluster Primary Key PK_Vendor_BusinessEntityID: BusinessEntityIDForeign Keys FK_Vendor_BusinessEntity_BusinessEntityID: [Person].[BusinessEntity].BusinessEntityIDBusinessEntityIDint4
    False
    Primary key for Vendor records.  Foreign key to BusinessEntity.BusinessEntityID
    Indexes AK_Vendor_AccountNumberAccountNumber[dbo].[AccountNumber]30
    False
    Vendor account (identification) number.
    Name[dbo].[Name]100
    False
    Company name.
    Check Constraints CK_Vendor_CreditRating : ([CreditRating]>=(1) AND [CreditRating]<=(5))CreditRatingtinyint1
    False
    1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
    PreferredVendorStatus[dbo].[Flag]1
    False
    ((1))0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
    ActiveFlag[dbo].[Flag]1
    False
    ((1))0 = Vendor no longer used. 1 = Vendor is actively used.
    PurchasingWebServiceURLnvarchar(1024)2048
    True
    Vendor URL.
    ModifiedDatedatetime8
    False
    (getdate())Date and time the record was last updated.
    KeyNameKey ColumnsUniqueDescription
    Cluster Primary Key PK_Vendor_BusinessEntityID: BusinessEntityIDPK_Vendor_BusinessEntityIDBusinessEntityID
    True
    Primary key (clustered) constraint
    AK_Vendor_AccountNumberAccountNumber
    True
    Unique nonclustered index.
    NameANSI Nulls OnQuoted Identifier OnOnNot For ReplicationDescription
    dVendor
    True
    True
    Instead Of Delete
    True
    INSTEAD OF DELETE trigger which keeps Vendors from being deleted.
    NameOn ColumnConstraintDescription
    CK_Vendor_CreditRatingCreditRating([CreditRating]>=(1) AND [CreditRating]<=(5))Check constraint [CreditRating] BETWEEN (1) AND (5)
    NameColumnsDescription
    FK_Vendor_BusinessEntity_BusinessEntityIDBusinessEntityID->[Person].[BusinessEntity].[BusinessEntityID]Foreign key constraint referencing BusinessEntity.BusinessEntityID
    CREATE TABLE [Purchasing].[Vendor]
    (
    [BusinessEntityID] [int] NOT NULL,
    [AccountNumber] [dbo].[AccountNumber] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CreditRating] [tinyint] NOT NULL,
    [PreferredVendorStatus] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Vendor_PreferredVendorStatus] DEFAULT ((1)),
    [ActiveFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Vendor_ActiveFlag] DEFAULT ((1)),
    [PurchasingWebServiceURL] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Vendor_ModifiedDate] DEFAULT (getdate())
    ) ON [PRIMARY]
    GO

    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;
    GO
    ALTER TABLE [Purchasing].[Vendor] ADD CONSTRAINT [CK_Vendor_CreditRating] CHECK (([CreditRating]>=(1) AND [CreditRating]<=(5)))
    GO
    ALTER TABLE [Purchasing].[Vendor] ADD CONSTRAINT [PK_Vendor_BusinessEntityID] PRIMARY KEY CLUSTERED  ([BusinessEntityID]) ON [PRIMARY]
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [AK_Vendor_AccountNumber] ON [Purchasing].[Vendor] ([AccountNumber]) ON [PRIMARY]
    GO
    ALTER TABLE [Purchasing].[Vendor] ADD CONSTRAINT [FK_Vendor_BusinessEntity_BusinessEntityID] FOREIGN KEY ([BusinessEntityID]) REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Companies from whom Adventure Works Cycles purchases parts or other goods.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', NULL, NULL
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Vendor account (identification) number.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'AccountNumber'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'0 = Vendor no longer used. 1 = Vendor is actively used.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'ActiveFlag'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Primary key for Vendor records.  Foreign key to BusinessEntity.BusinessEntityID', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'BusinessEntityID'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'CreditRating'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Date and time the record was last updated.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'ModifiedDate'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Company name.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'Name'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'PreferredVendorStatus'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Vendor URL.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'COLUMN', N'PurchasingWebServiceURL'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [CreditRating] BETWEEN (1) AND (5)', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'CONSTRAINT', N'CK_Vendor_CreditRating'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 1 (TRUE)', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'CONSTRAINT', N'DF_Vendor_ActiveFlag'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of GETDATE()', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'CONSTRAINT', N'DF_Vendor_ModifiedDate'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 1 (TRUE)', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'CONSTRAINT', N'DF_Vendor_PreferredVendorStatus'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing BusinessEntity.BusinessEntityID', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'CONSTRAINT', N'FK_Vendor_BusinessEntity_BusinessEntityID'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'CONSTRAINT', N'PK_Vendor_BusinessEntityID'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Unique nonclustered index.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'INDEX', N'AK_Vendor_AccountNumber'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'Clustered index created by a primary key constraint.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'INDEX', N'PK_Vendor_BusinessEntityID'
    GO
    EXEC sp_addextendedproperty N'MS_Description', N'INSTEAD OF DELETE trigger which keeps Vendors from being deleted.', 'SCHEMA', N'Purchasing', 'TABLE', N'Vendor', 'TRIGGER', N'dVendor'
    GO