Skip to Main Content
  • Questions
  • Calling a stored procedure to send a mail from after insert trigger

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vijayakumar .

Asked: October 01, 2018 - 6:00 am UTC

Last updated: October 03, 2018 - 4:25 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

I have a requirement to send mail from an after insert trigger and I am passing the :NEW.MYID to the procedure.
The procedure contains a query with aggregate functions and storing to an 'INTO mynvarchar2var' as a generated string to be send as email body. i.e. "This mail is send to AgCode|email@server.com. Your collection information is generated for 01/10/2018 and your total credit available is Rs.445/- from 50 transactions."
My problem is trigger always throw exception
ORA-04091: table <tablename> is mutating, trigger/function may not see it
ORA-06512: at <triggername>, line 7
ORA-04088: error during execution of trigger '<triggername>'

How could I rectify this issue when no insert or update is made from trigger or stored procedure?

and Connor said...

See here

https://asktom.oracle.com/pls/apex/asktom.search?tag=emailing-after-inserting-into-database-tables

It will

- fix your mutating issue
- fix the problem of emailing even if the transaction rolls back
- fix the problem of multiple emails being sent (because a trigger can fire MORE than once for the same single row change)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library