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.
Trigger properties
| name | value |
|---|
| parent | Individual |
| type | After Update Insert |
| is first insert | false |
| is last insert | false |
| is first update | false |
| is last update | false |
| is last delete | false |
| is first delete | false |
| is disabled | false |
| is not for replication | true |
Dependency graph
Objects that [Sales].[iuIndividual] depends on
Code
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;