TR_CONSTITUENTSITE_TASYNC_I

    CREATE trigger TR_CONSTITUENTSITE_TASYNC_I on dbo.CONSTITUENTSITE after insert not for replication
    as begin
      if dbo.UFN_B2T_CONTEXTISSYNC() = 0
      begin
        insert into dbo.TA_CONSTITUENTSITE (ID, SYNCID, ACTIONCODE)
        select INSERTED.ID, newid(), 1
        from INSERTED
        inner join dbo.B2TROWS r on r.TATABLE = 'T2B_CLASSIFICATION_VALUE' and r.BBECTABLE = 'SITE' and r.BBECID = INSERTED.SITEID
        -- don't sync records for groups

        where not exists(select 'x'
                         from dbo.GROUPDATA d
                         where d.ID = INSERTED.CONSTITUENTID
                         and d.GROUPTYPECODE = 1);

        if @@ROWCOUNT < (select count(*) from INSERTED)
        begin
            -- force re-sync of the parent row

          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.B2TROWS r on r.TATABLE = 'STATION_SUPPORT' and r.BBECTABLE = 'SITE' and r.BBECID = INSERTED.SITEID);

          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.B2TROWS r on r.TATABLE = 'STATION_SUPPORT' and r.BBECTABLE = 'SITE' and r.BBECID = INSERTED.SITEID);
        end

        -- note that the above inserts 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

      end
    end