Creating Email Triggers in SQL Server 2005
What exactly is a trigger?
It is a piece of SQL that is activated when a certain event happens or occurs. A common usage is when a new record is added to a database, this triggers an action - for example, a new member triggers an email to the admin for the registration. Or you have certain records in a database table that are assigned to a particular customer and, upon that record being updated, that customer/admin should be notified of the change.
Triggers are useful to fire events and perform some action when data is inserted, updated, or deleted from a table. Microsoft SQL Server's xp_sendmail stored procedure can be invoked to send an email to one or more recipients.
Creating the Table
We will start by creating a table which will hold Customer Information that need to be updated. The name of the table is CustomerInfo
or you can use the following Sql Script
|
CREATE TABLE [CustomerInfo]
( [CUSTOMER_ID] [int] IDENTITY(1,1) NOT NULL,
[FIRST_NAME] [varchar](50) NULL,
[MIDDLE_NAME] [varchar](50) NULL,
[LAST_NAME] [nvarchar](50) NULL,
)
GO |
Inserting the Data
Now, we will insert some dummy data into the table. You can directly insert the data into the table by using the Grid Format or you can just write the script to do that.
or you can use the following Sql Script
|
INSERT INTO [CustomerInfo]([CUSTOMER_ID],[FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES (1,'Amjad','Ali','Leghari')
INSERT INTO [CustomerInfo]([CUSTOMER_ID],[FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES (2,'Ali','Raza','Shaikh') |
Creating the Trigger
Now coming down to the business, we will create the trigger for the CustomerInfo table, but there are few steps that need to be checked before creating the trigger.
- Check that the trigger already exists
- If the trigger already exists, then delete it
If the trigger doesn’t exists, now you can create the specified trigger. Right click on the Trigger section and click on “New Trigger”.
This will generate the following template.
|
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO |
So, now we end up writing the following trigger
|
-- Create Trigger with name 'CustomerUpdateMail'
CREATE TRIGGER CustomerUpdateMail
-- and the table is CustomerInfo
ON CustomerInfo
-- trigger is fired when an update is made for the table
FOR UPDATE
AS
-- holds the CustomerID so we know which Customer was updated
declare @CustomerID varchar(10)
-- holds the body of the email
declare @body varchar(2000)
-- holds the old customer name which has been changed
declare @CustomerName varchar(10)
-- holds the new customer name
declare @CustomerNewName varchar(10)
-- gets the previous customer first name that was deleted
SELECT @CustomerID = Customer_id,
@CustomerName = d.First_Name
FROM deleted d
-- gets the new customer first name
SELECT @CustomerNewName = First_Name
FROM inserted
SET @body = 'Customer with ID=' @CustomerID ' has been updated
with previous First Name is ' @CustomerName '
and the new First Name is ' @CustomerNewName
--xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = 'ali_raza_shaikh@datasprings.com',
@subject = 'Customer Information Updated',
@message = @body
GO |
So, after writing the trigger, execute it to create the trigger and you can see the newly created trigger under the trigger section.
xp_sendmail extended stored procedure
Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message. In order to use this extended stored procedure you must have SQL Mail Setup. You must grant EXECUTE permissions for the xp_sendmail procedure in the master database. After completing all these steps, the following lines will send the mail to the target recipient.
|
--xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = 'ali_raza_shaikh@datasprings.com',
@subject = 'Customer Information Updated',
@message = @body |
Testing the Trigger
After the table has been created, we inserted the dummy data into the tables and in the end created a trigger to mail the user about the customer update, now its time to execute the trigger. You can execute the trigger by using the following lines.
|
update customerinfo
set First_Name='Ali Update'
where customer_id=1 |
Resources
xp_sendmail (Transact-SQL)