January 11, 2010 at 7:39 am
Having a table as DETAILS with 7 columns which get automatically inserting or updating thru an XML file, I need a stored procedure to send an email and netsend message whenever the row gets inserted or the column gets updated
SOURCEIDNAMEDEPTIDLNAMEDESCRIPTIONSTATUSID PLACEID
3 jaypeNETDATASOLUTIONS1 BSK
If SOURCEID is 4 it should take as NGS
If SOURCEID is 3 it should take as SJP
If SOURCEID is 2 it should take as PJP
If SOURCEID is 1 it should take as NMD
NAME same as it is
DEPTID same as it is
LNAME same as it is
DESCRIPTION whatever it gets updated
PLACEID same as it is
I need a simple or HTML mail like
Subject: New LNAME received
NAME = SJP
LNAME = DATA
DEPTID = NET
DESCRIPTION = SOLUTIONS
OR
Subject: New LNAME received
New LNAME DATA received from SJP, the DESCRIPTION is SOLUTIONS and the DEPTID is NET.
I had done that with Trigger, but I need this in SP.
Please guide me on how to do this
January 11, 2010 at 9:41 am
You could do it with a trigger and a trigger and a xp_sendmail call.
January 21, 2010 at 2:55 am
A few days back i wrote the below trigger to send an email whenever any record gets inserted,update,deleted in a table.You can modify it as per your requirements, hope it will meet your requirements.....
Consider an example where i have a table named student which has two columns named student_id and student_name.I need to send notification email to a particular user whenever any student name is inserted,updated or deleted.Please try the below tested code,i believe it will meet your requirements.
create table student
(
student_id int,
student_name varchar(100),
)
create trigger trg_ins_upd_del on dbo.student
after insert,update,delete
as
begin
declare @student_name varchar(100)
declare @Newstudent_name varchar(100)
declare @MailSubject varchar(100)
declare @MailBody varchar(4000)
IF EXISTS(select INS.student_name from INSERTED INS inner join DELETED DEL on INS.student_name!=DEL.student_name
inner join student S on INS.student_name=S.student_name)
begin
select
@student_name=DEL.student_name
from DELETED DEL
inner join INSERTED INS on DEL.student_name!=INS.student_name
select @Newstudent_name=S.student_name
from student S
inner join INSERTED INS on S.student_name=INS.student_name
inner join DELETED DEL on S.student_name!=DEL.student_name
set @MailSubject=’Student Name Updated’
set @MailBody=’Hi,
Student named ‘+convert(varchar(100),@student_name)+’ has been updated.
Old Student Name:’+convert(varchar(100),@student_name)+’
New Student Name:’+convert(varchar(100),@Newstudent_name)+’
Regards,
XYZ
‘
print @MailBody
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile Name’,
@recipients =’Please enter the email address here’,
@body = @MailBody,
@subject = @MailSubject ;
end
else
/* Student name inserted */
IF EXISTS(select INS.student_name from INSERTED INS
inner join student S on INS.student_name=S.student_name)
begin
select @student_name = S.student_name from student S
inner join INSERTED INS on S.student_name = INS.student_name
set @MailSubject=’New Student Is Inserted’
set @MailBody=’Hi,
A new student named ‘+convert(varchar(100),@student_name)+’ has been inserted.
Thanks,
XYZ
‘
print @MailBody
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile Name’,
@recipients =’Please enter the email address here’,
@body = @MailBody,
@subject = @MailSubject ;
end
else
/* student is deleted */
IF EXISTS(select student_name from DELETED)
begin
select @student_name = student_name from DELETED
set @MailSubject = ‘Student Deleted’
set @MailBody = ‘Hi,
Student named ‘+convert(varchar(100),@student_name)+’has been deleted
Thanks,
XYZ
‘
print @MailBody
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile Name’,
@recipients =’Please enter the email address here’,
@body = @MailBody,
@subject = @MailSubject ;
end
end
January 25, 2010 at 7:22 am
Hey,
Just a word of caution with using a trigger to send the email, I have been burnt in the past with this method. I would suggest writing the information you need for the email into a separate table and then have a job running that will read off this table to send the email and then delete the rows that have had the email sent. I have had triggers lock up which then locks that row on the table and then the whole table and brings the whole database to a grinding halt.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply