AdventureWorks

Basic Index   Expanded Index

Table Name:Purchasing.PurchaseOrderDetail
Description:Individual products associated with a specific purchase order. See PurchaseOrderHeader.
Primary Keys:PurchaseOrderID
PurchaseOrderDetailID
FieldTypeDefaultsNulls?Comments
PurchaseOrderIDintNoneNoPrimary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PurchaseOrderDetailIDint - IdentityNoneNoPrimary key. One line number per purchased product.
DueDatedatetimeNoneNoDate the product is expected to be received.
OrderQtysmallintNoneNoQuantity ordered.
ProductIDintNoneNoProduct identification number. Foreign key to Product.ProductID.
UnitPricemoneyNoneNoVendor's selling price of a single product.
LineTotalmoneyNoneNoPer product subtotal. Computed as OrderQty * UnitPrice.
ReceivedQtydecimal (8, 2)NoneNoQuantity actually received from the vendor.
RejectedQtydecimal (8, 2)NoneNoQuantity rejected during inspection.
StockedQtydecimal (9, 2)NoneNoQuantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailIDYesYesPurchaseOrderID
PurchaseOrderDetailID
IX_PurchaseOrderDetail_ProductIDNoNoProductID

Check ConstraintText
CK_PurchaseOrderDetail_OrderQty([OrderQty]>(0))
CK_PurchaseOrderDetail_UnitPrice([UnitPrice]>=(0.00))
CK_PurchaseOrderDetail_ReceivedQty([ReceivedQty]>=(0.00))
CK_PurchaseOrderDetail_RejectedQty([RejectedQty]>=(0.00))

Internal Foreign Key ConstraintAffected FieldSource Table
FK_PurchaseOrderDetail_Product_ProductIDProductIDProduction.Product
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderIDPurchaseOrderIDPurchasing.PurchaseOrderHeader

No primary keys as foreign key constraints
TriggerText
iPurchaseOrderDetailCREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail]
AFTER INSERT AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO [Production].[TransactionHistory]
([ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionType]
,[TransactionDate]
,[Quantity]
,[ActualCost])
SELECT
inserted.[ProductID]
,inserted.[PurchaseOrderID]
,inserted.[PurchaseOrderDetailID]
,'P'
,GETDATE()
,inserted.[OrderQty]
,inserted.[UnitPrice]
FROM inserted
INNER JOIN [Purchasing].[PurchaseOrderHeader]
ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID];
UPDATE [Purchasing].[PurchaseOrderHeader]
SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =
(SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
FROM [Purchasing].[PurchaseOrderDetail]
WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted);
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
uPurchaseOrderDetailCREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail]
AFTER 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])
BEGIN
INSERT INTO [Production].[TransactionHistory]
([ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionType]
,[TransactionDate]
,[Quantity]
,[ActualCost])
SELECT
inserted.[ProductID]
,inserted.[PurchaseOrderID]
,inserted.[PurchaseOrderDetailID]
,'P'
,GETDATE()
,inserted.[OrderQty]
,inserted.[UnitPrice]
FROM inserted
INNER JOIN [Purchasing].[PurchaseOrderDetail]
ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID];
UPDATE [Purchasing].[PurchaseOrderHeader]
SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =
(SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
FROM [Purchasing].[PurchaseOrderDetail]
WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]
= [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]
IN (SELECT inserted.[PurchaseOrderID] FROM inserted);
UPDATE [Purchasing].[PurchaseOrderDetail]
SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]
AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID];
END;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;