It's obviously a very difficult situation to deal with and will only be resolved over time.
You do love each other and you both want to make it work. There are many reasons you might want to stay together, not least that you do love each other, but also because of the children and because of a myriad of social, practical and financial reasons. And no-one wants to fail at a marriage.
But something that HW said: 'My fear is that my h is staying for the wrong reasons (although i do think he is not clever enough to keep up such a pretence for a long time). He will often say he is scared of losing everything as he came so close before - but i would rather hear he is here out of his utter love for me not fear.'
And I agree with MadameOvary: 'It is when that dynamic begins to shift that things get complicated, because if you do stay with him then you will have to find a way to treat him with love,trust and respect, and all the things that make for a successful fulfilling relationship (it goes without saying that you would need this from him too)
Otherwise you may want to punish him for the rest of his life, and miss the chance to be happy, either on your own or with someone else.'
My friend's father had an affair about 35 years ago. Her mother has never fully forgiven him. They are old now and unwell. Maybe they didn't talk about it enough at the time but my friend hates the bitterness of the situation, the arguments about other things that are really about the affair which is not spoken of. The way her mother has always confided in her and she doesn't want to hear it. She never wanted to take sides. She wishes they had separated years ago.
You have just found out and both you and he are upset. It seems that he definitely wants to stay with you, which is a relief, and he is desperate right now to make amends. But it is when this first phase ends that things get difficult. And it all comes down to whether it is possible to rebuild the trust and respect between you.
Hopefully he is just a 'fuckwit' who was just greedy or dealing with some insecurity he had or working out some issue in your marriage that he should have spoken to you about.
It sounds as if the other woman and he were just using each other and that the attachment is not very great.
I agree that he has to tell you everything now in return for you giving him a chance to mend this. Why did he do this (apart from the obvious reason - because he could)?
In your place, I would have to be careful of what HW has said: Is he staying with me because he loves me and can't bear to lose me (it was just a silly mistake) or does he want to stay with me because he can't separate 'it' from me - the children, the house, the finances, the lifestyle, the social circle, the nice settled social status. (I think Xenia has quoted one of her friends as saying that he'd like to keep everything the same, just to swap the wife for the lover.)
If it is the latter, you would have to ask yourself over time whether you can, or should, tolerate that and whether it is really the best thing for your family. In your place, I would ask him which it is and see how he answers. Given what you have found out so far, hopefully his answer will be spontaneous and reassuring and will encourage you to move forward.
If not, then you would have to dig deeper to understand what has gone wrong, if anything.
The obvious thing is to see it as 'boys will be boys' and you will forgive him providing he is really sorry and doesn't do it again. But you really would want to make sure it is just that. That the affair is not ongoing. That he hasn't done it before. That he doesn't think that, once the coast is clear and things have settled down, he can do it again.
Also that You are not wanting to save the marriage just because of those same reasons of the social stigma of divorce, the children, fear of being alone/single parent, the house, finances, etc.. These may seem very important right now but they may not be adequate consolation for you if the trust between you and him cannot be rebuilt.
It does sound hopeful though.