Properties

Owner:	Sales
Table/View:	Sales.SalesOrderDetail
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Encrypted:
Description:	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Type

Instead of:
Insert:
Update:
Delete:

Objects that [Sales].[iduSalesOrderDetail] 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
	OrderNumber	dbo	User Defined type	1
	Phone	dbo	User Defined type	1
	ufnLeadingZeros	dbo	Function	2
	Contact	Person	Table	2
	CountryRegion	Person	Table	2
	CreditCard	Sales	Table	2
	Currency	Sales	Table	2
	ErrorLog	dbo	Table	2
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	SalesTerritory	Sales	Table	2
	ShipMethod	Purchasing	Table	2
	SpecialOffer	Sales	Table	2
	UnitMeasure	Production	Table	2
	uspPrintError	dbo	Procedure	2
	CurrencyRate	Sales	Table	3
	Customer	Sales	Table	3
	Employee	HumanResources	Table	3
	ProductSubcategory	Production	Table	3
	StateProvince	Person	Table	3
	uspLogError	dbo	Procedure	3
	Address	Person	Table	4
	Individual	Sales	Table	4
	Product	Production	Table	4
	SalesPerson	Sales	Table	4
	SalesOrderHeader	Sales	Table	5
	SpecialOfferProduct	Sales	Table	5
	TransactionHistory	Production	Table	5
	SalesOrderDetail	Sales	Table	6

Total: 33 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail]
AFTER INSERT, DELETE, UPDATE AS
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- If inserting or updating these columns
        IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])
        -- Insert record into TransactionHistory
        BEGIN
            INSERT INTO [Production].[TransactionHistory]
                ([ProductID]
                ,[ReferenceOrderID]
                ,[ReferenceOrderLineID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity]
                ,[ActualCost])
            SELECT
                inserted.[ProductID]
                ,inserted.[SalesOrderID]
                ,inserted.[SalesOrderDetailID]
                ,'S'
                ,GETDATE()
                ,inserted.[OrderQty]
                ,inserted.[UnitPrice]
            FROM inserted
                INNER JOIN [Sales].[SalesOrderHeader]
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];

            UPDATE [Sales].[Individual]
            SET [Demographics].modify('declare default element namespace
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')
            FROM inserted
                INNER JOIN [Sales].[SalesOrderHeader]
                ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]
            WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
        END;

        -- Update SubTotal in SalesOrderHeader record. Note that this causes the
        -- SalesOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] =
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

        UPDATE [Sales].[Individual]
        SET [Demographics].modify('declare default element namespace
            "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
            replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
            with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")')
        FROM deleted
            INNER JOIN [Sales].[SalesOrderHeader]
            ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]
        WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID];
    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 [Sales.SalesOrderDetail]