AdventureWorks

Basic Index   Expanded Index

Table Name:Production.WorkOrder
Description:Manufacturing work orders.
Primary Keys:WorkOrderID
FieldTypeDefaultsNulls?Comments
WorkOrderIDint - IdentityNoneNoPrimary key for WorkOrder records.
ProductIDintNoneNoProduct identification number. Foreign key to Product.ProductID.
OrderQtyintNoneNoProduct quantity to build.
StockedQtyintNoneNoQuantity built and put in inventory.
ScrappedQtysmallintNoneNoQuantity that failed inspection.
StartDatedatetimeNoneNoWork order start date.
EndDatedatetimeNoneYesWork order end date.
DueDatedatetimeNoneNoWork order due date.
ScrapReasonIDsmallintNoneYesReason for inspection failure.
ModifiedDatedatetime(getdate())NoDate and time the record was last updated.

IndexClustered?Unique?Fields
PK_WorkOrder_WorkOrderIDYesYesWorkOrderID
IX_WorkOrder_ScrapReasonIDNoNoScrapReasonID
IX_WorkOrder_ProductIDNoNoProductID

Check ConstraintText
CK_WorkOrder_OrderQty([OrderQty]>(0))
CK_WorkOrder_ScrappedQty([ScrappedQty]>=(0))
CK_WorkOrder_EndDate([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Internal Foreign Key ConstraintAffected FieldSource Table
FK_WorkOrder_Product_ProductIDProductIDProduction.Product
FK_WorkOrder_ScrapReason_ScrapReasonIDScrapReasonIDProduction.ScrapReason

Primary Key as Foreign Key ConstraintAffected TableAffected Field
FK_WorkOrderRouting_WorkOrder_WorkOrderIDProduction.WorkOrderRoutingWorkOrderID

TriggerText
iWorkOrderCREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder]
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]
,[TransactionType]
,[TransactionDate]
,[Quantity]
,[ActualCost])
SELECT
inserted.[ProductID]
,inserted.[WorkOrderID]
,'W'
,GETDATE()
,inserted.[OrderQty]
,0
FROM inserted;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
uWorkOrderCREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder]
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])
BEGIN
INSERT INTO [Production].[TransactionHistory](
[ProductID]
,[ReferenceOrderID]
,[TransactionType]
,[TransactionDate]
,[Quantity])
SELECT
inserted.[ProductID]
,inserted.[WorkOrderID]
,'W'
,GETDATE()
,inserted.[OrderQty]
FROM inserted;
END;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;