TR_CONSTITUENTSITE_TASYNC_U

    CREATE trigger TR_CONSTITUENTSITE_TASYNC_U on dbo.CONSTITUENTSITE after update not for replication
    as begin
      if dbo.UFN_B2T_CONTEXTISSYNC() = 0
      begin
        -- if deleted.siteid was a classification site, it will be in ta_constituentsite

        -- if inserted.siteid is a classification site but there is no record in ta_constituentsite, we need to insert one (behave like insert trigger)

        -- if inserted.siteid is an affiliation site but there is a record in ta_constituentsite, we need to delete it (behave like delete trigger)

        -- if inserted.siteid is a classification site and there is a record in ta_constituentsite, this is a normal update

        merge dbo.TA_CONSTITUENTSITE t
        using (select INSERTED.ID, r.TATABLE
               from INSERTED
               inner join dbo.B2TROWS r on r.BBECTABLE = 'SITE' and r.BBECID = INSERTED.SITEID
               where not exists(select 'x'
                                from dbo.GROUPDATA d
                                where d.ID = INSERTED.CONSTITUENTID
                                and d.GROUPTYPECODE = 1)) i
        on (i.ID = t.ID)
        when not matched and i.TATABLE = 'T2B_CLASSIFICATION_VALUE' then
          insert (ID, SYNCID, ACTIONCODE)
          values (i.ID, newid(), 1)
        when matched and i.TATABLE = 'STATION_SUPPORT' and t.ACTIONCODE = 1 then
          delete
        when matched then
          update set ACTIONCODE = case i.TATABLE when 'T2B_CLASSIFICATION_VALUE' then case when ACTIONCODE=1 then 1 else 2 end else 3 end;

        -- if either or both deleted.siteid and/or inserted.siteid are an affiliation site, we need to update the constituent

        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
                    union all
                    select DELETED.CONSTITUENTID
                    from DELETED
                    inner join dbo.B2TROWS r on r.TATABLE = 'STATION_SUPPORT' and r.BBECTABLE = 'SITE' and r.BBECID = DELETED.SITEID);

        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.B2TROWS r on r.TATABLE = 'STATION_SUPPORT' and r.BBECTABLE = 'SITE' and r.BBECID = INSERTED.SITEID
                      union all
                      select DELETED.CONSTITUENTID
                      from DELETED
                      inner join dbo.B2TROWS r on r.TATABLE = 'STATION_SUPPORT' and r.BBECTABLE = 'SITE' and r.BBECID = DELETED.SITEID);
      end
    end