• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 837
  • Last Modified:

Help with Trigger - Send E-Mail when data has been changed

Hi..
I need help with a Trigger.
I have a table with ADDRESS, CITY, STATE
If on an UPDATE - if the address changes (i.e.  123 Main Street TO 123 Main Ave)
I need to send an E-MAIL notifiying a user of the Change (using Xp_sendmail)
That shows the OLD Value and NEW Value in the E-Mail text
thanks in advance
0
JElster
Asked:
JElster
  • 12
  • 7
1 Solution
 
RiteshShahCommented:
have a look at

http://www.sqlhub.com/2009/04/email-from-insert-trigger-with.html

I have used pseudo table inserted but you can use inserted and deleted for update the data.
0
 
RiteshShahCommented:
please be informed that, if you have not set database mail than you won't be able to send an email from your trigger so first step would be setup database mail. here are few links for that.

http://www.kodyaz.com/articles/sql2005-database-mail.aspx

http://www.sqlserverclub.com/articles/how-to-set-up-database-mail-on-sql-server-2005.aspx

http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JElsterAuthor Commented:
How do I check if the data has changed?
I need to email the OLD and NEW values
thanks
0
 
RiteshShahCommented:
please have a look at my second post,

when you update the data, two pseudo table will be accesible in trigger, old value goes to DELETED pseudo table and new value goes to INSERTED pseudo table. it is described in the link given in my second post.

here is the same link for your convenience.

http://www.datasprings.com/Resources/ArticlesInformation/CreatingEmailTriggersinSQLServer2005/tabid/807/language/en-US/Default.aspx
0
 
RiteshShahCommented:
if you don't want to look in details in all above links than simply, you can do something like below code.
create table addressOfemp
(
name varchar(20),
address varchar(50),
city varchar(10)
)
go
insert into addressOfemp values('ritesh','a-206 bhavya appt','ahmedabad')
go
 
--trigger
-- Create Trigger with name 'CustomerUpdateMail'
CREATE TRIGGER CustomerUpdateMail
-- and the table is CustomerInfo
ON addressOfemp
-- 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 @CustomerName varchar(10)
   
   -- holds the body of the email
   declare @bod varchar(2000)
   declare @sub varchar(100)
  declare @oldAdd varchar(50)
  declare @newAdd varchar(50)
   
   -- gets the previous address that was deleted
   SELECT @oldAdd = address
   FROM deleted 
 
   -- gets the new address
   SELECT @oldAdd = address
   FROM inserted
 
          SET @bod = 'old address=' + @oldAdd + ' has been updated
          with new address' + @CustomerName 
  set @sub='customer address is updated' 
 
   EXEC msdb.dbo.sp_send_dbmail @recipients='Rits4Friends@gmail.com',@copy_recipients='ritesh_a_shah@yahoo.com',
@subject = @SUB,
@body = @BOD,
@body_format = 'HTML';
GO

Open in new window

0
 
JElsterAuthor Commented:
I getting the following error
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'msdb.dbo.xp_sendmail'. The stored procedure will still be created.

thanks
0
 
RiteshShahCommented:
Have you enable database mail? this is first step as I explained it in my third post (post # 24129576)
0
 
JElsterAuthor Commented:
Yes
0
 
JElsterAuthor Commented:
HI..
I got it working... one final question how do you add a carriage return in the text?
I'm not using HTML thanks


   SELECT @newAdd = address
   FROM inserted
        SET @bod = 'Old address :' + @oldAdd + ' has been updated
          with New address : ' + @newAdd
              set @sub='* CRM address has been updated *'
0
 
RiteshShahCommented:
you can use HTML. like

set @bod = 'Old address :' + @oldAdd + ' has been updated
 this is carriage return'
0
 
RiteshShahCommented:
sorry, my HTML tag is not showing in EE's page as it is rendering. you can use  lessthan br greaterthan tag of HTML.
0
 
JElsterAuthor Commented:
??
I'm using Sql Server 2000 - I don't believe it accepts HTML....
thanks
0
 
RiteshShahCommented:
may be \n works or char(13)
0
 
JElsterAuthor Commented:
I also noticed the OLD and NEW Address are the same?


   -- gets the previous address that was deleted
   SELECT @oldAdd = address,
     @Name = firstname+' '+lastname+'  '+repno
   FROM deleted
 
   -- gets the new address
   SELECT @newAdd = address
   FROM inserted
        SET @bod = 'Old address :' + @oldAdd + ' has been updated
          with New address : ' + @newAdd
              set @sub= @Name + '    * CRM address has been updated *'
0
 
RiteshShahCommented:
if you will not update address than obviously it is going to be same. otherwise, code is right.
0
 
JElsterAuthor Commented:
When I change the Address the OLDVAL and NEWVAL are the SAME.
Also I get the email when ever the record is Changed... I need to Send the email ONLY if the address has changed.
thanks
0
 
RiteshShahCommented:
you can make an IF confition like

if @oldAdd<>@newAdd
begin
email code.........
end

0
 
RiteshShahCommented:
check this script which will log entry if address is different.

use adventureworks
create table addressOfemp
(
name varchar(20),
address varchar(50),
city varchar(10)
)
go
insert into addressOfemp values('ritesh','a-206 bhavya appt','ahmedabad')
go
 
create table logs
(
oldval varchar(20),
newval varchar(20)
)
go
--trigger
-- Create Trigger with name 'CustomerUpdateMail'
CREATE TRIGGER CustomerUpdateMail
-- and the table is CustomerInfo
ON addressOfemp
-- 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 @CustomerName varchar(10)
   
   -- holds the body of the email
   declare @bod varchar(2000)
   declare @sub varchar(100)
  declare @oldAdd varchar(50)
  declare @newAdd varchar(50)
   
   -- gets the previous address that was deleted
   SELECT @oldAdd = address
   FROM deleted 
 
   -- gets the new address
   SELECT @newAdd = address
   FROM inserted
 
          SET @bod = 'old address=' + @oldAdd + ' has been updated
          with new address' + @CustomerName 
  set @sub='customer address is updated' 
 
--   EXEC msdb.dbo.sp_send_dbmail @recipients='Rits4Friends@gmail.com',@copy_recipients='ritesh_a_shah@yahoo.com',
--@subject = @SUB,
--@body = @BOD,
--@body_format = 'HTML';
 
if @oldAdd<>@newAdd
begin
insert into logs values(@oldadd,@newadd)
end
 
GO
 
--check now
update addressOfemp set address=address
select * from addressOfemp
 
update addressOfemp set address='tempadd'
select * from addressOfemp

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now