TR_EMAILADDRESS_TASYNC_U

            CREATE trigger TR_EMAILADDRESS_TASYNC_U on dbo.EMAILADDRESS after update not for replication
            as begin

              declare @CHILDRECORDS table (EMAILADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, ISPRIMARY bit);
              declare @MASTERSYNCRECORDS table (EMAILADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier);

              if dbo.UFN_B2T_CONTEXTISSYNC() = 0
              begin
                -- flag this row

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

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

                --

                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)));
                --

                -- find all the records that is a child of the updated record (if any)

                --

                insert into @MASTERSYNCRECORDS (EMAILADDRESSID, CONSTITUENTID)
                select a.ID, a.CONSTITUENTID
                from INSERTED i
                inner join dbo.TA_EMAILADDRESS mta on i.ID = mta.ID
                inner join dbo.TA_EMAILADDRESS ta on mta.SYNCID = ta.MASTERSYNCID
                inner join dbo.EMAILADDRESS a on ta.ID = a.ID
                --

                --When updating a master record, flag related rows for update

                --which may result in the related rows being inserted as new rows in TA

                --(decoupling).  Only do this if the updates resulted in unlinking the 

                --linked rows.

                --

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

                -- update a household record to match an individual record.  Flag

                -- the matched rows for update.

                -- (coupling)

                --

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

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

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

                -- or the record being updated 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)
                --

                -- when updating a primary record and it matches an existing primary address in the household

                -- flag that constituent. (coupling)

                --

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

                --

                --when updating a primary record and it unlinks the record from the master, flag that 

                --constituent (decoupling)

                --

                update t
                set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
                from  dbo.TA_CONSTITUENT t
                where t.ID not in (select CONSTITUENTID from @CHILDRECORDS) and t.ID in (select CONSTITUENTID from @MASTERSYNCRECORDS)
                --

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

                -- Refresh accounts.email_sts

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