How have Trigger's example after insert send to mail on outlook?.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » How have Trigger's example after insert send...

17 posts, Page 1 of 2

1 2 »»

How have Trigger's example after insert send to mail on outlook?.

Rate Topic

Display Mode

Topic Options

Author

Message

seiyak2009

Posted Tuesday, January 29, 2013 7:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 09, 2013 12:24 AM
Points: 4, Visits: 6

I create trigger after insert into table i want send mail on outlook and example trigger please.

thank you.

Post #1413416

anthony.green

Posted Wednesday, January 30, 2013 2:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

CREATE TRIGGER ... ON ...
AFTER INSERT
AS
EXEC MSDB.dbo.sp_send_dbmail ...., ...., ...., ....

Lookup database mail, configure it, and then put the right parameters into the sp_send_dbmail call that you need.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1413522

Ed Wagner

Posted Friday, February 01, 2013 12:28 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 2,004, Visits: 720

An point worth considering here is that sp_send_dbmail is a system stored procedure provided by MS with the product. Customizing it may result in your functionality "just going away" if you upgrade to a newer version or patch it.

Another possibility is to wrap the sp_send_dbmail proceudre and then call your procedure instead. Then have your wrapper do whatever it is that you need it to do. Example:

CREATE PROCDURE my_send_dbmail(parameters_supported_by_db_mail)
AS

BEGIN
EXECUTE sp_send_dbmail passed_parameters;
--do your stuff here
END;

Granted, you'd ideally support all the parameters that sp_send_dbmail supports and should pass them to sp_send_dbmail by name, but you'd lower your risk if you upgrade. Just something to consider.

Post #1414804

Evil Kraig F

Posted Friday, February 01, 2013 1:37 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 5,722, Visits: 6,194

I want to throw in a warning here. Sending emails via triggers means that if the exchange server goes down... so does your database. Also any transaction has to wait for the sendmail to complete, thus slowing your system.

This is a good example of when to use Service Broker or another messaging system. I recommend you look into Service Broker, and then switch your trigger into adding a message to a local queue for asynchronous emailing of the information.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1414842

Lowell

Posted Friday, February 01, 2013 2:11 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789, Visits: 28,063

Craig I think that was true with SQL2000, and the old SQL Mail, but the 2005 and above database mail via msdb.dbo.sp_send_dbmail is already an asynchronous service broker item, so it's safe for use in a trigger, as long as the profile and account have been set up.

I took the OP to mean "mail" or "SMTP Mail" when he said "Outlook", instead of Outlook Profile that was the setup requirement for the old SQL 2000 stuff

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1414849

Evil Kraig F

Posted Friday, February 01, 2013 2:45 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 5,722, Visits: 6,194

Lowell (2/1/2013)

Didn't know sendmail was built asynchronous. I've had exchange servers down fail ssis packages using the equivalent command so I assumed it wasn't.

I'll have to look into that further, thanks for the information Lowell.

Post #1414854

seiyak2009

Posted Friday, February 01, 2013 7:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 09, 2013 12:24 AM
Points: 4, Visits: 6

Thank you
//------------------------
This is example,But can't send mail.
Please check code syntax me.

CREATE TRIGGER SendMail ON OIL_Price

AFTER INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @profile_name varchar(250)

declare @recipients varchar(500)

declare @body varchar(5000)

declare @subject varchar(500)

declare @price decimal(18,2)

-- Insert statements for trigger here

set @price =(SELECT top 1 OIL_PRICE FROM OIL_Price order by OIL_DATE desc)

EXEC msdb.dbo.sp_send_dbmail

set @profile_name = 'Oil Price MPV'

set @recipients = 'prasitk@mitrphol.com'

set @body = 'Just testing the mail by trigger SQL server, price oil today is ='+@price

set @subject = 'Sending Mail using Database Mail'

END
//----------------------
Thank you.
Sitt of Thailand.

Post #1414897

Ed Wagner

Posted Friday, February 01, 2013 9:53 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 2,004, Visits: 720

You aren't passing any of your parameters to the EXECUTE sp_send_dbmail statement.

Post #1414904

seiyak2009

Posted Saturday, February 02, 2013 8:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 09, 2013 12:24 AM
Points: 4, Visits: 6

Ed Wagner ,Edit code me please.
Thank you.

Post #1414945

anthony.green

Posted Monday, February 04, 2013 12:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

Remove the sets from your sp_send_dbmail call, also read the documentation on the procedure, it will tell you how to make a successful mail call.

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Post #1415144

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions