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

From dbscript Online Help
Jump to: navigation, search
Line 12: Line 12:
  
  
 +
=== 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 18: Line 19:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''Description / PK / Index
+
| '''Description
 
 
 
|- valign="top"
 
|- valign="top"
Line 25: Line 26:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key. Foreign key to SalesOrderHeader.SalesOrderID.<br />PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
+
| Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
 
|- valign="top"
 
|- valign="top"
 
| SalesOrderDetailID
 
| SalesOrderDetailID
Line 31: Line 32:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key. One incremental unique number per product sold.<br />PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
+
| Primary key. One incremental unique number per product sold.
 
|- valign="top"
 
|- valign="top"
 
| CarrierTrackingNumber
 
| CarrierTrackingNumber
Line 37: Line 38:
 
| null
 
| null
 
|  
 
|  
−
| Shipment tracking number supplied by the shipper.<br />
+
| Shipment tracking number supplied by the shipper.
 
|- valign="top"
 
|- valign="top"
 
| OrderQty
 
| OrderQty
Line 43: Line 44:
 
| not null
 
| not null
 
|  
 
|  
−
| Quantity ordered per product.<br />
+
| Quantity ordered per product.
 
|- valign="top"
 
|- valign="top"
 
| ProductID
 
| ProductID
Line 49: Line 50:
 
| not null
 
| not null
 
|  
 
|  
−
| Product sold to customer. Foreign key to Product.ProductID.<br />IX_SalesOrderDetail_ProductID
+
| Product sold to customer. Foreign key to Product.ProductID.
 
|- valign="top"
 
|- valign="top"
 
| SpecialOfferID
 
| SpecialOfferID
Line 55: Line 56:
 
| not null
 
| not null
 
|  
 
|  
−
| Promotional code. Foreign key to SpecialOffer.SpecialOfferID.<br />
+
| Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
 
|- valign="top"
 
|- valign="top"
 
| UnitPrice
 
| UnitPrice
Line 61: Line 62:
 
| not null
 
| not null
 
|  
 
|  
−
| Selling price of a single product.<br />
+
| Selling price of a single product.
 
|- valign="top"
 
|- valign="top"
 
| UnitPriceDiscount
 
| UnitPriceDiscount
Line 67: Line 68:
 
| not null
 
| not null
 
| ((0.0))
 
| ((0.0))
−
| Discount amount.<br />
+
| Discount amount.
 
|- valign="top"
 
|- valign="top"
 
| LineTotal
 
| LineTotal
Line 73: Line 74:
 
|  
 
|  
 
|  
 
|  
−
| Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.<br />
+
| Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
 
|- valign="top"
 
|- valign="top"
 
| rowguid
 
| rowguid
Line 79: Line 80:
 
| not null
 
| not null
 
| (newid())
 
| (newid())
−
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.<br />AK_SalesOrderDetail_rowguid
+
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 85: Line 86:
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
| Date and time the record was last updated.<br />
+
| 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_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
 +
| SalesOrderID, SalesOrderDetailID
 +
|}
  
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|-
 +
| AK_SalesOrderDetail_rowguid
 +
| Unique
 +
| rowguid
 +
|-
 +
| IX_SalesOrderDetail_ProductID
 +
|
 +
| ProductID
 +
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''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 ===
 
{| 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 106: Line 154:
 
|}
 
|}
  
−
 
+
=== 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"
Line 117: Line 165:
 
|}
 
|}
  
 +
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"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 127: Line 258:
 
| '''Child Object
 
| '''Child Object
  
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Sales_(schema)|Sales]]
 +
 +
|
 +
|
 
|-
 
|-
 
| Insert
 
| Insert
Line 178: Line 316:
 
|}
 
|}
  
−
 
+
=== Dependencies ===
 
{| 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 196: Line 334:
 
| iduSalesOrderDetail
 
| iduSalesOrderDetail
 
|}
 
|}
−
 
  
  

Revision as of 00:05, 3 February 2010

wikibot

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


Columns

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

Primary Key Columns
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID SalesOrderID, SalesOrderDetailID

Indexes

Index Type Columns
AK_SalesOrderDetail_rowguid Unique rowguid
IX_SalesOrderDetail_ProductID ProductID

Check Constraints

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

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

Triggers

Triggers Type
iduSalesOrderDetail ON INSERT UPDATE DELETE

Trigger iduSalesOrderDetail 

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

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

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


automatically generated

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=2111"