January 29, 2013 at 7:37 pm
I create trigger after insert into table i want send mail on outlook and example trigger please.
thank you.
January 30, 2013 at 2:23 am
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.
February 1, 2013 at 12:28 pm
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.
February 1, 2013 at 1:37 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2013 at 2:11 pm
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
February 1, 2013 at 2:45 pm
Lowell (2/1/2013)
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
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2013 at 7:57 pm
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.
February 1, 2013 at 9:53 pm
You aren't passing any of your parameters to the EXECUTE sp_send_dbmail statement.
February 2, 2013 at 8:46 am
Ed Wagner ,Edit code me please.
Thank you.
February 4, 2013 at 12:58 am
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.
February 4, 2013 at 1:06 am
I hope this helps.
http://www.sql-server-performance.com/2010/dml-triggers-multiple-triggers/
With Thanks,
Satnam
February 4, 2013 at 1:25 am
Hey seiyak2009, what happens when the business just decides to record the oil price every 5 seconds due to a new fast trading platform introduced at the company? Your trigger is now sending thousands of emails crashing both SQL and the Exchange server.
Don't send emails from triggers. Use a procedure which can be configured for a set time interval to check the oil price table, then generate an email. (I'd also hesitate sending any non critical alert emails from SQL itself. Business functionality should not be built in the data layer.)
February 4, 2013 at 2:59 am
Thank you all topic, If example trigger send mail ?.
Please, I want send mail if insert table data auto with trigger.
I educate on web applier develop but can't send mail.
Please help me.
Thank you.
February 4, 2013 at 6:41 am
Anthony is right - please look at the MSDN page for how to execute the sp_send_dbmail procedure. He gave you a link to the page. This is one of those things were it's better to teach someone how to fish instead of just giving them a fish. The reasons are:
1. MSDN is a great resource for SQL Server syntax and what what accepts what parameters. If you don't use it already, you should start getting used to it with something simple. Then when you have to use it for something more difficult, you'll be able to.
2. It appears that if we just post the code using your parameters, you'll just copy and paste it without understanding how it works, which is never advisable in a production environment. You are going to have to support the code, so you should always understand how it works.
You're going to want to pass your parameters to the procedure when you execute it. Sorry if I'm being a pain here. You don't have to read the whole MSDN page, but it would help you understand how it works.
June 3, 2013 at 5:54 pm
would this code runs on mysql successfully, please help me.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply