Editing Sales.SalesOrderDetail (table)

== wikibot ==

{| 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.
|-
|}


=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description
		
|- valign="top"
| SalesOrderID
| int
| not null
| 
| Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
|- valign="top"
| SalesOrderDetailID
| int
| not null
| 
| Primary key. One incremental unique number per product sold.
|- valign="top"
| CarrierTrackingNumber
| nvarchar(25)
| null
| 
| Shipment tracking number supplied by the shipper.
|- valign="top"
| OrderQty
| smallint
| not null
| 
| Quantity ordered per product.
|- valign="top"
| ProductID
| int
| not null
| 
| Product sold to customer. Foreign key to Product.ProductID.
|- valign="top"
| SpecialOfferID
| int
| not null
| 
| Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
|- valign="top"
| UnitPrice
| money
| not null
| 
| Selling price of a single product.
|- valign="top"
| UnitPriceDiscount
| money
| not null
| ((0.0))
| Discount amount.
|- valign="top"
| LineTotal
| 
| 
| 
| Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
|- valign="top"
| rowguid
| uniqueidentifier
| not null
| (newid())
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
|- valign="top"
| ModifiedDate
| datetime
| not null
| (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
|-
| 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"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column
		
|-
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
| SalesOrderID
| SalesOrderID
|-
| [[Sales.SpecialOfferProduct_(table)|Sales.SpecialOfferProduct]]
| SpecialOfferID
| SpecialOfferID
|}

=== Triggers ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| 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

|-
| 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
|}

=== Dependencies ===
{| 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
|}


== 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 />
|}



{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column
		
|-
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
| SalesOrderID
| SalesOrderID
|-
| [[Sales.SpecialOfferProduct_(table)|Sales.SpecialOfferProduct]]
| SpecialOfferID
| SpecialOfferID
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| iduSalesOrderDetail
| 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
|}