AdventureWorks

Basic Index   Expanded Index

Table Name:Sales.SalesOrderDetail
Description:Individual products associated with a specific sales order. See SalesOrderHeader.
Primary Keys:SalesOrderID
SalesOrderDetailID
FieldTypeDefaultsNulls?Comments
SalesOrderIDintNoneNoPrimary key. Foreign key to SalesOrderHeader.SalesOrderID.
SalesOrderDetailIDint - IdentityNoneNoPrimary key. One incremental unique number per product sold.
CarrierTrackingNumbernvarchar (25)NoneYesShipment tracking number supplied by the shipper.
OrderQtysmallintNoneNoQuantity ordered per product.
ProductIDintNoneNoProduct sold to customer. Foreign key to Product.ProductID.
SpecialOfferIDintNoneNoPromotional code. Foreign key to SpecialOffer.SpecialOfferID.
UnitPricemoneyNoneNoSelling price of a single product.
UnitPriceDiscountmoney((0.0))NoDiscount amount.
LineTotalnumeric (38, 6)NoneNoPer product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
rowguiduniqueidentifier(newid())NoROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailIDYesYesSalesOrderID
SalesOrderDetailID
AK_SalesOrderDetail_rowguidNoYesrowguid
IX_SalesOrderDetail_ProductIDNoNoProductID

Check ConstraintText
CK_SalesOrderDetail_OrderQty([OrderQty]>(0))
CK_SalesOrderDetail_UnitPrice([UnitPrice]>=(0.00))
CK_SalesOrderDetail_UnitPriceDiscount([UnitPriceDiscount]>=(0.00))

Internal Foreign Key ConstraintAffected FieldSource Table
FK_SalesOrderDetail_SalesOrderHeader_SalesOrderIDSalesOrderIDSales.SalesOrderHeader
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductIDProductIDSales.SpecialOfferProduct
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductIDSpecialOfferIDSales.SpecialOfferProduct

No primary keys as foreign key constraints
TriggerText
iduSalesOrderDetailCREATE 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 UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])
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 [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];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;