Should you take trauma to defeat non-elite enemies? Probably not. As a Guardian you will very rarely need to take such drastic measures to defeat these kinds of enemies. Every once in a blue moon you'll be caught in a gigantic swarm of enemies, and maybe playing this would help your teammates win a scenario. But that's incredibly rare. The vast majority of this time, this event is not going to help the team win. And remember that many scenarios impose really nasty penalties on defeated investigators, which can make playing this event even more dangerous than it first appears. The physical trauma might not be the full extent of the punishment you take for activating this event.
So what's the point of this card? I see two possible upsides. First, it has two Combat icons, which is generally what Guardians most want to see. I wouldn't run this over Overpower or Unexpected Courage, both of which offer extra perks that are a heck of a lot more useful than I'll see you in hell!'s event, but Guardians who are looking to hit more reliably (probably on higher levels) could run this in addition. I think the Guardian card pool is deep enough that you can probably do better, but still, there are worse things to have in your deck than a cheap +2 bonus to one Combat check.
The second way you could possibly use this card is to avoid mental trauma. Guardians tend to have a lot of health and not very much sanity, so they greatly prefer physical over mental trauma. So, theoretically, you could play this card if you think you're about to die from sanity loss, and instead take the more desirable trauma type (perhaps taking out an enemy or two in the bargain.) This is a cute idea, but I think in practice you would almost always be better off just taking more horror healing cards if you're that worried about mental trauma.
All in all, I don't think this card is all that useful as things currently stand. Its theme is pretty cool, though.