SQL Server email trigger when table is updated

Sometimes you have very important tables that you need to be alerted if there are any changes made to the data.  You can setup a trigger that sends you an email whenever an insert/delete/update is done to any of the data.

What is a trigger?  Per Microsoft’s documentation, they define it as: “A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.”

There are many different kinds of triggers, but to really understand all of the possibilities, I highly recommend you check out the documentation:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2016

For our example we have a table called Jim_Test that has important voter registration information.  We need to know if anybody makes a modification to any of the data.  To do this we will setup a trigger.

tr1

 

Before creating this email trigger, you need to already have setup email on your server.  Lucky for you, I have a posting on how to complete this task.  Setting up SQL Server Database Mail

Below is the code to create the trigger.   After it runs, you can see in SSMS that a trigger is now associated with the table.

CREATE TRIGGER [dbo].[trg_Jim_Test] 
ON [dbo].[Jim_Test] 
AFTER INSERT, UPDATE, DELETE 
AS 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'SQL Admin', 
@recipients = 'james_salasek@whereever.com', 
@body = 'Someone made a change to data in the JIM_TEST table', 
@subject = 'Check this out!'; 
GO

tr2

 

Now I will update a record in that table.  The update completes successfully, but now I am also sent an email.

tr3

If I look in my email, I see I received an alert that somebody made changes to the table.

tr4

 

This trigger is a quick way to be notified if any sensitive data has been modified.  If you also enable auditing, you have a very sophisticated tracking mechanism of any data changes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s