AdventureWorks

Basic Index   Expanded Index

Table Name:Purchasing.PurchaseOrderHeader
Description:General purchase order information. See PurchaseOrderDetail.
Primary Keys:PurchaseOrderID
FieldTypeDefaultsNulls?Comments
PurchaseOrderIDint - IdentityNoneNoPrimary key.
RevisionNumbertinyint((0))NoIncremental number to track changes to the purchase order over time.
Statustinyint((1))NoOrder current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeIDintNoneNoEmployee who created the purchase order. Foreign key to Employee.EmployeeID.
VendorIDintNoneNoVendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
ShipMethodIDintNoneNoShipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDatedatetime(getdate())NoPurchase order creation date.
ShipDatedatetimeNoneYesEstimated shipment date from the vendor.
SubTotalmoney((0.00))NoPurchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmtmoney((0.00))NoTax amount.
Freightmoney((0.00))NoShipping cost.
TotalDuemoneyNoneNoTotal due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_PurchaseOrderHeader_PurchaseOrderIDYesYesPurchaseOrderID
IX_PurchaseOrderHeader_VendorIDNoNoVendorID
IX_PurchaseOrderHeader_EmployeeIDNoNoEmployeeID

Check ConstraintText
CK_PurchaseOrderHeader_Status([Status]>=(1) AND [Status]<=(4))
CK_PurchaseOrderHeader_ShipDate([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_PurchaseOrderHeader_SubTotal([SubTotal]>=(0.00))
CK_PurchaseOrderHeader_TaxAmt([TaxAmt]>=(0.00))
CK_PurchaseOrderHeader_Freight([Freight]>=(0.00))

Internal Foreign Key ConstraintAffected FieldSource Table
FK_PurchaseOrderHeader_Employee_EmployeeIDEmployeeIDHumanResources.Employee
FK_PurchaseOrderHeader_ShipMethod_ShipMethodIDShipMethodIDPurchasing.ShipMethod
FK_PurchaseOrderHeader_Vendor_VendorIDVendorIDPurchasing.Vendor

Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderIDPurchasing.PurchaseOrderDetailPurchaseOrderID

TriggerText
uPurchaseOrderHeaderCREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader]
AFTER UPDATE AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
IF NOT UPDATE([Status])
BEGIN
UPDATE [Purchasing].[PurchaseOrderHeader]
SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] =
[Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1
WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN
(SELECT inserted.[PurchaseOrderID] FROM inserted);
END;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;