Description
AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.
Trigger properties
| name | value |
|---|
| parent | WorkOrder |
| type | After Update |
| is first insert | false |
| is last insert | false |
| is first update | false |
| is last update | false |
| is last delete | false |
| is first delete | false |
| is disabled | false |
| is not for replication | false |
Dependency graph
Objects that [Production].[uWorkOrder] depends on
| name | object type | database | server | level |
|---|
| uspLogError | stored procedure | AdventureWorks2008 | SPRING\KATMAI | 1 |
| uspPrintError | stored procedure | AdventureWorks2008 | SPRING\KATMAI | 2 |
| ErrorLog | table | AdventureWorks2008 | SPRING\KATMAI | 2 |
| TransactionHistory | table | AdventureWorks2008 | SPRING\KATMAI | 1 |
| WorkOrder | table | AdventureWorks2008 | SPRING\KATMAI | 1 |
Code
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;