long delay when waiting for trigger????
-
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
-
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 -
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply