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;
IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store] 
ON inserted.[CustomerID] = [Sales].[Store].[CustomerID]) 
BEGIN
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;