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 = 'email@example.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.