Forum Stats

  • 3,837,629 Users
  • 2,262,276 Discussions
  • 7,900,334 Comments

Discussions

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.

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
  • 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.
  • 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
  • 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
This discussion has been closed.