Hi,
I have a requirement to generate an email notification if there is a new record added into a SQL table and meets a criteria based on the value in a column.
Back in the days SQL notification services was working fine but unfortunately Microsoft discontinued that product.
Would like to hear from Pros any suggestions to accomplish this via triggers or any other process or 3rd party tools.
Thanks,
Hope this helps
you have a few options
- Trigger based
https://sweetcode.io/using-triggers-and-email-alerts-in-microsoft-sql-server/ - SSIS
using a logger table that keeps track of the last PK of a table that was last processed (so as to avoid re sending email for last data change for same row) and the column that changed (in case you need to inform recipient of multi column changes) if there are any changes to target column send an email using ssis. - Powershell
same as above but using powershell - SQL Job
same as above but using purely sql script.
I like SSIS myself
Thanks yosiasz for the quick reply and suggestions.
SSIS looks promising do you have an example of this like trigger based that would help me a lot.
check out the link I attached for trigger based
In all options - except for the trigger option - you must be able to determine the rows that qualify and have not already been processed. Each of these options will also be scheduled to run on some set frequency - every minute, every 5 minutes, every hour, etc...
How that will be accomplished will depend on what columns are available in each table. For example - if you have a last updated/created date/time column - that can be used. If you have an identity column - that could also be used (but you then need to keep the last processed identity in another table).
If you rely on an identity column - then you can only consider 'new' (inserted) rows and not rows that have been updated and now meet the requirement.
In all cases - you need to consider times when the job does not run and how to handle that scenario.
1 Like