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 |
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-01-04 : 14:38:28
|
hi therei have sql server 2008 r2and i have 2 tables products and customerstable productsidproduct name idcustomer(fk)3 tshirt 156table customeridcustomer(pk) name email156 john doe johndoe@gmail.comok, and thats what i need,: when i insert i new record in my table "products " and column "idcustomer=156" .,, i need to send an email to "john doe" wich is the customer with idcustomer=156 in my othe table "customers", with the following items:subject: new buy idproduct 3 message " you have bought a new item idproduct= 3i have no idea how to do thati really need your helpthanks in advanced |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-01-04 : 16:19:44
|
1) Create an INSERT trigger on the table2) Use the sp_send_dbmail procedure to send the resultsThis is a gross simplification but the genius, as always, is in the details. Look up CREATE TRIGGER and the stored procedure in BOL.=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-01-04 : 18:04:53
|
could u give me more details or an exmaple, im a real newbiethanks a lot |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-01-04 : 18:40:08
|
You'll need to have email set up on your SQL Server in order to get the Profile name but...[CODE]CREATE TRIGGER [dbo].[tr_Ins_MyTable]ON [dbo].[MyTable]AFTER UPDATE ASset nocount onDECLARE @CustomerName varchar(50), @CustEmail varchar(255), @body varchar(max)SELECT @CustomerName = c.CustomerName, @CustEmail = c.CustEmail, @body = 'Blah, blah, blah' + i.ProductName + 'mode blah''s' + cast(i.Quantity as vahchar(10))FROM INSERTED i -- Assumes one row inserted - You'll need to change this if multiple rows can be insertedinner join Customer on i.CustomerID = c.customerIDexec msdb.dbo.sp_send_dbmail @profile_name = '<<Profile Name>>', -- Get this value from Server configuration @recipients = @CustEmail, @subject = 'Blah, blah', @body = @body[/CODE]=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 03:07:58
|
Personally I wouldn't send the email in that way (although you could, and its more simple to do as Bustaz Kool describes)We create the subject/text and from/to etc. of the Email and store them in an "Emails" table (using a trigger or whatever method you like). We have a [Status] column in the EMails table, and set that to "New".Then we have a separate process that "sends" the emails. That loops round processing each "New" email, and sets the status to "Sending", then it sends the email, if there is any error it changes the status to "SendError" otherwise to "Sent".This allows us to see exactly what emails were sent, customers can "read" their emails online (which can be handy if their email failed to deliver because of spam filter or somesuch) and so on.Then ANY process that wants to send Emails just has to insert a row in the EMails table.Other benefits:Can resend an email (by changing its status back to NEW - or having an additional status of RESEND)Can change the method of sending - e.g. if volume becomes high. We don't, for example, send emails from SQL Box (our security prefers not to have EMail capability enabled on the SQL box itself), so the emails are actually sent from a different server. We have a special 3rd party emailing tool which has higher throughput than straight SMTP (I think it can send 100 concurrently or somesuch).We often want to send emails from the Application - i.e. the application itself is in the best position to format the content of the email, including all the items a customer bought or whatever, so the Application can then just use some SQL (in our case a Stored Procedure) to "Save" the text of the email to the EMails table.We have a dummy image in the emails, if the Customer retrieves that image then we mark the original EMail record as "Received", similarly if they click-through any link in the email (the link has some sort of ?ID=1324 parameter that relates back to the original EMail record's ID) we can log that as well.... and so on ... |
|
|
sebastian11c
Posting Yak Master
129 Posts |
Posted - 2012-01-05 : 08:37:10
|
HI KRISTEN and Bustaz Kool , thanks for your repliesi get the idea, but kristen could you help me a little bit morehoe could i do a loop to send the emails with status "new"sorry but i have no idea, any example pleasethanks fro your help |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-05 : 11:15:29
|
"how could i do a loop to send the emails with status "new""You'll have to write an application to do that - which is a fair bit more work, and why I hinted that Bustaz Kool's solution might eb an easier place to start.We do something likeUPDATE USET Status = 'SENDING', @MyID = IDFROM MyEmailTable AS UWHERE ID = (SELECT TOP 1 ID FROM MyEmailTable WHERE Status = 'NEW') AND Status = 'NEW' -- Belt & Braces!!-- Get data to output to the actual EmailSELECT Col1, Col2, ...FROM MyEmailTableWHERE ID = @MyID |
|
|
|
|
|
|
|