Skip to Main Content
  • Questions

  • Do something when the transaction ends

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jose.

Asked: February 05, 2018 - 11:02 am UTC

Last updated: February 05, 2018 - 11:29 am UTC

Version: 11g Release 11.2.0.3.0 OCI Version 10.2

Viewed 1000+ times

You Asked

I work with Delphi and Oracle, and what I'm trying to do is change the value of a field by informing a user by mail. This is being done wrongly with a trigger. I thought that in the compound trigger in the after statement the transaction was over (commit or rollback). Sometimes the user changes the value of the field, then cancels the changes and the email has been sent.
Is there any way to know the end of the transaction to know if the changed data has been saved or not to be able to send the mail or not?

Thanks a lot.
Jose.

and Chris said...

Triggers work at the statement level. So anything non-transactional you do there - such as send an email - will still have happened. Even if you rollback!

To make emails transactional, you need to submit a job so the database only does this on commit. Tom shows a way to do this at:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7267435205059

Note you can use UTL_mail instead of UTL_SMTP from 10g to send email from the database. This is easier to use:

https://oracle-base.com/articles/10g/utl_mail-send-email-from-the-oracle-database

Rating

  (1 rating)

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

Comments

Very good solution

Jose Martinez Lobato, February 05, 2018 - 12:37 pm UTC

I had thought about reviewing the changes that occurred throughout the day and sending emails, but this option you offer is much better. Thank you very much.
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.