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.

8 thoughts on “SQL Server email trigger when table is updated”

      1. You will need to run a stored procedure with an agent job every ten minutes comparing the last updated field to the current date….pretty much like the blog post above shows you. You would need to have SQL Mail setup and configured, SQL Agent running and then you would just use their code as a guide modifying for your table names. I do not see a way to do this with a trigger.

        Running through specs of your environment is not easily accomplished through a blog comment.

        Like

  1. I have SQL Mail configured and SQL Agent running. It looks to me in the blog post they are running two stored procedures to do it. One checking the time and one sending the email?
    Is that correct or am i confused?

    Like

    1. You do not even need a stored procedure. This code works for me, you just need to change the values for your system. Change the field name and the tablename in the IF statement, and change the profile name and your email address. Run this code in SSMS to test, and then just run this as an agent job.

      if (Select max(lastupdated) from Testname) < DATEADD(mi, -10, getdate())
      begin
      exec msdb.dbo.sp_send_dbmail
      @profile_name = 'SQL Admin',
      @recipients = 'yourname@gmail.com',@subject = 'warning',
      @body='Your Table did not update'
      End

      Like

      1. And this checks if a table hasn’t been updated after a Certain number of minutes then once it’s a sql agent job?

        Like

  2. Yes, that is what this section does below, change the values to what your tables are. You also need to schedule the agent job to run every 10 minutes.

    if (Select max(lastupdated) from Testname) < DATEADD(mi, -10, getdate())

    Like

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