AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.
Properties
Creation Date
08/01/2010 08:41
Encrypted
Ansi Nulls
Trigger Type
Insert
Delete
Update
After
Instead Of
Trigger Definition
CREATETRIGGER[Purchasing].[uPurchaseOrderDetail]ON[Purchasing].[PurchaseOrderDetail]
AFTER UPDATEASBEGINDECLARE@Countint;
SET@Count=@@ROWCOUNT;
IF@Count=0RETURN;
SET NOCOUNT ON;
BEGIN TRY
IFUPDATE([ProductID]) ORUPDATE([OrderQty]) ORUPDATE([UnitPrice])
BEGININSERTINTO[Production].[TransactionHistory]
([ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,[TransactionType]
,[TransactionDate]
,[Quantity]
,[ActualCost])
SELECT
inserted.[ProductID]
,inserted.[PurchaseOrderID]
,inserted.[PurchaseOrderDetailID]
,'P'
,GETDATE()
,inserted.[OrderQty]
,inserted.[UnitPrice]FROM inserted
INNERJOIN[Purchasing].[PurchaseOrderDetail]ON inserted.[PurchaseOrderID]=[Purchasing].[PurchaseOrderDetail].[PurchaseOrderID];
UPDATE[Purchasing].[PurchaseOrderHeader]SET[Purchasing].[PurchaseOrderHeader].[SubTotal]=
(SELECTSUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
FROM[Purchasing].[PurchaseOrderDetail]WHERE[Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]=[Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
WHERE[Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]IN (SELECT inserted.[PurchaseOrderID]FROM inserted);
UPDATE[Purchasing].[PurchaseOrderDetail]SET[Purchasing].[PurchaseOrderDetail].[ModifiedDate]=GETDATE()
FROM inserted
WHERE inserted.[PurchaseOrderID]=[Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]AND inserted.[PurchaseOrderDetailID]=[Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID];
END;
END TRY
BEGIN CATCH
EXECUTE[dbo].[uspPrintError];
IF@@TRANCOUNT>0BEGINROLLBACKTRANSACTION;
ENDEXECUTE[dbo].[uspLogError];
END CATCH;
END;