Properties

Owner:	Production
Table/View:	Production.WorkOrder
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Encrypted:
Description:	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Type

Instead of:
Insert:
Update:
Delete:

Objects that [Production].[uWorkOrder] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	ErrorLog	dbo	Table	2
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	ScrapReason	Production	Table	2
	UnitMeasure	Production	Table	2
	uspPrintError	dbo	Procedure	2
	ProductSubcategory	Production	Table	3
	uspLogError	dbo	Procedure	3
	Product	Production	Table	4
	TransactionHistory	Production	Table	5
	WorkOrder	Production	Table	5

Total: 13 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of table/view triggers
Table [Production.WorkOrder]