A good trigger job will smooth out any stacking or creep it might have, will generally lighten it (though to different weights depending on if you're going for a competition gun of a defense gun), and will generally smooth out the three parts (not literal parts) of a trigger pull: the pretravel (where you can get creepy or stacking triggers) the actual pull and reset, and the overtravel (the movement of the trigger after the sear release).
Well, anyway, to answer your question, definitely not limited to competition pistols. Whenever you see a high-end defensive pistol, it's had a trigger job.
Andy