no your character dies... your pawn becomes you and [as far as we know] the seat is unused but imo the story could go on if your pawn [as the new you] goes on to fill it

But your Arisen's still there. If you do NG+ offline, you fight your previous Arisen. If you play online, you fight someone else's Arisen then the next player fights yours. There doesn't seem to be any clear in-game clue that states that your Arisen is gone forever after stabbing themself. For all we know, that probably only separated the Arisen's soul from their body and what we end up fighting in NG+ is just that, their soul that still lingers in the rift.