Re: Using a TRIGGER with window functions.

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