Solved

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

Posted on 2009-04-13
19
807 Views
Last Modified: 2012-05-06
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
Comment
Question by:JElster
  • 12
  • 7
19 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24129496
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24129540
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24129576
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
 
LVL 1

Author Comment

by:JElster
ID: 24129599
How do I check if the data has changed?
I need to email the OLD and NEW values
thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24129625
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24129700
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
 
LVL 1

Author Comment

by:JElster
ID: 24129791
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24129804
Have you enable database mail? this is first step as I explained it in my third post (post # 24129576)
0
 
LVL 1

Author Comment

by:JElster
ID: 24129809
Yes
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:JElster
ID: 24130058
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24130069
you can use HTML. like

set @bod = 'Old address :' + @oldAdd + ' has been updated
 this is carriage return'
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24130079
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
 
LVL 1

Author Comment

by:JElster
ID: 24130080
??
I'm using Sql Server 2000 - I don't believe it accepts HTML....
thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24130099
may be \n works or char(13)
0
 
LVL 1

Author Comment

by:JElster
ID: 24130114
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24130311
if you will not update address than obviously it is going to be same. otherwise, code is right.
0
 
LVL 1

Author Comment

by:JElster
ID: 24130345
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24130363
you can make an IF confition like

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

0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 400 total points
ID: 24130395
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now