Home » SQL & PL/SQL » SQL & PL/SQL » trigger (oracle 9i)
trigger [message #291240] Thu, 03 January 2008 09:00 Go to next message
kuldip
Messages: 10
Registered: November 2007
Junior Member
hi all,
i know what a trigger is and its use. i was asked a question recently about trigger that

QUES> IF an event occurs then i want to send a email. so how shall i send the email ?

(i have some idea about it that i will have to write a procedure for sending a mail and call that procedure in the trigger body. WILL IT WORK ? IF yes how ? give me in details and if not then let me know the solutions)

thanks
Re: trigger [message #291249 is a reply to message #291240] Thu, 03 January 2008 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

(i have some idea about it that i will have to write a procedure for sending a mail and call that procedure in the trigger body. WILL IT WORK ?

Yes
Quote:

IF yes how ?

As you said.

But which event?

Regards
Michel
Re: trigger [message #291266 is a reply to message #291249] Thu, 03 January 2008 10:03 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You can create an email procedure and call it from a trigger. However, be careful of this and realize that your db transaction will not complete until the trigger has completely executed, including any delays in sending the email.

Ron
Re: trigger [message #291271 is a reply to message #291266] Thu, 03 January 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the event.
Some are not driven by transaction processing.

Regards
Michel
Re: trigger [message #291535 is a reply to message #291240] Fri, 04 January 2008 14:14 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are some question on of our minds:

1) what is the transactional behaviour your want from your triggers. More specifically, if the email send should fail, what should happen to your transaction? If the transaction should fail, how do you recall a sent email?

2) is it necessary that the email transmition be immediate, or can there be some delay (a few seconds? a few minutes? a few hours?). What would it mean it the email was delayed 20 minutes between the time your trigger finished and the time the mail went out? Since email is itself not an instantaneous operation (mail servers may be down or delayed from time to time), there is no guarantee that any email will reach its recipient(s) in a "fast" timeframe. You may in fact have situations where email arrive 20 mintues after the event no matter what you code, so you should have an answer for this question already.

Not putting words into their mouths, but I think Michel and Ron were hinting at these issues in their posts.

The reason these questions are important is that they will lead you in a the direction of your solution. Said a different way, you have options for how to do this.

You could do it in your trigger as one event.
You could write a record to a table and then let some periodic process do the emails.
You could let advanced queuing to take care of the details.
I have done it all three ways.


It all depends upon how you want to treat the transaction event. Can you please provide us with a more indepth description of how you expect the transaction to behave? Indeed, after doing so you may figure out for yourself what course of action sounds best for your need.

Kevin

Previous Topic: Accumulated Total of Sal according to hierarchy (connect by, analytic funcion)
Next Topic: Seperate-Comma (merged)
Goto Forum:
  


Current Time: Thu Dec 08 08:39:42 CST 2016

Total time taken to generate the page: 0.10345 seconds