CASEDATES Table
CaseDateID CaseNumberID CaseReviewDate CaseStatus
1 1180 03/02/2019 Closed
2 1190 03/03/2018 Open
CASELOG Table
CaseLogID CaseNumberID LogAction LogDate
9999 1180 13 04/1/2019
10000 1180 14 04/1/2019
The goal is to send an email notification when a log is recorded and the log action is 13 or 14. How do I accomplish this with a Table Trigger on INSERT? And would this be the best practice or will it slow down the application?
thanks in advance
You typically would never want to include sending an email in a trigger, just too many ways for things to go wrong / slow down.
One way around that is to have the trigger start a job that looks for emails to send. The msdb.dbo.sp_start_job returns control to the caller immediately after starting the job, so it doesn't add time to the trigger.
1 Like
I have a msdb.dbo.sp_send_dbmail that can be excuted once the criteria/condition is met. I was planning on putting the condition in the trigger and if it is true, exec msdb.dbo.sp_send_dbmail. Will that change the performance and is it possible? thanks
It's certainly possible to do it, but it could affect performance. In theory, at times, it could significantly delay processing in the trigger.
It's standard to use a job or queue or some other method to initiate emails. The job can call the proc instead of the trigger. It just separates the email processing from the trigger processing.
Are you going to send one email per insert - or one email per row? The problem with this approach is what happens when you batch insert a thousand rows?
If you are expecting one email per row - with different criteria (e.g. different recipients, subject, message) then you need a cursor, and performing a cursor in a trigger will cause an impact on the application.
If all you are doing is checking to see if one or more rows inserted have a specified value - and call out to sp_send_dbmail one time, then it probably won't have a significant impact since sp_send_dbmail queues the email.
With that said - you would probably be better off creating separate code that checks the table for qualifying data and sends your emails. That code can then be scheduled in an agent job to run every xx minutes.
Hello, how can this be implemented on Azure sql server? There’s no sp_start_job.
Thank you
Thanks for the input everyone. I was able to put an email trigger to the table AFTER INSERT succesfully. It was originally creating problems in the application when I combine the email funtion into a StoredProcedure. It is very difficult to work with encrpyted functions and SPs in the database, apparently there are other things that throws the system off which we cannot see.