Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How have Trigger's example after insert send to mail on outlook?. Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 7:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 9, 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
Posted Wednesday, January 30, 2013 2:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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
Posted Friday, February 1, 2013 12:28 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 4,202, Visits: 3,639
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1414804
Posted Friday, February 1, 2013 1:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
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
Posted Friday, February 1, 2013 2:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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
Posted Friday, February 1, 2013 2:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
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.



- 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 #1414854
Posted Friday, February 1, 2013 7:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 9, 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
Posted Friday, February 1, 2013 9:53 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 4,202, Visits: 3,639
You aren't passing any of your parameters to the EXECUTE sp_send_dbmail statement.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1414904
Posted Saturday, February 2, 2013 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 9, 2013 12:24 AM
Points: 4, Visits: 6
Ed Wagner ,Edit code me please.
Thank you.
Post #1414945
Posted Monday, February 4, 2013 12:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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




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 #1415144
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse