Skip to Main Content
  • Questions

  • Calling a stored procedure to send a mail from after insert trigger

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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