Description
Sale discounts lookup table.
Properties
Creation Date | 27/10/2017 14:33 |
File Group | PRIMARY |
Text File Group | |
System Object | |
Published for Replication | |
Rows | 16 |
Data Space Used | 8.00 KB |
Index Space Used | 24.00 KB |
Columns
| Column Name | Description | Datatype | Length | Allow Nulls | Default | Formula |
| SpecialOfferID | Primary key for SpecialOffer records. | Integer | 4 | | | |
| Description | Discount description. | VarWChar | 255 | | | |
| DiscountPct | Discount precentage. | Currency | 8 | | ((0.00)) | |
| Type | Discount type category. | VarWChar | 50 | | | |
| Category | Group the discount applies to such as Reseller or Customer. | VarWChar | 50 | | | |
| StartDate | Discount start date. | DBTimeStamp | 4 | | | |
| EndDate | Discount end date. | DBTimeStamp | 4 | | | |
| MinQty | Minimum discount percent allowed. | Integer | 4 | | ((0)) | |
| MaxQty | Maximum discount percent allowed. | Integer | 4 | | | |
| rowguid | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. | GUID | 16 | | (newid()) | |
| ModifiedDate | Date and time the record was last updated. | DBTimeStamp | 4 | | (getdate()) | |
Indexes
Check Constraints
Name | Description | Expression |
CK_SpecialOffer_DiscountPct | Check constraint [DiscountPct] >= (0.00) | ([DiscountPct]>=(0.00)) |
CK_SpecialOffer_EndDate | Check constraint [EndDate] >= [StartDate] | ([EndDate]>=[StartDate]) |
CK_SpecialOffer_MaxQty | Check constraint [MaxQty] >= (0) | ([MaxQty]>=(0)) |
CK_SpecialOffer_MinQty | Check constraint [MinQty] >= (0) | ([MinQty]>=(0)) |
Objects that depend on Sales.SpecialOffer
| Database Object | Object Type | Description | Dep Level |
| iduSalesOrderDetail | Trigger | AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column. | 3 |
| Sales.SalesOrderDetail | Table | Individual products associated with a specific sales order. See SalesOrderHeader. | 2 |
| Sales.SpecialOfferProduct | Table | Cross-reference table mapping products to special offer discounts. | 1 |
SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SpecialOffer](
[SpecialOfferID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DiscountPct] [smallmoney] NOT NULL,
[Type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Category] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[MinQty] [int] NOT NULL,
[MaxQty] [int] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_SpecialOffer_SpecialOfferID] PRIMARY KEY CLUSTERED
(
[SpecialOfferID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [Sales].[SpecialOffer] ADD CONSTRAINT [DF_SpecialOffer_DiscountPct] DEFAULT ((0.00)) FOR [DiscountPct]
ALTER TABLE [Sales].[SpecialOffer] ADD CONSTRAINT [DF_SpecialOffer_MinQty] DEFAULT ((0)) FOR [MinQty]
ALTER TABLE [Sales].[SpecialOffer] ADD CONSTRAINT [DF_SpecialOffer_rowguid] DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SpecialOffer] ADD CONSTRAINT [DF_SpecialOffer_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SpecialOffer] WITH CHECK ADD CONSTRAINT [CK_SpecialOffer_DiscountPct] CHECK (([DiscountPct]>=(0.00)))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_DiscountPct]
ALTER TABLE [Sales].[SpecialOffer] WITH CHECK ADD CONSTRAINT [CK_SpecialOffer_EndDate] CHECK (([EndDate]>=[StartDate]))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_EndDate]
ALTER TABLE [Sales].[SpecialOffer] WITH CHECK ADD CONSTRAINT [CK_SpecialOffer_MaxQty] CHECK (([MaxQty]>=(0)))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_MaxQty]
ALTER TABLE [Sales].[SpecialOffer] WITH CHECK ADD CONSTRAINT [CK_SpecialOffer_MinQty] CHECK (([MinQty]>=(0)))
ALTER TABLE [Sales].[SpecialOffer] CHECK CONSTRAINT [CK_SpecialOffer_MinQty]
|
See Also