TLDR; Yes - you have to add the my.trafficmanager.net to the site definitions of your website (considering that in your case you have added the actual bindings to the
websitesite removing the catch-all rule for Sitecore. If that is not the case and you have thewebsitesite untouched - there might be a deeper problem with the actual resolution happening on the sites)
The reason for this actually lies inside the entire mechanism that Azure Traffic Manager works. And especially the part
The client connects to the application service endpoint directly, not through Traffic Manager.
which is happening after the DNS resolution happened.
In real case scenario you are probably going to have your real domain name (i.e. www.example.com) pointed towards the ATM and it will be responsible for handling the actual traffic that is coming for your website. As ATM is not acting as a reverse proxy it will just redirect the request to the responsible node keeping the hostname that was requested.
In order for Sitecore to be able to resolve the website - it is going to use hostname that the node was requested with - which in your case is going to be my.traffimanager.net. Meaning that you have to bind it in your site definition.