I need to design a system to send out emails that are user driven (ex. user requests password, system admin wants to send emails to all users), conditionally driven (ex. a PC has failed to communicate with the server for X time) and conditionally event driven (a PC switches from red status to green status).
I'd like to encapsulate all Business/Sending logic in one layer as much as possible (so I don't want to initiate send emails from the app layer and then have sql jobs that send other types). I also want to track what emails have been sent, when sent, etc.
I'm thinking about using an Outgoing email table to hold all the notifications (with stored procedures hooked both to the app layer and sql jobs that put the emails into those tables). I was going to put an AFTER INSERT trigger on the table so that critical alerts could be sent immediately. At night, I would poll the table to send out all other, non time critical emails (like new account notifications, expired account notifications, etc). Reason not to initiate sending via trigger for all emails is that some notifications could be inserting 1000's of rows at a time and I don't want to "overwhelm the trigger".
Does any of this sound reasonable? Alternative solutions?