Difference between revisions of "Sales.SalesOrderDetail (table)"

From dbscript Online Help
Jump to: navigation, search
 
(2 intermediate revisions by the same user not shown)
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 15: Line 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| SalesOrderID
 
| SalesOrderID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
+
| Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
−
|
+
|- valign="top"
−
|-
 
 
| SalesOrderDetailID
 
| SalesOrderDetailID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
+
| Primary key. One incremental unique number per product sold.
−
|
+
|- 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.
−
| IX_SalesOrderDetail_ProductID
+
|- valign="top"
−
|-
 
 
| 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.
−
| AK_SalesOrderDetail_rowguid
+
|- valign="top"
−
|-
 
 
| 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"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 104: Line 141:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 
| SalesOrderID
 
| SalesOrderID
 
| SalesOrderID
 
| SalesOrderID
−
|-
+
|- valign="top"
 
| [[Sales.SpecialOfferProduct_(table)|Sales.SpecialOfferProduct]]
 
| [[Sales.SpecialOfferProduct_(table)|Sales.SpecialOfferProduct]]
 
| SpecialOfferID
 
| SpecialOfferID
Line 115: Line 152:
 
|}
 
|}
  
−
 
+
=== Triggers ===
 
{| 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"
−
| '''Triggers
+
| '''Trigger
 
| '''Type
 
| '''Type
−
+
 
−
|-
+
|- valign="top"
 
| iduSalesOrderDetail
 
| iduSalesOrderDetail
 
| ON INSERT UPDATE DELETE
 
| 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 ==
 
== automatically generated ==
  

Latest revision as of 23:17, 23 June 2010

wikibot[edit]

Table Sales.SalesOrderDetail
Description Individual products associated with a specific sales order. See SalesOrderHeader.

Columns[edit]

Column Data Type Nullable Default Description
SalesOrderID int not null Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesOrderDetailID int not null Primary key. One incremental unique number per product sold.
CarrierTrackingNumber nvarchar(25) null Shipment tracking number supplied by the shipper.
OrderQty smallint not null Quantity ordered per product.
ProductID int not null Product sold to customer. Foreign key to Product.ProductID.
SpecialOfferID int not null Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPrice money not null Selling price of a single product.
UnitPriceDiscount money not null ((0.0)) Discount amount.
LineTotal Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguid uniqueidentifier not null (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.

Primary Key[edit]

Primary Key Columns
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID SalesOrderID, SalesOrderDetailID

Indexes[edit]

Index Type Columns
AK_SalesOrderDetail_rowguid Unique rowguid
IX_SalesOrderDetail_ProductID ProductID

Check Constraints[edit]

Check Constraint Expression Description
CK_SalesOrderDetail_OrderQty ([OrderQty]>(0)) Check constraint [OrderQty] > (0)
CK_SalesOrderDetail_UnitPrice ([UnitPrice]>=(0.00)) Check constraint [UnitPrice] >= (0.00)
CK_SalesOrderDetail_UnitPriceDiscount ([UnitPriceDiscount]>=(0.00)) Check constraint [UnitPriceDiscount] >= (0.00)

Foreign Keys[edit]

Relation Column Referenced Column
Sales.SalesOrderHeader SalesOrderID SalesOrderID
Sales.SpecialOfferProduct SpecialOfferID SpecialOfferID

Triggers[edit]

Trigger Type
iduSalesOrderDetail ON INSERT UPDATE DELETE

Trigger iduSalesOrderDetail[edit]

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;

References[edit]

Dependency Type Object Type Referenced Object Child Type Child Object
Schema Schema Sales
Insert Table Production.TransactionHistory Trigger iduSalesOrderDetail
Update Table Sales.Individual Trigger iduSalesOrderDetail
Select Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail
Select Table Sales.SalesOrderHeader Trigger iduSalesOrderDetail
Update Table Sales.SalesOrderHeader Trigger iduSalesOrderDetail
Execute Procedure dbo.uspLogError Trigger iduSalesOrderDetail
Execute Procedure dbo.uspPrintError Trigger iduSalesOrderDetail

Dependencies[edit]

Reference Type Object Type Referencing Object Child Type Child Object
Select Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail


automatically generated[edit]

Table Sales.SalesOrderDetail
Description Individual products associated with a specific sales order. See SalesOrderHeader.


Column Data Type Nullable Default Description / PK / Index
SalesOrderID int not null Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
SalesOrderDetailID int not null Primary key. One incremental unique number per product sold.
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
CarrierTrackingNumber nvarchar(25) null Shipment tracking number supplied by the shipper.
OrderQty smallint not null Quantity ordered per product.
ProductID int not null Product sold to customer. Foreign key to Product.ProductID.
IX_SalesOrderDetail_ProductID
SpecialOfferID int not null Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPrice money not null Selling price of a single product.
UnitPriceDiscount money not null (0.0) Discount amount.
LineTotal Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguid uniqueidentifier not null (NEWID()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
AK_SalesOrderDetail_rowguid
ModifiedDate datetime not null (GETDATE()) Date and time the record was last updated.


Relation Column Referenced Column
Sales.SalesOrderHeader SalesOrderID SalesOrderID
Sales.SpecialOfferProduct SpecialOfferID SpecialOfferID


Triggers Type
iduSalesOrderDetail ON INSERT UPDATE DELETE


Dependency Type Object Type Referenced Object Child Type Child Object
Schema Schema Sales
Insert Table Production.TransactionHistory Trigger iduSalesOrderDetail
Update Table Sales.Individual Trigger iduSalesOrderDetail
Select Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail
Select Table Sales.SalesOrderHeader Trigger iduSalesOrderDetail
Update Table Sales.SalesOrderHeader Trigger iduSalesOrderDetail
Execute Procedure dbo.uspLogError Trigger iduSalesOrderDetail
Execute Procedure dbo.uspPrintError Trigger iduSalesOrderDetail


Reference Type Object Type Referencing Object Child Type Child Object
Select Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.SalesOrderDetail_(table)&oldid=2668"