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:
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.
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
Now I will update a record in that table. The update completes successfully, but now I am also sent an email.
If I look in my email, I see I received an alert that somebody made changes to the table.
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.
Jim Salasek's SQL Server Blog 
Can this be done that an email is sent once a table is not updated after 10 minutes?
LikeLike
I did a quick search and you can try something like this link below. You will need some kind of timestamp field on the table to compare that no new data has been updated. If there is not a last update field in the table, I cannot think of a way how to do it because you need something to compare against.
https://community.spiceworks.com/topic/449552-get-alert-when-tables-in-a-sql-database-don-t-update
LikeLike
I had a look there and it looks okay , I have a last updated field in every row, could I use that , If so , how?
LikeLike
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.
LikeLike
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?
LikeLike
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
LikeLike
And this checks if a table hasn’t been updated after a Certain number of minutes then once it’s a sql agent job?
LikeLike
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())
LikeLike