March 25, 2009 at 11:41 am
Hey folks,
I need some expert advice. I have two tables, one 'Order' table and one 'OrderDetails' table. The 'Order' table store information about the order (Customer name, Phone number etc.). The 'OrderDetails' table store information about the order itself (part, quantity etc.). There can be many OrderDetails to an Order.
I have an AFTER INSERT trigger on the 'Order' table that fires an email. The body of the email in the trigger includes information from a row in the 'Order' table and any associated rows from the 'OrderDetails' table.
Every email I get shows all information from the 'Order' table but the Order Details are blank. This happens because the trigger fires before the 'OrderDetails' table is filled.
Any advise?
Thanks,
Allan
March 25, 2009 at 11:48 am
To avoid this you may add a "IsComplete" and a "IsSend" column to your Order table and use an UPDATE trigger. Then:
* Insert the order
* Insert the order details
* Update the order and set the "IsComplete" to 1
* In your trigger check if already send by "IsSend". If not send the mail and set the "IsSend" to 1
Tip:
Maybe consider to create a "ToSend" entry into any other table and use a job to send the mail. Sending the mail from a trigger affects the performance seriously because the INSERT/UPDATE statement waits until the mail was send. This is only a consideration without any knowledge about your business. 😉
Greets
Flo
March 25, 2009 at 12:04 pm
Florian i recently found out that the procedure sp_send_dbmail is asychronous, so it does not wait for the preocdure to execute before returning control tot eh calling procedure;
in 2000, i always made a trigger add to an emailOUT table, and a job would whip through that table to do the email sending, just so my triggers would not suffer, exactly as you described.
I think it was GSquared or Lynn that "schooled" me on that, so the procedure sp_send_dbmail adds to it's own internal Mail queue.
Goes against my previous training, which was "mail in trigger" = "bad", but the tool is there for us to use now.
i learn something here every day.
Lowell
March 25, 2009 at 12:08 pm
Lowell (3/25/2009)
Florian i recently found out that the procedure sp_send_dbmail is asychronous, so it does not wait for the preocdure to execute before returning control tot eh calling procedure;in 2000, i always made a trigger add to an emailOUT table, and a job would whip through that table to do the email sending, just so my triggers would not suffer, exactly as you described.
I think it was GSquared or Lynn that "schooled" me on that, so the procedure sp_send_dbmail adds to it's own internal Mail queue.
Goes against my previous training, which was "mail in trigger" = "bad", but the tool is there for us to use now.
i learn something here every day.
Hello Lowell
Thanks for this information! So you became my teacher :-).
I can affirm that there is always something new here every day!
Have a nice day
Flo
March 25, 2009 at 12:20 pm
Awesome idea. Thanks for your help guys!
One question though. Lowell, how is your 'job' set up to run through the emailOUT table? Is it a stored procedure running in the background every minute or so??
March 25, 2009 at 1:19 pm
yes exactly just a scheduled job for a stored proc that ran every minute....it looped thru every row in the table where a flag SentSuccessfully = 0; it was nothing more than an SMTP function in the DLL.
mine used a dll, so it was synchronous; if it returned an error, the error was logged,and the flag was not updated, if it worked, the flag was set to 1 to prevent it from being sent again.
one advantage was i had a copy of every email, so you could see who was last contacted when, run statistics against it to see email campaign success rates, etc.
I still prefer to do it that way, but it's good to know you can use the newer built in stored proc, which uses a service broker to send the emails.
Lowell
March 25, 2009 at 1:39 pm
Lowell, do you know how to recognize if there is any problem while sending the mail?
Since now I also use the job-based solution. I just had a look to BOL for sp_send_dbmail but cannot find any information about send errors, only errors for permissions and wrong parameters.
Greets
Flo
March 25, 2009 at 5:56 pm
Florian, i used a smtp dll...not the built in. nowadays program that runs the job handleds it , but i've done it all in SQL as well.
so i might get any error from the network, say can't resolve address/no dns, no connection, 550 so such mailbox, 552 mailbox full, etc.
even stuff from my own server, like relaying not permitted, auth required,etc. only if i got 250 Requested mail action okay, completed did i update the sent successful flag, and if it failed, it updated the number of tries field...3 tries and i'd give up no matter the error.
Lowell
March 28, 2009 at 1:03 am
[font="Verdana"]
amadriaga (3/25/2009)
Hey folks,I need some expert advice. I have two tables, one 'Order' table and one 'OrderDetails' table. The 'Order' table store information about the order (Customer name, Phone number etc.). The 'OrderDetails' table store information about the order itself (part, quantity etc.). There can be many OrderDetails to an Order.
I have an AFTER INSERT trigger on the 'Order' table that fires an email. The body of the email in the trigger includes information from a row in the 'Order' table and any associated rows from the 'OrderDetails' table.
Every email I get shows all information from the 'Order' table but the Order Details are blank. This happens because the trigger fires before the 'OrderDetails' table is filled.
Any advise?
Thanks,
Allan
If you could have AFTER Trigger on OrderDetails table, you could have get the all information from both the tables.
Mahesh[/font]
MH-09-AM-8694
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply