Latest revision |
Your text |
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 |
| | Sales.SalesOrderDetail | | | Sales.SalesOrderDetail |
− | |- valign="top" | + | |- |
− | | '''Description
| |
− | | Individual products associated with a specific sales order. See SalesOrderHeader.
| |
| |} | | |} |
| | | |
− | === Columns ===
| + | |
| {| 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" |
Line 17: |
Line 15: |
| | '''Nullable | | | '''Nullable |
| | '''Default | | | '''Default |
− | | '''Description | + | | '''PK |
− | | + | | '''Index |
− | |- valign="top" | + | |
| + | |- |
| | SalesOrderID | | | SalesOrderID |
| | int | | | int |
| | not null | | | not null |
| | | | | |
− | | Primary key. Foreign key to SalesOrderHeader.SalesOrderID. | + | | PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID |
− | |- valign="top" | + | | |
| + | |- |
| | SalesOrderDetailID | | | SalesOrderDetailID |
| | int | | | int |
| | not null | | | not null |
| | | | | |
− | | Primary key. One incremental unique number per product sold. | + | | PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID |
− | |- valign="top" | + | | |
| + | |- |
| | CarrierTrackingNumber | | | CarrierTrackingNumber |
| | nvarchar(25) | | | nvarchar(25) |
| | null | | | null |
| | | | | |
− | | Shipment tracking number supplied by the shipper. | + | | |
− | |- valign="top" | + | | |
| + | |- |
| | OrderQty | | | OrderQty |
| | smallint | | | smallint |
| | not null | | | not null |
| | | | | |
− | | Quantity ordered per product. | + | | |
− | |- valign="top" | + | | |
| + | |- |
| | ProductID | | | ProductID |
| | int | | | int |
| | not null | | | not null |
| | | | | |
− | | Product sold to customer. Foreign key to Product.ProductID. | + | | |
− | |- valign="top" | + | | IX_SalesOrderDetail_ProductID |
| + | |- |
| | SpecialOfferID | | | SpecialOfferID |
| | int | | | int |
| | not null | | | not null |
| | | | | |
− | | Promotional code. Foreign key to SpecialOffer.SpecialOfferID. | + | | |
− | |- valign="top" | + | | |
| + | |- |
| | UnitPrice | | | UnitPrice |
| | money | | | money |
| | not null | | | not null |
| | | | | |
− | | Selling price of a single product. | + | | |
− | |- valign="top" | + | | |
| + | |- |
| | UnitPriceDiscount | | | UnitPriceDiscount |
| | money | | | money |
| | not null | | | not null |
| | ((0.0)) | | | ((0.0)) |
− | | Discount amount. | + | | |
− | |- valign="top" | + | | |
| + | |- |
| | LineTotal | | | LineTotal |
| | | | | |
| | | | | |
| | | | | |
− | | Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty. | + | | |
− | |- valign="top" | + | | |
| + | |- |
| | rowguid | | | rowguid |
| | uniqueidentifier | | | uniqueidentifier |
| | not null | | | not null |
| | (newid()) | | | (newid()) |
− | | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. | + | | |
− | |- valign="top" | + | | AK_SalesOrderDetail_rowguid |
| + | |- |
| | ModifiedDate | | | ModifiedDate |
| | datetime | | | datetime |
| | not null | | | not null |
| | (getdate()) | | | (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
| |
− | |- valign="top"
| |
− | | PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
| |
− | | SalesOrderID, SalesOrderDetailID
| |
− | |}
| |
− |
| |
− | === Indexes ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Index
| |
− | | '''Type
| |
− | | '''Columns
| |
− |
| |
− | |- valign="top"
| |
− | | AK_SalesOrderDetail_rowguid
| |
− | | Unique
| |
− | | rowguid
| |
− | |- valign="top"
| |
− | | IX_SalesOrderDetail_ProductID
| |
| | | | | |
− | | ProductID
| |
− | |}
| |
− |
| |
− | === Check Constraints ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Check Constraint
| |
− | | '''Expression
| |
− | | '''Description
| |
− |
| |
− | |- valign="top"
| |
− | | CK_SalesOrderDetail_OrderQty
| |
− | | ([OrderQty]>(0))
| |
− | | Check constraint [OrderQty] > (0)
| |
− | |- valign="top"
| |
− | | CK_SalesOrderDetail_UnitPrice
| |
− | | ([UnitPrice]>=(0.00))
| |
− | | Check constraint [UnitPrice] >= (0.00)
| |
− | |- valign="top"
| |
− | | CK_SalesOrderDetail_UnitPriceDiscount
| |
− | | ([UnitPriceDiscount]>=(0.00))
| |
− | | Check constraint [UnitPriceDiscount] >= (0.00)
| |
− | |}
| |
− |
| |
− | === Foreign Keys ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Relation
| |
− | | '''Column
| |
− | | '''Referenced Column
| |
− |
| |
− | |- valign="top"
| |
− | | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
| |
− | | SalesOrderID
| |
− | | SalesOrderID
| |
− | |- valign="top"
| |
− | | [[Sales.SpecialOfferProduct_(table)|Sales.SpecialOfferProduct]]
| |
− | | SpecialOfferID
| |
− | | SpecialOfferID
| |
− | |}
| |
− |
| |
− | === Triggers ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Trigger
| |
− | | '''Type
| |
− |
| |
− | |- valign="top"
| |
− | | iduSalesOrderDetail
| |
− | | ON INSERT UPDATE DELETE
| |
− | |}
| |
− |
| |
− | ==== Trigger iduSalesOrderDetail ====
| |
− | <pre>
| |
− | CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail]
| |
− | AFTER INSERT, DELETE, UPDATE AS
| |
− | BEGIN
| |
− | DECLARE @Count int;
| |
− |
| |
− | SET @Count = @@ROWCOUNT;
| |
− | IF @Count = 0
| |
− | RETURN;
| |
− |
| |
− | SET NOCOUNT ON;
| |
− |
| |
− | BEGIN TRY
| |
− | -- If inserting or updating these columns
| |
− | IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])
| |
− | -- Insert record into TransactionHistory
| |
− | BEGIN
| |
− | INSERT INTO [Production].[TransactionHistory]
| |
− | ([ProductID]
| |
− | ,[ReferenceOrderID]
| |
− | ,[ReferenceOrderLineID]
| |
− | ,[TransactionType]
| |
− | ,[TransactionDate]
| |
− | ,[Quantity]
| |
− | ,[ActualCost])
| |
− | SELECT
| |
− | inserted.[ProductID]
| |
− | ,inserted.[SalesOrderID]
| |
− | ,inserted.[SalesOrderDetailID]
| |
− | ,'S'
| |
− | ,GETDATE()
| |
− | ,inserted.[OrderQty]
| |
− | ,inserted.[UnitPrice]
| |
− | FROM inserted
| |
− | INNER JOIN [Sales].[SalesOrderHeader]
| |
− | ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];
| |
− |
| |
− | UPDATE [Sales].[Individual]
| |
− | SET [Demographics].modify('declare default element namespace
| |
− | "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
| |
− | replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
| |
− | with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')
| |
− | FROM inserted
| |
− | INNER JOIN [Sales].[SalesOrderHeader]
| |
− | ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]
| |
− | WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
| |
− | END;
| |
− |
| |
− | -- Update SubTotal in SalesOrderHeader record. Note that this causes the
| |
− | -- SalesOrderHeader trigger to fire which will update the RevisionNumber.
| |
− | UPDATE [Sales].[SalesOrderHeader]
| |
− | SET [Sales].[SalesOrderHeader].[SubTotal] =
| |
− | (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
| |
− | FROM [Sales].[SalesOrderDetail]
| |
− | WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
| |
− | WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);
| |
− |
| |
− | UPDATE [Sales].[Individual]
| |
− | SET [Demographics].modify('declare default element namespace
| |
− | "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
| |
− | replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
| |
− | with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")')
| |
− | FROM deleted
| |
− | INNER JOIN [Sales].[SalesOrderHeader]
| |
− | ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]
| |
− | WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
| |
− | 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
| |
− |
| |
− | |- valign="top"
| |
− | | Schema
| |
− | | Schema
| |
− | | [[Sales_(schema)|Sales]]
| |
− |
| |
| | | | | |
− | |
| |
− | |- valign="top"
| |
− | | Insert
| |
− | | Table
| |
− | | [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |- valign="top"
| |
− | | Update
| |
− | | Table
| |
− | | [[Sales.Individual_(table)|Sales.Individual]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |- valign="top"
| |
− | | Update
| |
− | | Table
| |
− | | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |- valign="top"
| |
− | | Execute
| |
− | | Procedure
| |
− | | [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |- valign="top"
| |
− | | Execute
| |
− | | Procedure
| |
− | | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |}
| |
− |
| |
− | === Dependencies ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Reference Type
| |
− | | '''Object Type
| |
− | | '''Referencing Object
| |
− |
| |
− | | '''Child Type
| |
− | | '''Child Object
| |
− |
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |}
| |
− |
| |
− |
| |
− | == automatically generated ==
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |-
| |
− | | '''Table
| |
− | | Sales.SalesOrderDetail
| |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Individual products associated with a specific sales order. See SalesOrderHeader.
| |
− | |-
| |
− | |}
| |
− |
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Column
| |
− | | '''Data Type
| |
− | | '''Nullable
| |
− | | '''Default
| |
− | | '''Description / PK / Index
| |
− |
| |
− | |- valign="top"
| |
− | | SalesOrderID
| |
− | | int
| |
− | | not null
| |
− | |
| |
− | | Primary key. Foreign key to SalesOrderHeader.SalesOrderID.<br />PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
| |
− | |- valign="top"
| |
− | | SalesOrderDetailID
| |
− | | int
| |
− | | not null
| |
− | |
| |
− | | Primary key. One incremental unique number per product sold.<br />PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
| |
− | |- valign="top"
| |
− | | CarrierTrackingNumber
| |
− | | nvarchar(25)
| |
− | | null
| |
− | |
| |
− | | Shipment tracking number supplied by the shipper.<br />
| |
− | |- valign="top"
| |
− | | OrderQty
| |
− | | smallint
| |
− | | not null
| |
− | |
| |
− | | Quantity ordered per product.<br />
| |
− | |- valign="top"
| |
− | | ProductID
| |
− | | int
| |
− | | not null
| |
− | |
| |
− | | Product sold to customer. Foreign key to Product.ProductID.<br />IX_SalesOrderDetail_ProductID
| |
− | |- valign="top"
| |
− | | SpecialOfferID
| |
− | | int
| |
− | | not null
| |
− | |
| |
− | | Promotional code. Foreign key to SpecialOffer.SpecialOfferID.<br />
| |
− | |- valign="top"
| |
− | | UnitPrice
| |
− | | money
| |
− | | not null
| |
− | |
| |
− | | Selling price of a single product.<br />
| |
− | |- valign="top"
| |
− | | UnitPriceDiscount
| |
− | | money
| |
− | | not null
| |
− | | (0.0)
| |
− | | Discount amount.<br />
| |
− | |- valign="top"
| |
− | | LineTotal
| |
− | |
| |
− | |
| |
− | |
| |
− | | Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.<br />
| |
− | |- valign="top"
| |
− | | rowguid
| |
− | | uniqueidentifier
| |
− | | not null
| |
− | | (NEWID())
| |
− | | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.<br />AK_SalesOrderDetail_rowguid
| |
− | |- valign="top"
| |
− | | ModifiedDate
| |
− | | datetime
| |
− | | not null
| |
− | | (GETDATE())
| |
− | | Date and time the record was last updated.<br />
| |
| |} | | |} |
| | | |
Line 450: |
Line 124: |
| | iduSalesOrderDetail | | | iduSalesOrderDetail |
| | ON INSERT UPDATE DELETE | | | ON INSERT UPDATE DELETE |
− | |}
| |
− |
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | | '''Child Type
| |
− | | '''Child Object
| |
− |
| |
− | |-
| |
− | | Schema
| |
− | | Schema
| |
− | | [[Sales_(schema)|Sales]]
| |
− |
| |
− | |
| |
− | |
| |
− | |-
| |
− | | Insert
| |
− | | Table
| |
− | | [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |-
| |
− | | Update
| |
− | | Table
| |
− | | [[Sales.Individual_(table)|Sales.Individual]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |-
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |-
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |-
| |
− | | Update
| |
− | | Table
| |
− | | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |-
| |
− | | Execute
| |
− | | Procedure
| |
− | | [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |-
| |
− | | Execute
| |
− | | Procedure
| |
− | | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
− | |}
| |
− |
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Reference Type
| |
− | | '''Object Type
| |
− | | '''Referencing Object
| |
− |
| |
− | | '''Child Type
| |
− | | '''Child Object
| |
− |
| |
− | |-
| |
− | | Select
| |
− | | Table
| |
− | | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
| |
− |
| |
− | | Trigger
| |
− | | iduSalesOrderDetail
| |
| |} | | |} |