If you find yourself needing to send an email automatically upon certain events occurring in SQL Server, you can do this via a trigger.

For example, you could automatically send an email when somebody deletes or updates a record from a table, etc.

To do this, you need to create a trigger that includes code for sending the email upon the required event.

Example

Here’s an example of a trigger that sends an email whenever someone deletes a record from a table called Cats:

CREATE TRIGGER trg_deleted_cat  
ON Cats  
AFTER DELETE   
AS  
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DB Admin Profile',  
    @recipients = '[email protected]',  
    @body = 'Oh no... somebody just deleted a cat!!!',  
    @subject = 'Warning: This email may distress the reader';

In this case I want the email to be sent whenever someone deletes a row, so I use AFTER DELETE.

I use the sp_send_dbmail stored procedure on the msdb database to send the email.

In order for this to work, you need to have Database Mail enabled and configured to send emails (Database Mail is SQL Server’s solution for sending emails). See How to Send Email from SQL Server for instructions on how to do this.