TR_PHONE_TASYNC_I

    CREATE trigger TR_PHONE_TASYNC_I on dbo.PHONE after insert not for replication
    as begin

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

      if dbo.UFN_B2T_CONTEXTISSYNC() = 0
      begin
        insert into dbo.TA_PHONE (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 phones that looks the same as the record being inserted.

        --

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

        -- 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 phone 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_PHONE t, INSERTED i 
        where t.ID = dbo.UFN_B2T_GETACCOUNTPREFERREDTELEPHONE(i.CONSTITUENTID,1)
        and i.ISPRIMARY = 1
        --

        -- When inserting a household phone that matches a member's phone, 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 phone.

        --

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

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

        -- the primary email 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 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 in (1,3) then ACTIONCODE else 2 end
        where ID in(select CONSTITUENTID from INSERTED);
      end
    end