TR_PHONE_TASYNC_U

    CREATE trigger TR_PHONE_TASYNC_U on dbo.PHONE after update not for replication
    as begin

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

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

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

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

        --

        insert into @CHILDRECORDS (PHONEID, 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.PHONE a on a.CONSTITUENTID = m.MEMBERID
        and a.NUMBER = i.NUMBER and (a.PHONETYPECODEID = i.PHONETYPECODEID or (a.PHONETYPECODEID is null and i.PHONETYPECODEID 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.PHONE a2 on a2.RELATIONSHIPID=r.ID
        where r.ISCONTACT=1
        and a2.NUMBER = i.NUMBER and (a2.PHONETYPECODEID = i.PHONETYPECODEID or (a2.PHONETYPECODEID is null and i.PHONETYPECODEID is null));
        --

        --

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

        --

        insert into @MASTERSYNCRECORDS (PHONEID, CONSTITUENTID)
        select a.ID, a.CONSTITUENTID
        from INSERTED i
        inner join dbo.TA_PHONE mta on i.ID = mta.ID
        inner join dbo.TA_PHONE ta on mta.SYNCID = ta.MASTERSYNCID
        inner join dbo.PHONE 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_PHONE t
        where t.ID not in (select PHONEID from @CHILDRECORDS) and t.ID in (select PHONEID from @MASTERSYNCRECORDS)
        --

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

        -- the matched rows for update.

        -- (coupling)

        --

        update dbo.TA_PHONE
        set ACTIONCODE = case when ACTIONCODE in (1,3) then ACTIONCODE else 2 end
        where ID in (select PHONEID 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.PHONE 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.PHONE a where t.ID = a.CONSTITUENTID)
        --

        -- when updating a primary record and it matches an existing primary phone 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 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.telephone_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