Home » SQL & PL/SQL » SQL & PL/SQL » Sending Single email from database in case of multiple updates (Oracle 11gR2)
Sending Single email from database in case of multiple updates [message #665941] Wed, 04 October 2017 01:56 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #665944 is a reply to message #665942] Wed, 04 October 2017 02:33 Go to previous messageGo to next message
oracbeg
Messages: 16
Registered: July 2017
Junior Member
I am sorry for using that phrase. I really didn't meant that for assistance. Thank you for your help. I will try to work on the approach you suggested Smile
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 Go to previous message
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.

Previous Topic: Help in SQL query
Next Topic: Tablespaces for Index Partitions and Subpartitions
Goto Forum:
  


Current Time: Thu Mar 28 03:12:08 CDT 2024