Sometimes we want to send an email when something suspicious happens in a database. SQL Server is able to natively send emails.
Example: We want to receive an alert every time a user is deleted, so that we can immediately check if this happened for a good reason or not.
We could do this using external tools that regularly connect to the database and perform some checks about the most important data. However this would be overcomplicated, and requires the installation of some tools.
Here we’ll see how to achieve the same result with a trigger.
CREATE TRIGGER AuditUserDelete ON dbo.user FOR DELETE AS DECLARE @UserId INT SELECT @UserId = id FROM deleted EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA', @recipients = 'firstname.lastname@example.org', @body = 'User id: ' + @UserId, @subject = 'ALERT: User was deleted' GO
Here’s what happens when a user is deleted:
- The deleted user’s id (from the
deletedtable) is copied into the
msdb.dbo.sp_send_dbmailsends an email.
SQL Server documentation: