Request for info - detecting DB changes and batching for e-mail

I have a .NET app recently ported from 1.1 to 2.0 with a pending feature request. I'd first like to state that I'm not asking anyone for the programmatic answer, unless of course, you'd like to provide it. I'm simply asking the best way to accomplish this task with the tools I have available to me (VS 2005, SQL 2000 and 2005). I do not consider myself a professional .NET or T-SQL programmer but I do have a good understanding of the technologies so that I can find what I need to know once I know I'm going about something the right way.

The application I wrote, from scratch, manages News, Featured Connections, and FAQs for a web site. It was designed in a multi-layer approach where there are:

items -> assigned to categories -> assigned to users

So if user Fred is granted rights to category A, when he authenticates to the app he can Add/Change/Delete any item in category A. Simple enough. But also what Fred can do is see all the other items other users have created and *schedule* any relevant item related to his web page to appear within his own items even tho he has no rights to change that item in the admin interface.

The feature request is a notification service, so that Fred gets an e-mail when someone has added a new item or edited an existing one. I don't want Fred to get an e-mail *every* time an item is created or edited, that would be big bother and would generate lots of e-mails. What I'd like to do is send Fred an e-mail sometime in the middle of the night that informs him of the items added or changed since his last login, so that at his choosing he can go into the admin interface and schedule one or more of those items for his page.

Should I just write a new console app that does this, compile it and schedule the .EXE to run at midnight? Or is there a better way to leverage the new features in SQL 2005 (extended strored procs, triggers) and .NET to do this a better way? Currently, the DB is in SQL 2000 but I have no problems moving it to SQL 2005 if that helps me in some way. Thanks in advance for your comments.

...Bill

Table UserLastNotify

ID (useridtype - int/uid/whatever)

LastChangeSeen int

Create trigger on your categories table, recording a changeid, time,categoryid that changed to an auditCategories table.

Create a stored procedure (or cursor) that checks the last changeid in the auditCategories table to their entry in the UserLastNotify table, and then filter based on whatever criteria you want them to be notified about. Then generate the email, and update the UserLastNotify table with the current max of auditCategories (alternatively you can also use the max of the auditCategories id that you generated an email for them - since any higher you either haven't seen, or don't care about).

Create an sql job that runs at midnight every night that calls your stored procedure.