Monday, May 16, 2011 7:13 AM
Hello experts... I am not sure if this is the right place to ask my below question:
My requirement is to send emails to different users on the event of changing a column value in the database table. The users will be able to configure the column in the database along with the value of it based on which the email will be sent. E.g.: If 'Status' column value of the table 'tblTask' is changed to 'Completed', then an email should be sent to the task owner ('owner' is a column in tblTask that contains the email address). Same type of configuration will be made on any other tables.
My first approach was to create audit tables in the database for each main table and to check the value change (previous and now) to determine whether or not to send email. But I am wondering if there is any efficient and smart way available that can be used to accomplish this job.
FYI, the emails will be created from templates (outlook .oft or word .doc files) that will contain predefined formatted text in the body and will be mapped in the configuration section. So, the process responsible for sending the emails should be able to monitor the tables for the value change for the configured columns and will create emails from the template, and will send them to the users accordingly.
Any suggestions on this will be highly appreciated.
Thanks in advance...