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 Person table to the current date.
Trigger properties
| name | value |
|---|
| parent | Person |
| 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 [Person].[iuPerson] depends on
| name | object type | database | server | level |
|---|
| Person | table | AdventureWorks2008 | SPRING\KATMAI | 1 |
| NameStyle | type | AdventureWorks2008 | SPRING\KATMAI | 2 |
Code
CREATE TRIGGER [Person].[iuPerson] ON [Person].[Person]
AFTER INSERT, UPDATE NOT FOR REPLICATION AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
IF UPDATE([BusinessEntityID]) OR UPDATE([Demographics])
BEGIN
UPDATE [Person].[Person]
SET [Person].[Person].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0.00</TotalPurchaseYTD>
</IndividualSurvey>'
FROM inserted
WHERE [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID]
AND inserted.[Demographics] IS NULL;
UPDATE [Person].[Person]
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 [Person].[Person].[BusinessEntityID] = inserted.[BusinessEntityID]
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;