Apart from poorly implemented trophies and multiplayer specific trophies that become impossible to obtain, I can tell you some games that WOULD HAVE BEEN ruined with trophies:
- Castlevania SOTN
- the Silent Hills
- Super Mario 3D Land / Super Mario 3D World
- Any game that contains structural game surprises
There's no way you can say that these games would have been ruined if they had trophies/achievements. There's just no way to know whether that would be the case.