TR_EMAILADDRESS_TASYNC_D

        CREATE trigger TR_EMAILADDRESS_TASYNC_D on dbo.EMAILADDRESS after delete not for replication
        as begin
          if dbo.UFN_B2T_CONTEXTISSYNC() = 1
            delete from dbo.TA_EMAILADDRESS
            where ID in(select ID from DELETED)
          else
          begin
            merge dbo.TA_EMAILADDRESS t
            using (select ID from DELETED) d
            on (d.ID = t.ID)
            when matched and t.ACTIONCODE = 1 then
              delete
            when matched then
            update set ACTIONCODE = 3;
            --

            -- reset the primary.

            --

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

            -- When deleting a master row (t2), flag the linked non-master (t) rows for insert

            -- into TA.

            --

            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 dbo.TA_EMAILADDRESS t2 on t.MASTERSYNCID = t2.SYNCID
            inner join DELETED d on d.ID = t2.ID
            --

            -- flag the constituent record that the EMAILADDRESS is on or

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

            -- and the constituent does not have an EMAILADDRESS

            --

            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 DELETED 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 DELETED 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 members when household master records are deleted and the member

            -- record is primary.

            --

            update t
            set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
            from dbo.TA_CONSTITUENT t, dbo.TA_EMAILADDRESS d, dbo.TA_EMAILADDRESS m, dbo.EMAILADDRESS a, DELETED i 
            where t.ID in (select MEMBERID from dbo.UFN_B2T_GET_HOUSEHOLDANDMEMBERS(i.CONSTITUENTID, 1))
            and i.ID = d.ID
            and d.SYNCID = m.MASTERSYNCID
            and m.ID = a.ID
            and t.ID = a.CONSTITUENTID
            and a.ISPRIMARY = 1
            --

            -- do the same for org contacts

            --

            update t
            set ACTIONCODE = case when t.ACTIONCODE in (1,3) then t.ACTIONCODE else 2 end
            from dbo.TA_CONSTITUENT t, dbo.TA_EMAILADDRESS d, dbo.TA_EMAILADDRESS m, dbo.EMAILADDRESS a, DELETED i 
            where dbo.UFN_CONSTITUENT_ISORGANIZATION (i.CONSTITUENTID) = 1
            and t.ID in (select r.RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP r where r.RELATIONSHIPCONSTITUENTID = i.CONSTITUENTID)
            and i.ID = d.ID
            and d.SYNCID = m.MASTERSYNCID
            and m.ID = a.ID
            and t.ID = a.CONSTITUENTID
            and a.ISPRIMARY = 1
            --

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

            -- Refresh accounts.address_sts

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