Email Reminder on Updating a Table
A table named Salary can be created with columns - EmpName, BasicSal and NetSal for this example.
In this post you will get to know - how to send an email reminder after any change e.g. add or delete operation on a table.
This can be possible with the help of Triggers and one inbuilt Stored Procedure named "msdb.dbo.sp_send_dbmail". Triggers will help us to know about the time of changes in our given table & the stored procedure will lead us in sending emails.
Lets take a simple example:
Now, for understanding, we will create a trigger on table for INSERT and DELETE operation in which if anyone adds or deletes record in the table, then an email (with stated content in mail section of trigger) will be sent.
We are using table named "Salary". It has 3 columns viz. EmpName, BasicSal, NetSal. We will add a trigger for this table for INSERT or DELTE. This trigger itself will have the SQL statements for sending an email on insertion or deletion.
In the end of trigger, a stored procedure named "msdb.dbo.sp_send_dbmail" is called which will help us in sending emails from SQL server after insert or delete operation. It requires 4 parameters to be given for sending email alerts on table updations.
Paramters:
a) Profile name: It is the name of the profile for which records are being stored for SMTP settings. Profile name can be found in MSDB database’s dbo.sysmail_profile table.
b) Recipients: It will have the email ids of recipients.
c) Subject will have the subject string.
d) Body will be having message body.
Let’s see it in working:
We will write a query i.e. INSERT INTO Salary VALUES(Avashish1245123,100,100); & mail will look like:
Other configurations that may be required:
For sending email reminders one has to set up few configurations. Sending of email alerts can be possible with the help of one inbuilt Stored Procedure "msdb.dbo.sp_send_dbmail". But for using this procedure, developer has to execute few configuration settings and have to create profile in the system tables which are saved under MSDB db.
We will go through one by one.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
sp_Configure is the procedure which is used to show or update global configuration settings for the current server. Use the show advanced options option to show the sp_configure system procedure’s advance options. With the 1 here, you can list the advance options by using sp_configure. The default is 0. These settings will affect server immediately without restart.
And RECONFIGURE will update the currently configured value of a configuration option changed with the sp_configure system procedure. Because some configuration options require a server stop and restart to update the currently running value, RECONFIGURE does not always update the currently running value for a changed configuration value.
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Database Mail XPs option with sp_Configure is used to enable Database Mail on server. The possible values can be:
0 which indicates that Database Mail not available (by default).
1 states that Database Mail is available.
The settings will take effect immediately without restarting a server.
Thereafter for creating mail profile related configurations, we need to execute below 4 queries which are given below one after the other.
1) sysmail_add_account_sp
First, execute the below stored procedure:
a) The name of the account.
b) A description of the account.
c) The e-mail address of the account.
d) The display name for the account.
e) The e-mail address to use as the reply-to information for the account.
f) The name of the e-mail server.
More info can also be given, like:
E-mail server type. Simple Mail Transfer Protocol (SMTP) is always used for MSQL (Microsoft SQL Server).
E-mail server’s port number.
User name & password for authentication to the e-mail server.
2) sysmail_add_profile_sp
Then, we have another query to execute for adding mail profile.
3) sysmail_add_profileaccount_sp
After this, another stored procedure needs to be executed which is referred to sysmail_add_profileaccount_sp. It will add a DB Mail account to a DB Mail profile. To do this, we need to execute following query:
4) sysmail_add_principalprofile_sp
Then, last but not the least , one stored procedure has to be executed i.e.
By giving principal_name as 'public', makes this profile a public one and, granting the access to all the principals in the DB. Is_Default will set the principal as default for the database.
I was able to receive the emails by
- first creating the Salary table then
- running the code under "Other configurations that may be required" then
- the Trigger code and
- lastly executing the INSERT statement.
Do remember to change your email addresses and mail server names etc.
A table named Salary can be created with columns - EmpName, BasicSal and NetSal for this example.
In this post you will get to know - how to send an email reminder after any change e.g. add or delete operation on a table.
This can be possible with the help of Triggers and one inbuilt Stored Procedure named "msdb.dbo.sp_send_dbmail". Triggers will help us to know about the time of changes in our given table & the stored procedure will lead us in sending emails.
Lets take a simple example:
Now, for understanding, we will create a trigger on table for INSERT and DELETE operation in which if anyone adds or deletes record in the table, then an email (with stated content in mail section of trigger) will be sent.
We are using table named "Salary". It has 3 columns viz. EmpName, BasicSal, NetSal. We will add a trigger for this table for INSERT or DELTE. This trigger itself will have the SQL statements for sending an email on insertion or deletion.
--Query [Trigger Code] :
CREATE TRIGGER [dbo].[tr_Ins_Del_Salary]
ON [dbo].[Salary]
AFTER INSERT, DELETE
AS
SET NOCOUNT ON
DECLARE
@EmpName VARCHAR(50),
@CustEmail VARCHAR(255),
@body VARCHAR(MAX),
@sub VARCHAR(100)
IF EXISTS (SELECT * FROM INSERTED) --Will be executed in case of Insertion
BEGIN
SELECT
@sub = 'Record Inserted',
@EmpName = ins.EmpName,
@CustEmail = 'abc@xyz.com',
@body = 'Hi' + CAST(ins.EmpName AS VARCHAR(50)) + ', Record inserted:
Basic Sal:' + CAST(ins.BasicSal AS VARCHAR(50)) + ', NetSal: ' +
CAST(ins.NetSal AS VARCHAR(50))
FROM INSERTED ins
END
ELSE IF EXISTS (SELECT * FROM DELETED) --Will be executed in case of Deletion
BEGIN
SELECT
@sub = 'Record Deleted',
@EmpName = d.EmpName,
@CustEmail = 'abc@xyz.com',
@body = 'Hi' + CAST(d.EmpName AS VARCHAR(50)) + ', ' + @sub +
': Basic Sal:' + CAST(d.BasicSal AS VARCHAR(50)) + ', NetSal: '
+ CAST(d.NetSal AS VARCHAR(50))
FROM DELETED d
END
-- Statements for Email sending
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test Profile', -- Created by own in Configuration of DB
@recipients = @CustEmail,
@subject = @sub,
@body = @body
The above trigger is created for Insert or Delete in ‘Salary’ table. The trigger gets executed for INSERT or DELETE and accordingly the corresponding block of code will be executed. From the variable names we can easily find what value is required to be given.In the end of trigger, a stored procedure named "msdb.dbo.sp_send_dbmail" is called which will help us in sending emails from SQL server after insert or delete operation. It requires 4 parameters to be given for sending email alerts on table updations.
Paramters:
a) Profile name: It is the name of the profile for which records are being stored for SMTP settings. Profile name can be found in MSDB database’s dbo.sysmail_profile table.
b) Recipients: It will have the email ids of recipients.
c) Subject will have the subject string.
d) Body will be having message body.
Let’s see it in working:
We will write a query i.e. INSERT INTO Salary VALUES(Avashish1245123,100,100); & mail will look like:
Other configurations that may be required:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test',
@description = 'Mail account for Testing.',
@email_address = 'sachin@gmail.com',
@replyto_address = 'sachin@gmail.com',
@display_name = 'Test Automated Mailer',
@mailserver_name = 'Mail Server Name' ;
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Test Profile',
@description = 'Profile used for administrative mail.' ;
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Test Profile',
@account_name = 'Test',
@sequence_number =1 ;
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test Profile',
@principal_name = 'public',
@is_default = 1 ;
Explanation of above configuration:For sending email reminders one has to set up few configurations. Sending of email alerts can be possible with the help of one inbuilt Stored Procedure "msdb.dbo.sp_send_dbmail". But for using this procedure, developer has to execute few configuration settings and have to create profile in the system tables which are saved under MSDB db.
We will go through one by one.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
sp_Configure is the procedure which is used to show or update global configuration settings for the current server. Use the show advanced options option to show the sp_configure system procedure’s advance options. With the 1 here, you can list the advance options by using sp_configure. The default is 0. These settings will affect server immediately without restart.
And RECONFIGURE will update the currently configured value of a configuration option changed with the sp_configure system procedure. Because some configuration options require a server stop and restart to update the currently running value, RECONFIGURE does not always update the currently running value for a changed configuration value.
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Database Mail XPs option with sp_Configure is used to enable Database Mail on server. The possible values can be:
0 which indicates that Database Mail not available (by default).
1 states that Database Mail is available.
The settings will take effect immediately without restarting a server.
Thereafter for creating mail profile related configurations, we need to execute below 4 queries which are given below one after the other.
1) sysmail_add_account_sp
First, execute the below stored procedure:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test',
@description = 'Mail account for Testing.',
@email_address = 'abc@zyc.com',
@replyto_address = 'xyz.yu@server.com',
@display_name = 'Test Automated Mailer',
@mailserver_name = "mail server name" ;
With this execution, it adds the information related to Database Mail Account. This information is stored in the msdb. Each account will consists of the below information:a) The name of the account.
b) A description of the account.
c) The e-mail address of the account.
d) The display name for the account.
e) The e-mail address to use as the reply-to information for the account.
f) The name of the e-mail server.
More info can also be given, like:
E-mail server type. Simple Mail Transfer Protocol (SMTP) is always used for MSQL (Microsoft SQL Server).
E-mail server’s port number.
User name & password for authentication to the e-mail server.
2) sysmail_add_profile_sp
Then, we have another query to execute for adding mail profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Test Profile',
@description = 'Profile used for administrative mail.' ;
It will create a new Database Mail profile. It will return 0 in case of success else 1 for failure. Profile_name is the parameter which is used to specify the name of the profile. This profile name will be used in our consumption query (which is in trigger example). And Description can be used to add description for the Profile.3) sysmail_add_profileaccount_sp
After this, another stored procedure needs to be executed which is referred to sysmail_add_profileaccount_sp. It will add a DB Mail account to a DB Mail profile. To do this, we need to execute following query:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Test Profile',
@account_name = 'Test',
@sequence_number =1 ;
Account_Name is given to the corresponding profile. Sequence number to be given with the help of sequence_number parameter.4) sysmail_add_principalprofile_sp
Then, last but not the least , one stored procedure has to be executed i.e.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test Profile',
@principal_name = 'public',
@is_default = 1 ;
With this stored procedure, we can grant permissions to a database user to use a DB Mail profile.By giving principal_name as 'public', makes this profile a public one and, granting the access to all the principals in the DB. Is_Default will set the principal as default for the database.
I was able to receive the emails by
- first creating the Salary table then
- running the code under "Other configurations that may be required" then
- the Trigger code and
- lastly executing the INSERT statement.
Do remember to change your email addresses and mail server names etc.