If you want to update many rows after updating N rows, you want an after STATEMENT trigger which is executed after insert, update or delete. You also want to ensure that only that function maintains sort_order field and that you don't update recursively, perhaps by executing that function when NEW.sort_order IS NOT DISTINCT FROM old.sort_order to prevent an infinite loop, and executing a different function when NEW.sort_order IS DISTINCT FROM OLD.sort_order such that you ensure all other fields have not changed.
By the way, your window function could be row_number() - 1 instead of activity_id - FIRST_VALUE(activity_id).
