Trigger to generate SQL Mail

[Guest_imported]

Hi,
I am Tejo here.
I am working in a project where I want to fire a trigger when a INSERTION is made in a table.
The trigger should collect all the E-Mail IDs (retrieved from another table) and send mails to them .
For this I think I have to make use of SQL Mail.
If u have any Stored Procedure for this type of logic then please send it to me as the requirement is very urgent.
Or can any body tell me any tips for making this Trigger easy to write .

Thanks in advance.
Tejo

 

[tlbroadbent]

You can use SQLMail to send the messages. However, don't send mail from a trigger. Triggers should execute quickly. Sending Mail is not quick.

I recommend creating a MailQueue table. All of the required information could be inserted into this table and a JOB could be scheuled to process the queue regularly and send the mail messages.

The exact code required depends on several factors.

1- Do all the EmailIDs receive the same message?
2- Should All EmailIDs receive a message each time the trigger fires? If not what is the selection criteria?
3- What information from the inserted record is required for the Email message?
4- How many messages will be sent for each insertion? Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[ADW]

Hi, there, tlbroadbent is right it is slow sending mail using SQL mail, but that depends if you have any attachments and how long ur message is. below is some code which uses SQLMail in a trigger. The results of a select are passed into a variable and sent that way. Hope this makes sense!!


CREATE TRIGGER [AGE] ON [Employees]
FOR INSERT, UPDATE
AS

declare @tempage datetime
declare @tempemp varchar(30)
declare @tempstring varchar(50)

if update
BEGIN (Birthdate)
select @tempage = (select * from Inserted)
begin
select @tempemp = (Select FirstName from Inserted)
select @tempstring = 'Mail notification of an update using atrigger - tony' + @tempemp
exec master.dbo.xp_sendmail
'tony.whitwell@decorpart.co.uk' ,@tempstring

end
end

 

[DuncFair]

The following creates a trigger on a table that monitors a single column (Account Status), when the column is updated and email is sent to the contact telling them the old and updated Account Status values, as well as the user who made the change to their record.

------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'e_mail_to_contact' AND type =
'TR')
DROP trigger e_mail_to_contact
go
CREATE TRIGGER e_mail_to_contact
ON table1

FOR INSERT, update
AS

declare @text varchar(255)
declare @contact_email varchar (50)

if update (Changed_Field)
if exists (select(convert(varchar(30),inserted.Changed_Field)) from Inserted where Inserted.Changed_Field <>'')

begin
SELECT @text = table2.column1 + ', Your Status has been changed from '+ isnull((select(convert(varchar(30),deleted.Changed_Field))),'empty')
+ ' to '+ isnull((select(convert(varchar(30),inserted.Changed_Field))),'empty')
+ ' by: '+table1.lastuser + '.'

from Inserted, table1, deleted, table2
where inserted.id_column=table1.id_column
and table1.id_column = deleted.id_column
and table1.id_column=table2.id_column
and table2.column2 = '0111'

select @contact_email = column3
from Inserted, table2, deleted
where inserted.id_column=table2.id_column and table2.id_column = deleted.id_column
and table2.contact = 'e-mail address' and table2.column2 = '0111'

exec master.dbo. xp_sendmail @recipients = @contact_email,
@subject = 'Account Status Change',
@message = @text
------------------------------------------------------------

Good Luck,
Dunc
dunc@onebox.com