You need the carrier to offer eSIM, of course, but then you can just store a bunch of eSIMs on your iPhone and switch which one is active in the Settings app.
My carrier is currently not doing eSIM. Also some low cost carriers can’t be contacted in any other way than a chat. Which is a problem in many real world situations if you need an eSIM asap. Sometimes there is no one in the chat available at all.
If your carrier doesn’t support eSIM, then this discussion doesn’t apply to you. Carriers aren’t going to make eSIM available until they have the support structure available to make it useful to customers.
I guess eSIM is still a luxury for now so they want to milk it. I have only ever heard of first time activation of a sim. Vodafone in NL requires an activation via their app, or phone, before first use. But I think that is normal. The last time I got a physical sim it was in 2013 and I have transferred the same sim across multiple phones since.
You can transfer the sim but it needs to be activated on the other phone. I have even seen reactivation charges of €5.