0

I have a table where I store errors calculated from a bunch of .csv files that contain prediction and measurements. What I want, is to create an automatic process that will allow me to send an email to my account whenever any row within the table of errors contains a value that exceeds a predefined threshold. I have gone through a lot of documentation online, and as far as I get it I either need to create a trigger or set up a job on the server. However, I am new therefore I have real troubles implementing what I want. Any example would thus be greatly appreciated :)

Nisfa
  • 359
  • 1
  • 4
  • 16
  • Check those: https://stackoverflow.com/questions/10755469/send-e-mail-from-a-trigger https://stackoverflow.com/questions/34150162/sql-server-trigger-to-send-email-on-insert-with-conditions-and-using-table-value – Missak Boyajian Dec 05 '17 at 13:04
  • It *sounds* like you want a SQL Server Agent job that queries a table, and depending on the values it finds, sends [database email](https://technet.microsoft.com/en-us/library/ms175887(v=sql.105).aspx). Loosely speaking, triggers operate on every row change; Agent jobs run every 'n' minutes. This question will probably be closed. Ask another with more code details after you've written some code. – Mike Sherrill 'Cat Recall' Dec 05 '17 at 13:49

1 Answers1

0

Sending e-mail from a trigger is not a good idea.

Any trigger is a part of the transaction, this means your transaction will not commit until e-mail will be sent, and in case of sending error your transaction will be rolled back.

As you said you can use a job to do this.

In the job you just check for the records of interest and if any row is found, send e-mail from a job. Or you can raise an error if any row is found and make a job send you email on failure.

The job can be scheduled to execute every 5 minutes and you can check for new rows using datetime filed: new rows in the table means 

datetime_field >= dateadd(minute, -5, getdate())
sepupic
  • 8,409
  • 1
  • 9
  • 20