Is 'Thanks for your trouble' a common phrase?

Yes, "Thanks for your trouble" or "Thank you for your trouble" is a very common phrase (In fact I use it quite often).

It doesn't mean He is creating the trouble, it is you who is creating a trouble/burden for him. To elaborate, think of the sentence like

• "Thank you for (handling/taking care of) the trouble (I put you through)."

You are thanking him for "taking the trouble to help you."

Another way of saying this might be - "Sorry for any inconvenience this might have caused, but I sure do appreciate it."

Your final phrase - "Thanks for your effort" would in fact sound quite off in most context.

Thanks for your effort makes it sound a little bit like -- He failed in helping you, but you appreciate the effort anyway.

I wouldn't use it, as it doesn't really make you seem like you are totally satisfied with "his (the person you're thanking) help".

"Thanks for your help/favor" would be more natural than this variation.