TR_EMAILADDRESS_TASYNC_I

            CREATE trigger TR_EMAILADDRESS_TASYNC_I on dbo.EMAILADDRESS after insert not for replication
            as begin
              declare @CHILDRECORDS table (EMAILADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, ISPRIMARY bit);
              if dbo.UFN_B2T_CONTEXTISSYNC() = 0
              begin
                insert into dbo.TA_EMAILADDRESS (ID, SYNCID, ACTIONCODE)
                select ID, newid(), 1
                from INSERTED
                -- don't sync records for groups

                where not exists(select 'x'
                          from dbo.GROUPDATA d
                          where d.ID = INSERTED.CONSTITUENTID
                          and d.GROUPTYPECODE = 1);
                --

                -- note that the above insert could fail if row is deleted and then reinserted w/ same ID before sync is run

                -- since we would not know anything about whether the row is really the same or not, we need to throw an exception

                -- to resolve, run the sync, then retry the insert

                --

                -- find and store all the records that looks the same as the record being inserted.

                --

                insert into @CHILDRECORDS (EMAILADDRESSID, CONSTITUENTID, ISPRIMARY)
                select a.ID, a.CONSTITUENTID, a.ISPRIMARY
                from INSERTED i
                inner join dbo.GROUPMEMBER m on m.GROUPID = i.CONSTITUENTID
                inner join dbo.EMAILADDRESS a on a.CONSTITUENTID = m.MEMBERID
                and (a.EMAILADDRESS = i.EMAILADDRESS 
                     and (a.EMAILADDRESSTYPECODEID = i.EMAILADDRESSTYPECODEID 
                          or (a.EMAILADDRESSTYPECODEID is null and i.EMAILADDRESSTYPECODEID is null)))
                union all
                select a2.ID, a2.CONSTITUENTID, a2.ISPRIMARY
                from INSERTED i
                inner join dbo.RELATIONSHIP r on i.CONSTITUENTID=r.RELATIONSHIPCONSTITUENTID
                inner join dbo.EMAILADDRESS a2 on a2.RELATIONSHIPID=r.ID
                where r.ISCONTACT=1
                and (a2.EMAILADDRESS = i.EMAILADDRESS 
                     and (a2.EMAILADDRESSTYPECODEID = i.EMAILADDRESSTYPECODEID 
                          or (a2.EMAILADDRESSTYPECODEID is null and i.EMAILADDRESSTYPECODEID is null)));
                --

                -- flag additional records for when primary is inserted and the old primary for the 

                -- accounts has to be unflagged.  This is to handle the case where the inserted record

                -- is the first one for the household and a member's primary record was synced as 

                -- the account's primary in TA.  This record now has to be unflagged as the account's

                -- primary.

                --

                update t
                set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
                from dbo.TA_EMAILADDRESS t, INSERTED i
                where t.ID = dbo.UFN_B2T_GETACCOUNTPREFERREDEMAIL(i.CONSTITUENTID,1)
                and i.ISPRIMARY = 1
                --

                -- When inserting a household address that matches a member's emailaddress, flag the member's

                -- row for update, in this case the member's row will be deleted in TA and will be associated

                -- with the household's emailaddress.

                --

                update dbo.TA_EMAILADDRESS
                set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
                where ID in (select EMAILADDRESSID from @CHILDRECORDS)
                --

                -- flag the members in the household for update when a primary record is inserted

                -- and the constituent does not have a record or if the record matches the inserted record

                -- or the record being inserted is considered the household's primary

     --

                update t
                set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
                from dbo.TA_CONSTITUENT t
                inner join INSERTED i on i.CONSTITUENTID = t.ID
                where i.ISPRIMARY = 1

                update t
                set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
                from INSERTED i
                cross apply dbo.UFN_B2T_GET_HOUSEHOLDANDMEMBERS(i.CONSTITUENTID, 1) m
                inner join dbo.TA_CONSTITUENT t on t.ID = m.MEMBERID
                where i.ISPRIMARY = 1
                and NOT exists (select 'x' from dbo.EMAILADDRESS a where t.ID = a.CONSTITUENTID)
                --

                --flag the contact(s) for an org constituent if that contact does not have an address

                --

                update t
                set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
                from INSERTED i
                inner join dbo.RELATIONSHIP r on i.CONSTITUENTID = r.RELATIONSHIPCONSTITUENTID
                inner join dbo.TA_CONSTITUENT t on t.ID = r.RECIPROCALCONSTITUENTID
                where dbo.UFN_CONSTITUENT_ISORGANIZATION (i.CONSTITUENTID) = 1
                and i.ISPRIMARY = 1
                and NOT exists (select 'x' from dbo.EMAILADDRESS a where t.ID = a.CONSTITUENTID)
                --

                -- flag constituents when inserting a master to couple to a household master.  This is to update

                -- the primary emailaddress IDs for the name record. 

                --

                update dbo.TA_CONSTITUENT
                set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
                where ID in (select CONSTITUENTID from @CHILDRECORDS where ISPRIMARY = 1);

                update t
                set t.ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
                from dbo.TA_NAMES0 t
                inner join INSERTED i on i.CONSTITUENTID = t.ID and i.ISPRIMARY = 1

                -- Refresh accounts.address_sts

                update dbo.TA_ACCOUNTS
                set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
                where ID in(select CONSTITUENTID from INSERTED);
              end
            end