Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Sending Single email from database in case of multiple updates
oracbeg
post Oct 4 2017, 02:00 AM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 11-July 17
From: india
Member No.: 52,070





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
Go to the top of the page
 
+Quote Post
burleson
post Oct 4 2017, 05:47 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi,

The trick would be to know the number of rows that will result from a single update/delete, right?

That may not be easy to do!

One option might be to perform the DML row-by-row, using a cursor loop with a counter that counts each iteration.

Did I understand your issue?


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
oracbeg
post Oct 4 2017, 11:41 PM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 11-July 17
From: india
Member No.: 52,070



QUOTE (burleson @ Oct 4 2017, 05:47 AM) *
Hi,

The trick would be to know the number of rows that will result from a single update/delete, right?

That may not be easy to do!

One option might be to perform the DML row-by-row, using a cursor loop with a counter that counts each iteration.

Did I understand your issue?



Hi Donald,

Thanks for your response. As you stated...if i use a cursor to get count of the dml operation say delete for 10 rows. How will it be used in trigger to send a single mail using utl_mail.send....Now as i used trigger which will update the audit table and execute utl_mail to send a notification to xyz@abc.com. What current situation is that it is sending 10 mails for 10 rows deleted. I want that it should send 1 mail for 10 rows deleted at a time. Again if i delete 2 rows together it should single mail for that and so on.....Can we do that?


Thanks a ton for your time.

Go to the top of the page
 
+Quote Post
burleson
post Oct 5 2017, 06:40 AM
Post #4


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi,

Check out the sql%rowcount function:

http://www.dba-oracle.com/t_adv_plsql_mult...icit_cursor.htm

This should make it easy to achieve your goal!

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Oct 5 2017, 01:01 PM
Post #5


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



Hello buddy,

I guess the trigger which you've created is ROW LEVEL, but you need to change it to STATEMENT LEVEL for your requirement.

ROW LEVEL trigger - If a single UPDATE statement updates 10 rows, the trigger is executed 10 times.

STATEMENT LEVEL trigger - If a single UPDATE statement updates 10 rows, the trigger is executed only once. This is because this type of trigger doesnt mind the number of rows updated but only the number of statements executed. In this case only one statement, thus only fires once.

Kindly make the necessary change and let us know for any more clarification.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 03:48 PM