Forum Stats

  • 3,851,702 Users
  • 2,264,013 Discussions
  • 7,904,820 Comments

Discussions

Categories

Can trigger processing be separated from insert?

441298
441298 Member Posts: 3
edited Jul 30, 2009 8:14PM in SQL & PL/SQL
When inserting a record via jdbc, will making the insert trigger for the table 'AFTER INSERT' return control back to external app before the trigger is processed? I have a trigger that's sending mail and am trying find some way to decouple the insert from the smtp send.
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Himanshu Kandpal
    Himanshu Kandpal Member Posts: 1,971 Silver Badge
    You can have a field in the table which will say from where the table is getting updated and in the trigger you can have a check which if the field says that the update is comming from jdbc dont execute the trigger code.



    thanks
    · Share on TwitterShare on Facebook
  • 441298
    441298 Member Posts: 3
    I do need the trigger code to be executed. I am thinking something like a deferred execution so that the external app does not have to wait for the smtp call to complete.
    · Share on TwitterShare on Facebook
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    Transactions in Oracle are atomic. The trigger, unless it was created as an anonymous transaction can not, and does not, commit.

    I assume that what you are trying to do is make sure an email does not get sent unless, and until, the changes are commited.

    Can you confirm this and also provide a full version number?

    My personal preference, as I am not a fan of triggers, would be to move the sending of the email out of the trigger. It really
    doesn't belong there for just the reason you have brought up.
    damorgan
    · Share on TwitterShare on Facebook
  • 441298
    441298 Member Posts: 3
    I'm diverting system emails from the external app to the database and based on their frequency deciding whether to send or ignore. As you suggested, I'm now moving toward a scheduled procedure to send emails independent of the trigger.

    The version is 10.2.0.3.0
    · Share on TwitterShare on Facebook
This discussion has been closed.