In working with a client the other week we came across a bit of odd functionality...
Namely that after you upgrade a SharePoint implementation to MOSS 2007 existing alerts do not seem to function correctly.
KB#936759 http://support.microsoft.com/kb/936759/en-us
E-mail notifications for alerts are not sent when content in a migrated list or in a migrated document library changes after you perform a database migration to upgrade to Windows SharePoint Services 3.0
In a nutshell, the title says it all...
the solution is some raw C# code...
I built a command line utility using this code to make it easy to use. you may grab it here: V3UpdateAlertsKb936759.zip (11.13 kb)

Update: 5/15/08:  MS has released a set of tools that includes this functionality you may grab it here: SharePoint Administration Toolkit x86 or here SharePoint Administration Toolkit x64
A second issue you may encounter with Alerts is when you move a Web Application or Sites (i.e. do anything to change the URL of your site).
This also breaks the alerts (they point to the old location). 
The SharePointers blog has a good article on it SharePoint alerts: how to repair them after a web app move
Again it is raw C# code....
I took and built another command line utility for this as well... you may grab it here: SPSiteAlertUrlUpdate.zip (3.00 kb)