Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jaycee
Starting Member
18 Posts |
 Posted - 2012-11-30 : 03:52:51
|
| Hi, If I were to set up a website with on-line purchasing, when someone makes a purchase, is a reply e-mail best to be sent from sql server (trigger and db mail) or is it best to come from the interface. For example when buying off Amazon / ebay, you get replies as the document chain goes on - would these be sql mail based or written through the interface? Thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-30 : 03:58:09
|
| >> trigger and db mailDefinitely not a send mail in a trigger.Add a row to a table from the trigger - it shoiuld be a stored procedure and that should add the send mail row rather than a trigger.Another process looks at that table and sends any outstanding mails. This could be a sql server ob which polls the table or an external process. The good thing about this is that you can change the mail process easily and it shouldn't affect anything so the decision isn't so important - could just depend on the expertise available or what is in place already. Also gives a record of the emails sent and can resend if needed. Also dev and testing becomes easier because the mail just needs that table not the rest of the system.I used to send mails from a dedicated server but the implementation is a lot better now so that is not so important - but if you find it an issue that is quite easy to implement as long as you have the mail table as above..==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jaycee
Starting Member
18 Posts |
Posted - 2012-11-30 : 04:31:18
|
| Thanks for your thoughts on that.The reason at this moment I am thinking trigger is that software has been written by an external company who drop the records into our database through Sage web services and so we cannot write the stored procedure. In future developments, we are going to bring the process in-house and then yes, your idea is definitely viable - I had not thought of including a send mail within a stored procedure but with all the details ready in the stored proc, that would definitely be the efficient way. I was more fishing to get the idea if the likes of Amazon/ebay use sql mail for their replies. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-30 : 04:40:04
|
| In that case the trigger just inserts the details into a table and the asynchronous process sends the mail.If the trigger sends the mail it would block the inserts until it was sent - and the insert would fail if the send mail statement failed. You definitely don't want to be doing that if a third party is doing the inserts as you would be relying heavily on their error handling.If they are doing inserts then I would consider a timestamp or identity on the table so that it can be polled rather than a trigger as it's less risky.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jaycee
Starting Member
18 Posts |
Posted - 2012-12-04 : 13:47:12
|
| Interesting info that I didn't realise - Many thanks for your help. |
|
|
|
|
|
|
SQL Mail or not?