Sales.iuIndividual Trigger

Information

Name	Sales.iuIndividual
Table Name	Sales.Individual
Created	29.3.2010. 21:58:56
Modified	29.3.2010. 21:58:56
Disabled
For replication
Instead of trigger
Activates on	INSERT, UPDATE

SQL Script

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;