Properties

Owner:	Sales
Table/View:	Sales.Individual
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Encrypted:
Description:	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Type

Instead of:
Insert:
Update:
Delete:

Objects that [Sales].[iuIndividual] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	NameStyle	dbo	User Defined type	1
	Phone	dbo	User Defined type	1
	ufnLeadingZeros	dbo	Function	2
	Contact	Person	Table	2
	SalesTerritory	Sales	Table	2
	Customer	Sales	Table	3
	Employee	HumanResources	Table	3
	Individual	Sales	Table	4
	SalesPerson	Sales	Table	4
	Store	Sales	Table	5

Total: 12 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual]
AFTER INSERT, UPDATE NOT FOR REPLICATION AS
BEGIN
    DECLARE @Count int;

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

    SET NOCOUNT ON;

    -- Only allow the Customer to be a Store OR Individual
    IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store]
        ON inserted.[CustomerID] = [Sales].[Store].[CustomerID])
    BEGIN
        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;

    IF UPDATE([CustomerID]) OR UPDATE([Demographics])
    BEGIN
        UPDATE [Sales].[Individual]
        SET [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
            <TotalPurchaseYTD>0.00</TotalPurchaseYTD>
            </IndividualSurvey>'
        FROM inserted
        WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]
            AND inserted.[Demographics] IS NULL;
        
        UPDATE [Sales].[Individual]
        SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
            insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD>
            as first
            into (/IndividualSurvey)[1]')
        FROM inserted
        WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]
            AND inserted.[Demographics] IS NOT NULL
            AND inserted.[Demographics].exist(N'declare default element namespace
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
                /IndividualSurvey/TotalPurchaseYTD') <> 1;
    END;
END;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of table/view triggers
Table [Sales.Individual]