I'm a trainer and I use one myself. I have for years. Even though I can obviously work out on my own, I work harder when I'm with someone. I also learn so much from every trainer I've ever had. If your Y is like mine, the only "certification" that's required is the YMCA's own. My Y though, has several trainers with more reputable certifications as well. And actually my current trainer has no other certifications, but his knowledge and experience is way beyond anyone I've ever worked with, so don't be too concerned about which pieces of paper they hold. In my experience, Y's have a really great variety of trainers with various specialties so as long as you're clear about what you're looking for, I think you'll be happy. And if not, you can always switch.
Some trainers are awesome, others are not, particularly when you are going through the services offered at a chain gym.
I worked out with a trainer before my wedding. I was going to LA Fitness at the time. I really liked my trainer as a person and I knew very little about weights, so he was good about getting me familiar with them. It bothered me, however, that he never wanted me to use free weights and only the machines. I feel like, once I got comfortable, I could have done that by myself. He also didn't really push me to up my weights and bought into the "women shouldn't lift heavy ever" myth. I didn't know as much then as I do now and I regret wasting money on him.
Before I opted to join CrossFit I contemplated getting a trainer at the gym I was going to at the time. I watched all of the trainers really closely while I was working out and had it narrowed down to 2 (yes, 2 out of at least 15) that I would have considered working out with.
My advice? Be picky! If the person you are paired with is just "ok" or "good enough" ask to workout with someone else.
You do not want someone who leans on the machine and stares into space when you are lifting, you don't want someone with no energy, and you don't want someone who just carts you from machine to machine.
If you always put limits on everything you do, physical or anything else, it will spread into your work and into your life. There are no limits. There are only plateaus, and you must not stay there, you must go beyond them. - Bruce Lee
I work out with a trainer twice a week. Started while I was pregnant, and I just planned to do the free intro sessions, but I'm hooked. I am 3.5 months PP and am seeing great results, and I am lifting much heavier than I ever thought I would (which I find very gratifying). I get a much better workout with him than I do on my own, and I have more confidence in pushing myself.
I agree with PP that you do have to be picky. If you don't click with your initial trainer, don't be afraid to request a different one! You don't want to mistake a bad trainer for a bad workout.