Sometimes we underestimate the power of data. There are hundreds if not thousands of companies that would testify to the fact that without their data, they would not exist. People say time is money—and now, data is money. Due to the importance of data, companies need to stay updated on database changes. One way to ensure this is through the use of triggers and email alerts in Microsoft SQL Server. By using triggers and email alerts, you can be easily notified when important changes are made to your database.
Obviously, you don’t want to flood your inbox with notifications. So it is important to apply triggers and email alerts only to important changes. Generally, you may want to apply them to delete and update operations. However, you are free to add them based on preference.
What is a trigger?
A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. A trigger can be defined to execute after insert, update, and delete operations. Consequently, the triggers function would handle the event. In this case, an email alert as a notification would be sent.
Triggers can be applied to tables or views. It’s important to note that it is not possible to define a trigger to execute when data changes are made in two or more tables. As a result, a trigger is associated with only one table. To have better performance, you shouldn’t overload your database with triggers. Fewer triggers mean fewer processes carried out.
How to set up email configuration
Step 1:
Open SQL Server and log in.
Step 2:
Expand the Management tab and right-click on Database Mail, then select Configure Database Mail.
Step 3:
Click “Next.”
Step 4:
Select the “Set up Database Mail by performing the following tasks” option and click “Next.”
Step 5:
Type the profile name and description and click “Add.” Then click “Next.”
Step 6:
Provide the outgoing mail server (SMTP), then click “Basic authentication” and provide your username and password. Click “Next.”
Step 7:
Check the profile you created. Then, click “Next.”
Step 8:
Click “Next.”
Step 9:
Click “Finish.”
Step 10:
Send a test email to verify that it is working.
If you have any issues with the email configuration, you can go to this reference for more information.
To demonstrate how triggers and email alerts work, we’ll create a “person” table and create a trigger for its update and delete operation; consequently, we would receive an email each time data in the Person table is deleted or updated.
We start by creating a database and then creating a table for that database. We’ll name the table “person,” and give it four attributes: “personId”, “firstname,” “lastname” and “nationality.”
-- check if the database has already been created, if so drop it
if db_id('myDatabase') is not null drop database myDatabase;
go
-- Create a database named 'myDatabase'
create database myDatabase;
go
-- select the database you would like to use
use myDatabase;
go
-- Create a table named person and set the data types and attributes
--identity(1,1) basically means auto increment, and start from 1
create table person(
personId int not null identity(1,1),
firstname varchar(50) not null,
lastname varchar(50) not null,
nationality varchar(100) not null);
go
It is good practice to create a backup database with backup tables to keep logs of the activities that occur in the database.
-- create a backup database for myDatabase named 'mydatabase_backup' create database myDatabase_backup go --create a backup table for person with no constraints create table myDatabase_backup.dbo.person_backup( personId int, firstname varchar(50), lastname varchar(50), nationality varchar(100), audit_activity varchar(100), audit_time datetime) go
The following is a trigger created for when information is inserted into the person table—however, its function is to write a log of the activity into the backup table each time the insert operation is used.
-- Create an insert trigger
create trigger insertPerson on
person
for insert
as
declare @personId int;
declare @firstname varchar(50);
declare @lastname varchar(50);
declare @nationality varchar(100);
declare @activity varchar(100);
select @personId = s.personId from inserted s;
select @firstname = s.firstname from inserted s;
select @lastname = s.lastname from inserted s;
select @nationality = s.nationality from inserted s;
set @activity = 'A record has been inserted into the person table';
insert into myDatabase_backup.dbo.person_backup(personId, firstname, lastname, nationality, audit_activity, audit_time)
values(@personId, @firstname, @lastname, @nationality, @activity, current_timestamp)
go
The following is a trigger created for when updating records in the person table. Its function is to write a log of the activity into the backup table each time the update operation is executed. It would then send an email alert to “fre[email protected],” notifying him that a record has been updated.
--Create an update trigger and include an email alert
create trigger updatePerson on
person
for update
as
declare @personId int;
declare @firstname varchar(50);
declare @lastname varchar(50);
declare @nationality varchar(100);
declare @activity varchar(100);
select @personId = s.personId from inserted s;
select @firstname = s.firstname from inserted s;
select @lastname = s.lastname from inserted s;
select @nationality = s.nationality from inserted s;
if update(firstname)
set @activity = 'Updated person firstname'
if update(lastname)
set @activity = 'Updated person lastname'
if update(nationality)
set @activity = 'Updated person nationality'
insert into myDatabase_backup.dbo.person_backup(personId, firstname, lastname, nationality, audit_activity, audit_time)
values(@personId, @firstname, @lastname, @nationality, @activity, current_timestamp)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients = '[email protected]',
@subject = 'Database Record Updated',
@body = 'A record has been updated'
go
This is a trigger created for when deleting records in the person table. Its function is to write a log of the activity into the backup table. It would then send an email alert to “fre[email protected],” notifying him that a record has been deleted.
--Create a delete trigger and include an email alert
create trigger deletePerson on
person
for delete
as
declare @personId int;
declare @firstname varchar(50);
declare @lastname varchar(50);
declare @nationality varchar(100);
declare @activity varchar(100);
select @personId = s.personId from deleted s;
select @firstname = s.firstname from deleted s;
select @lastname = s.lastname from deleted s;
select @nationality = s.nationality from deleted s;
set @activity = 'A record has been deleted from the person table';
insert into myDatabase_backup.dbo.person_backup(personId, firstname, lastname, nationality, audit_activity, audit_time)
values(@personId, @firstname, @lastname, @nationality, @activity, current_timestamp)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients = '[email protected]',
@subject = 'Database Record Deleted',
@body = 'A record has been deleted'
go
Following is a stored procedure which is created to make it easier to insert data into the database. Using this stored procedure, we don’t need to repeat the same code.
--a stored procedure to insert
create proc insertPersonProc
@firstname varchar(50),
@lastname varchar(50),
@nationality varchar(100)
as
begin
insert into person(firstname, lastname, nationality) values(@firstname, @lastname, @nationality)
end
go
Using the stored procedure we created, we can insert three records into the person table.
insertPersonProc 'Frederick', 'Peter', 'Ghanaian' go insertPersonProc 'Peter', 'Frederick', 'Ethiopian' go insertPersonProc 'Fred', 'Peter', 'Ghanaian' go
You can always check to see if the stored procedure executed successfully by viewing the records in the table. You should notice three records in the table.
select * from person; go
Finally, we can delete a record or update a record to receive an email alert.
--delete the record with id 1 delete from person where personId=1; go --update the first name of the person with id 2 to David update person set firstname='David' where personId=2; go
Congratulations! You have implemented a simple database and created your first trigger and email alert. For more info about SQL, you may check out this link.
