Welcome Guest, you are in: Login

devio dbscript ScrewTurn Wiki

RSS RSS

Navigation





Search the wiki
»

PoweredBy

Page History: Sales SalesOrderDetail (table)

Compare Page Revisions



« Older Revision - Back to Page History - Current Revision


Page Revision: 2010/06/24 01:10


wikibot

TableSales.SalesOrderDetail
DescriptionIndividual products associated with a specific sales order. See SalesOrderHeader.

Columns

ColumnData TypeNullableDefaultDescription
SalesOrderIDintnot null Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesOrderDetailIDintnot null Primary key. One incremental unique number per product sold.
CarrierTrackingNumbernvarchar(25)null Shipment tracking number supplied by the shipper.
OrderQtysmallintnot null Quantity ordered per product.
ProductIDintnot null Product sold to customer. Foreign key to Product.ProductID.
SpecialOfferIDintnot null Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPricemoneynot null Selling price of a single product.
UnitPriceDiscountmoneynot null((0.0))Discount amount.
LineTotal   Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguiduniqueidentifiernot null(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailIDSalesOrderID, SalesOrderDetailID

Indexes

IndexTypeColumns
AK_SalesOrderDetail_rowguidUniquerowguid
IX_SalesOrderDetail_ProductID ProductID

Check Constraints

Check ConstraintExpressionDescription
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

RelationColumnReferenced Column
Sales.SalesOrderHeaderSalesOrderIDSalesOrderID
Sales.SpecialOfferProductSpecialOfferIDSpecialOfferID

Triggers

TriggerType
iduSalesOrderDetailON 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 TypeObject TypeReferenced ObjectChild TypeChild Object
SchemaSchemaSales  
InsertTableProduction.TransactionHistoryTriggeriduSalesOrderDetail
UpdateTableSales.IndividualTriggeriduSalesOrderDetail
SelectTableSales.SalesOrderDetailTriggeriduSalesOrderDetail
SelectTableSales.SalesOrderHeaderTriggeriduSalesOrderDetail
UpdateTableSales.SalesOrderHeaderTriggeriduSalesOrderDetail
ExecuteProceduredbo.uspLogErrorTriggeriduSalesOrderDetail
ExecuteProceduredbo.uspPrintErrorTriggeriduSalesOrderDetail

Dependencies

Reference TypeObject TypeReferencing ObjectChild TypeChild Object
SelectTableSales.SalesOrderDetailTriggeriduSalesOrderDetail

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam.