This is something I wish iOS5 had. Alas, it does not - however if it fails to send as iMessage with send as SMS disabled, it will allow you to tap / hold (I forget which) and specify to send as text.
The main caveat to this, though, is that it doesn't 'alert' you per-se.
Sound on iPhone is reasonably complicated when you get down and start "taking it apart". This is because it is managed on a very much case-by-base basis.
For example, if you call up Siri, and adjust the volume, it will only be adjusting the volume for Siri, not something like the ringer volume.
So to divide things up and try to answer your question, I'll start with the basics.
If you turn on Vibration mode (orange position) using the switch on the side of the phone it will turn off almost all sounds. Unlock, keyboards and such sounds will no longer be on. Notifications and phone calls will vibrate but not be heard. You will have to turn the switch off (no orange) in order to hear normal sounds while in apps - (i.e. games). When alternating vibration mode versus normal mode, iPhone remembers your previous state, and will set the volume to what it was before vibration mode was active.
Exception: alarms set in the Clock.app are still heard. In fact, there is no way to turn off Clock.app's alarms from being heard unless you turn off the alarm(s) itself. As for other alarm clock style apps (for example, Sleep Cycle), it is up to the developer to make it behave like Clock.app. That is the case with Sleep Cycle, but it would be worth verifying with any other alarm style app.
If you turn down the volume in the Music.app (or double tap home button and swipe to the right twice), or while watching a video, or in Settings.app, that is the volume that is applied across the board, mostly. That volume reflects the volume for videos, music, and apps that in general make noise - as in, games. Note that iPhone remembers your settings when you have headphones plugged in vs. not.
When iPhone is unlocked and you are on the home screen and you adjust the volume, you are adjusting ringer volume. In other words if the vibration switch is off (no orange) then that would be the volume it would ring at.
Do-not-disturb suppresses basically all notifications (there are some configuration options there). Again, alarms (from Clock.app, and other properly set up apps like SleepCycle) will stay play.
The only thing you can't do is have it toggle sound settings when it is plugged into the wall vs. when it is not. I think you will find it becomes habit, however, to throw the vibration switch.
Best Answer
Although it's not exactly what you might be looking for, if under Settings / Sounds / Sounds you set Text Tone to None, no sound will me made, neither when receiving nor when sending a Message.