Sending Single email from database in case of multiple updates [message #665941] |
Wed, 04 October 2017 01:56 |
|
oracbeg
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Hello Everyone,
Need some help/suggestion here. I have created a trigger which will update the table (audit table) when a record is updated (insert, update and delete) in parent table and will also send a email to recipient before that. It is working fine. Issue is that it is sending email for each record updated and is flooding the inbox as the updating activity is frequent.
I want that it should send single email instead of multiple emails every time when multiple records are updated in an instant with the information picked from audit table columns as it was doing for single row update. I have used utl_mail pacakage to execute.
Please help. Waiting for the response
|
|
|
Re: Sending Single email from database in case of multiple updates [message #665942 is a reply to message #665941] |
Wed, 04 October 2017 02:14 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
When you say "Waiting for the response" that implies that you feel entitled to assistance. Unless you want to pay for support, this is not the case.
I would de-couple the sending of an email from updating the rows by using Advanced Queuing. Another approach would be to schedule a job that queries your audit trail and sends a mail with whatever frequency you require.
Lastly, I wish you would not say "record" when you mean "row".
|
|
|
|
Re: Sending Single email from database in case of multiple updates [message #665953 is a reply to message #665944] |
Wed, 04 October 2017 08:37 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
I agree with John, it needs to be separated.
You also said "send a email to recipient before that" which really makes no sense. Why would you send an email before you did something? If you never get to the update because say a tablespace blew up, you just informed people that an update was done, when it wasn't.
If Advanced Queuing scares you, a simple procedure that does the updates and then sends an email at the end would be another option.
|
|
|