Trigger: iPurchaseOrderDetail on table Purchasing.PurchaseOrderDetail

Properties

Owner:	Purchasing
Table/View:	Purchasing.PurchaseOrderDetail
Disabled:
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Type

Instead of:
Insert:
Update:
Delete:

Objects that [Purchasing].[iPurchaseOrderDetail] depends on

Object Name	Owner	Object Type	Dep Level
AccountNumber	dbo	User Defined type	1
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
Phone	dbo	User Defined type	1
Contact	Person	Table	2
ErrorLog	dbo	Table	2
ProductCategory	Production	Table	2
ProductModel	Production	Table	2
ShipMethod	Purchasing	Table	2
UnitMeasure	Production	Table	2
Vendor	Purchasing	Table	2
uspPrintError	dbo	Procedure	2
Employee	HumanResources	Table	3
ProductSubcategory	Production	Table	3
uspLogError	dbo	Procedure	3
Product	Production	Table	4
PurchaseOrderHeader	Purchasing	Table	4
PurchaseOrderDetail	Purchasing	Table	5
TransactionHistory	Production	Table	5

Total: 20 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail]
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]
            ,[ReferenceOrderLineID]
            ,[TransactionType]
            ,[TransactionDate]
            ,[Quantity]
            ,[ActualCost])
        SELECT
            inserted.[ProductID]
            ,inserted.[PurchaseOrderID]
            ,inserted.[PurchaseOrderDetailID]
            ,'P'
            ,GETDATE()
            ,inserted.[OrderQty]
            ,inserted.[UnitPrice]
        FROM inserted
            INNER JOIN [Purchasing].[PurchaseOrderHeader]
            ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID];

        -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the
        -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Purchasing].[PurchaseOrderHeader]
        SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =
            (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
                FROM [Purchasing].[PurchaseOrderDetail]
                WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
        WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted);
    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