long delay when waiting for trigger????

Question

long delay when waiting for trigger????

Jeff-132889

Ten Centuries

Points: 1112
More actions
September 14, 2004 at 9:34 am

#60216

I am currently using the below trigger to send email notification on insert/update. For the transaction to complete it has to send the email. This is actually a secondary table so the trigger (email failure) doesn't effect the insert/update of the real table. I don't care if this trasaction ever really completes. Is there a way to do this so that I don't have a wait until the trigger is done?
CREATE TRIGGER trACE_List_update
ON dbo._TmptblStaff
FOR INSERT, UPDATE
AS
IF UPDATE (listserveace)
BEGIN
   EXEC master..xp_sendmail
@recipients = 'helpdeskrequests',
@query = 'IF (SELECT listserveace from MAWDATA.dbo._TmptblStaff) = 0
BEGIN
   SELECT ''Remove from list serve ACE: ''+ Email AS '' ''
   FROM MAWDATA.dbo._TmptblStaff
   WHERE listserveace = 0
END
ELSE
IF (SELECT listserveace from MAWDATA.dbo._TmptblStaff) = 1
BEGIN
   SELECT ''Add to list serve ACE: ''+ EMail AS '' ''
   FROM MAWDATA.dbo._TmptblStaff
   WHERE listserveace = 1
END',
@subject = 'ACE listserve update',
@message = 'ACE listserve update:',
@attach_results = 'FALSE',
@width = 80
EXEC master..xp_stopmail
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Brandon Hunt Old Hand Points: 303 More actions · Answer 1

The only thing I can think of is to not use the SQL Mail xp_ calls. That's where you're getting hung up. You could redo the email functionality a couple of other ways:

1) Write email text into a table that an external program would query occasionally and it would handle sending.

2) Write text files out to an SMTP queue directory and use something like IIS's SMTP server to send them. That way, you don't have to wait on the email server to respond, etc. You just write a file to disk and go... Who cares when it gets sent?

I personally would go with option 2. Check out this article for more info: http://www.evolt.org/article/Using_files_to_send_emails_with_IIS_part_1_of_2/17/3419/

Note: the article is written in ASP / VBScript, but the logic would be easy to port into a stored procedure.

HTH,

-Brandon

AKM SSCommitted Points: 1538 More actions · Answer 2

I would suggest this: Create a table that can hold all the details of the emails you wish to send. To send an email at a time-sensitive time (ie during a transaction), insert a row into this table with all the details of the mail you wish to send. Then set up a SQL Server job that runs say every 5 minutes, that calls a stored procedure which reads rows from this table and actually sends the email (deleting the row once it is done).

This arangement decouples the (slow) actual sending of the email from the place that needs to run fast (the trigger), but still gets the emails sent out soon after the event you want to notify about.