TR_CONSTITUENTNOTE_TASYNC_U

    CREATE trigger TR_CONSTITUENTNOTE_TASYNC_U on dbo.CONSTITUENTNOTE after update not for replication
    as begin
      if dbo.UFN_B2T_CONTEXTISSYNC() = 0
      begin
        -- normally this is triggering an update of interactions

        -- if notetypecode changed from one not mapped to interactions to one that is, this triggers an insert instead

        merge dbo.TA_CONSTITUENTNOTE t
        using (select i.ID
               from INSERTED i
               -- only keep notes using codes mapped from interactions

               inner join dbo.B2TROWS as jcontact_type on jcontact_type.TATABLE = 'CONTACT_TYPE' and jcontact_type.BBECTABLE = 'CONSTITUENTNOTETYPECODE' and jcontact_type.BBECID = i.CONSTITUENTNOTETYPECODEID
               where not exists(select 'x'
                                from dbo.GROUPDATA d
                                where d.ID = i.CONSTITUENTID
                                and d.GROUPTYPECODE = 1)) u
        on (t.ID = u.ID)
        when matched then
          update set ACTIONCODE = case when ACTIONCODE=1 then 1 else 2 end
        when not matched then
          insert (ID, SYNCID, ACTIONCODE) values (u.ID, newid(), 1);

        -- if notetypecode changed to one not mapped to interactions, delete the interaction from TA

        merge dbo.TA_CONSTITUENTNOTE t
        using (select i.ID
               from INSERTED i
               where not exists(select 'x'
                                from dbo.B2TROWS jcontact_type
                                where jcontact_type.TATABLE = 'CONTACT_TYPE'
                                and jcontact_type.BBECTABLE = 'CONSTITUENTNOTETYPECODE'
                                and jcontact_type.BBECID = i.CONSTITUENTNOTETYPECODEID)) u
        on (t.ID = u.ID)
        when matched and t.ACTIONCODE = 1 then
          delete
        when matched then
          update set ACTIONCODE = 3;

        update dbo.TA_CONSTITUENT
        set ACTIONCODE = case when ACTIONCODE=1 then 1 else 2 end
        where ID in(select INSERTED.CONSTITUENTID
                    from INSERTED
                    inner join dbo.CONSTITUENTNOTETYPECODE c on c.ID = INSERTED.CONSTITUENTNOTETYPECODEID
                    where c.DESCRIPTION = 'Constituent Comments');

        if @@ROWCOUNT > 0
          update dbo.TA_ACCOUNTS
          set ACTIONCODE = case when ACTIONCODE=1 then 1 else 2 end
          where ID in(select INSERTED.CONSTITUENTID
                      from INSERTED
                      inner join dbo.CONSTITUENTNOTETYPECODE c on c.ID = INSERTED.CONSTITUENTNOTETYPECODEID
                      where c.DESCRIPTION = 'Constituent Comments');
      end
    end