I've wrote a lot about SQL Server alerting recently including posts on what alerts you should set up, how to use them, how they fit alongside third party monitoring and how to test them too. I've really enjoyed writing these and if you are looking to implement them (and you should) here's a handy post detailing all of the different posts I've wrote:
SQL Agent Alerts - An introduction to SQL alerting and what alerts you need.
A bit more on SQL Server alerting - setting notifications and using native alerting sensibly.
Even more on SQL Server alerting: An example - setting up alerts with third party monitoring applications.
Raising errors - Testing SQL Agent alerts - using T-SQL to trigger our alerts
There's plenty of content here on how to start using SQL's native functionality to provide an effective alerting layer to trap and handle errors. If you have any questions, comments etc then feel free to do so :-)