In this use case, we will explore using the result of an SQL Query as a trigger to send an alert email. This is useful for verifying that data has been loaded and other validation checks. For example, maybe you want to be alerted when a new support request is generated or a new customer has signed up.
Three easy steps:
- Create an SMTP Connection with details about your SMTP server. Any SMTP server can be used.
- Create a Generic:Database Connection with details about your database. The information in the database will be used to determine if an e-mail is sent.
- Create a Task and select SQL Alerter from the list of engines.
- Use the Alias field to name your Task
- Select Send Email in the Action Type field
- Use the Target SMTP Server field to select the SMTP Connection that you created
- Fill out the From, To, Cc, Bcc, Subject and Body fields as desired
- Use the Target Database Connection field to select the Database Connection that you created
- Add an SQL Query that will determine if an e-mail will be sent. For example,
SELECT COUNT(*) > 0 FROM users WHERE created >= NOW() -1 Day;The actual query line will depend on your database type and content. The example will return true when any new users have been created within 24 hours of when it is run. A true result triggers an email to be sent.
- Click the Create button
Now that you’ve created a Task to send an email based on an SQL Query result, you probably want to run it on a regular basis. Just create a new Sequence to do that. For this example, running the Task daily would make sense, but you can adjust the frequency to whatever you want.
You can test running your Task before it runs on schedule by running the Sequence manually. And that’s it, you have successfully used an SQL query to automatically trigger sending emails.
For more details, use the ? icons next to the fields in the Task or visit our SQL Alerter Engine support page.