AdventureWorks - Production.WorkOrder

AdventureWorks

Table Name:	Production.WorkOrder
Description:	Manufacturing work orders.
Primary Keys:	WorkOrderID

Field	Type	Defaults	Nulls?	Comments
WorkOrderID	int - Identity	None	No	Primary key for WorkOrder records.
ProductID	int	None	No	Product identification number. Foreign key to Product.ProductID.
OrderQty	int	None	No	Product quantity to build.
StockedQty	int	None	No	Quantity built and put in inventory.
ScrappedQty	smallint	None	No	Quantity that failed inspection.
StartDate	datetime	None	No	Work order start date.
EndDate	datetime	None	Yes	Work order end date.
DueDate	datetime	None	No	Work order due date.
ScrapReasonID	smallint	None	Yes	Reason for inspection failure.
ModifiedDate	datetime	(getdate())	No	Date and time the record was last updated.

Index	Clustered?	Unique?	Fields
PK_WorkOrder_WorkOrderID	Yes	Yes	WorkOrderID
IX_WorkOrder_ScrapReasonID	No	No	ScrapReasonID
IX_WorkOrder_ProductID	No	No	ProductID

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

Internal Foreign Key Constraint	Affected Field	Source Table
FK_WorkOrder_Product_ProductID	ProductID	Production.Product
FK_WorkOrder_ScrapReason_ScrapReasonID	ScrapReasonID	Production.ScrapReason

Primary Key as Foreign Key Constraint	Affected Table	Affected Field
FK_WorkOrderRouting_WorkOrder_WorkOrderID	Production.WorkOrderRouting	WorkOrderID

Trigger	Text
iWorkOrder	CREATE 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;
uWorkOrder	CREATE 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;

Trigger

Text

iWorkOrder

CREATE 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;

uWorkOrder

CREATE 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;