Hrm. Being male, I cannot imagine exactly how this must feel. However, being an experienced piercer, I have done this for others more than once - both in temporary and permanent versions. A few points worthy of note:
1) Most, but not all women have anatomy suitable for this. The hood itself has to be large, thick and stable enough to support the piercing, and this also affects the gauge.
2) I only do the piercing before arousal. If the clit itself is enlarged, it makes the piercing much harder to do (not impossible, I suppose, but why make my task any harder than it already is).
3) The pain is subject to the gauge of the piercing. I've done temporary vaginal piercings as small as 33g that have only very minimal pain, and are quite fun. The largest I've done was a piercing at 18g. Again, being male, I cannot compare, but I can rather comfortably say that my piercing subject was not comfortable.
4) If you want to keep the piercing, you cannot take it out. Granted, vaginal piercings generally heal quickly (relative to other piercings - vaginal tissue is designed naturally to heal well), but that varies greatly from person to person. But Susie is right - this isn't likely something you will be able to hide from your hubby. Even once fully healed, if you want to keep he piercing open, just like any other piercing, you will need to wear something in it. You could, I suppose, remove said jewelry for encounters with your hubby, but the piercing itself would likely be visible. Now if you wanted a temporary (a few hours or less) piercing, that will likely heal rather quickly, and would not be noticeable past a week or so, but even that is not guaranteed.
5) The experience from wearing a piercing greatly varies from person to person. I've heard some reports that the piercing GREATLY enhances intercourse, as the jewelry causes stimulation. I've even heard reports from women who have been unable to keep the piercing, as it causes too much stimulation, especially in day-to-day activity. Conversely, while hood piercings are generally safe, I have heard cases where it caused decreased sensitivity, especially with larger piercings (clit piercings are much more prone to this, and I refuse to perform them for that reason). I cannot comment on the attractiveness of a genital piercing for men, however, and I know that for some it would be a turn on, and others, a turn-off.
It is definitely a very personal choice, but not one to be made lightly. It is also noted that this post is written in first-person for a reason - it reflects only my personal experiences, and is not a collective of all knowledge there is to find on the subject - just like you will find artists with vastly differing opinions on painting styles, you will also find piercers with vastly differing opinions on piercing styles, including genital piercings. Whatever the case, should you do it, make sure it's someone who knows what they are doing, and whom you trust without exception.