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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 send email when i insert a new record

Author  Topic 

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-01-04 : 14:38:28
hi there

i have sql server 2008 r2

and i have 2 tables products and customers

table products
idproduct name idcustomer(fk)
3 tshirt 156

table customer
idcustomer(pk) name email
156 john doe johndoe@gmail.com

ok, 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= 3
i have no idea how to do that

i really need your help

thanks 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 table
2) Use the sp_send_dbmail procedure to send the results

This 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
Go to Top of Page

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 newbie

thanks a lot
Go to Top of Page

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
AS
set nocount on

DECLARE
@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 inserted
inner join
Customer
on i.CustomerID = c.customerID

exec 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
Go to Top of Page

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 ...
Go to Top of Page

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-01-05 : 08:37:10
HI KRISTEN and Bustaz Kool , thanks for your replies
i get the idea,
but kristen could you help me a little bit more

hoe could i do a loop to send the emails with status "new"
sorry but i have no idea, any example please

thanks fro your help
Go to Top of Page

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 like

UPDATE U
SET Status = 'SENDING',
@MyID = ID
FROM MyEmailTable AS U
WHERE ID = (SELECT TOP 1 ID FROM MyEmailTable WHERE Status = 'NEW')
AND Status = 'NEW' -- Belt & Braces!!

-- Get data to output to the actual Email
SELECT Col1, Col2, ...
FROM MyEmailTable
WHERE ID = @MyID
Go to Top of Page
   

- Advertisement -