1 2 Previous Next 17 Replies Latest reply: May 17, 2013 2:42 AM by Hoek RSS

    Use of commit inside a trigger or an exception.

    BS2012
      Hi Everyone,
      To avoid table mutation error we can use pragma_autonomous transaction and use commit over there in side a mutiple rows update trigger. Is there anything else, where commit is required inside the trigger? For exception, why should we commit, if a program runs then it's commiting the transaction as well, then when and why we use commit inside an exception? Please correct me if I'm wrong.

      Regards,
      BS2012.
        • 1. Re: Use of commit inside a trigger or an exception.
          Hemant K Chitale
          Commit should never be required in a trigger.

          Exceptions should RAISE errors and let the calling session decide whether it should rollback or commit (generally rollback).


          Hemant K Chitale
          • 2. Re: Use of commit inside a trigger or an exception.
            Karthick_Arp
            A trigger is a part of a transaction. Using autonomous transaction in trigger isolates the trigger code into a independent transaction. This is always BAD. I have never come across such a situation where i would need to commit in a trigger.

            NEVER use code in a trigger that act as a Independent event. Code like AUTONOMOUS_TRANSACTION or code to sent EMAIL, all need to be avoided inside trigger.

            When ever mutating table error is hit you must be thinking why you have reached the error and not how to fix the error. Most (99.99%) of the time someone hit this error as a result of bad understandign of how oracle works.
            • 3. Re: Use of commit inside a trigger or an exception.
              BS2012
              Hi Hemant,
              Thanks for the reply. But I've a small query here.
              Hemant K Chitale wrote:
              Commit should never be required in a trigger.
              In case of updating multiple rows, a row level trigger has to fire for each row. Now assume, a trigger has fired for 1st row already, the when it has to fire for the next row, what kind of view is ready for that trigger? Is it pre-updated or post updated? So it raise the potential error ORA-04091 or table mutation error. To avoid this we use pragma_autonomus transaction. In this case how we will handle, if we won't use the commit inside the trigger? Please correct me if I'm wrong.

              Regards,
              BS2012.
              • 4. Re: Use of commit inside a trigger or an exception.
                John Stegeman
                BS2012,

                You need to explain your use case to us - forget about the trigger for a moment - what are you trying to do?

                999,999,999,999,999 times out of 1,000,000,000,000,000 when you are trying to use commit in a trigger, you are doing something wrong and the other 1 time out of 1,000,000,000,000,000, you're also wrong.
                • 5. Re: Use of commit inside a trigger or an exception.
                  BS2012
                  Hi karthik,
                  Please see my last reply to Hemant and please correct me if I'm wrong.

                  Regards,
                  BS2012.
                  • 6. Re: Use of commit inside a trigger or an exception.
                    John Stegeman
                    please correct me if I'm wrong.
                    I didn't understand your point, but if you are trying to look at other rows in the same table inside of a trigger, yes, you're doing it wrong.

                    Explain your use case if you'd like some specific advice.
                    • 7. Re: Use of commit inside a trigger or an exception.
                      BS2012
                      Hi John,
                      I asked this question to my lead and he explained like this. That time I found, he was correct. I was wondering, yes how the trigger will see the updated row, next time? Even I found a program in the web in which the pragma autonomous_transaction has been used inside a trigger. So I'm just clearing my doubts. Please go through this link. I'm sorry to upset you all. I was wondering that using of autonomous_transaction in side a trigger is very good.

                      http://www.java2s.com/Tutorial/Oracle/0560__Trigger/Autonomoustriggers.htm

                      Thanks a lot for clearing my doubts. But how we can handle that potential error if occur inside a trigger?
                      I mean how Oracle behaves when a trigger has to fire for updating multiple rows?

                      Regards,
                      BS2012.

                      Edited by: BS2012 on May 17, 2013 12:26 PM
                      • 8. Re: Use of commit inside a trigger or an exception.
                        Karthick_Arp
                        BS2012 wrote:
                        Hi karthik,
                        Please see my last reply to Hemant and please correct me if I'm wrong.

                        Regards,
                        BS2012.
                        My first post already had the answer for that
                        When ever mutating table error is hit you must be thinking why you have reached the error and not how to fix the error. Most (99.99%) of the time someone hit this error as a result of bad understandign of how oracle works.
                        Come up with a business requirement that will end up with a mutating table. We can go from there.
                        • 9. Re: Use of commit inside a trigger or an exception.
                          John Stegeman
                          But how we can handle that potential error if occur inside a trigger?
                          Give us an example.

                          Like I said, if you are trying to look at other rows in the table from an insert/update trigger, YOU'RE DOING SOMETHING WRONG

                          Furthermore, STAY AWAY FROM java2s.com. It's first and foremost a site which plagiarises paid works (all you have to do is google for the name of that trigger and you'll see its from a copyrighted work.

                          And furthermore, just because you see something on the Internet doesn't mean it's good, correct, or otherwise useful.
                          • 10. Re: Use of commit inside a trigger or an exception.
                            BS2012
                            Hey Guys,
                            You all need to see this as well. Please check this link as well.

                            http://decipherinfosys.wordpress.com/2009/06/22/mutating-tabletrigger-error-and-how-to-resolve-it/

                            This guy is also using autonomous_transaction inside a trigger.

                            Regards,
                            BS2012.
                            • 11. Re: Use of commit inside a trigger or an exception.
                              jeneesh
                              BS2012 wrote:
                              Hey Guys,
                              You all need to see this as well. Please check this link as well.

                              http://decipherinfosys.wordpress.com/2009/06/22/mutating-tabletrigger-error-and-how-to-resolve-it/

                              This guy is also using autonomous_transaction inside a trigger.

                              Regards,
                              BS2012.
                              I will use a quote as a reply for you..
                              John Stegeman wrote:
                              And furthermore, just because you see something on the Internet doesn't mean it's good, correct, or otherwise useful.
                              • 12. Re: Use of commit inside a trigger or an exception.
                                BS2012
                                John Stegeman wrote:
                                But how we can handle that potential error if occur inside a trigger?
                                Give us an example.
                                Suppose I'll have to update few employee's sal today. I will write 3 update statement to update those. Now we have a update trigger on employee table and the trigger has to fire thrice and back up those previous data somewhere. In this case when trigger has to fire for the 2nd time, what kind of view is ready for the trigger post updated or pre updated? How Oracle will behave in this situation? Please help me understand.

                                Regards,
                                BS2012.
                                • 13. Re: Use of commit inside a trigger or an exception.
                                  John Stegeman
                                  This guy is also using autonomous_transaction inside a trigger.
                                  So what?

                                  I can find plenty of people on the Internet who are wrong. I can find people on the Internet who show you how to commit terrorist acts; does that mean that it's good and right?

                                  If you want to use an autonomous transaction in a trigger, go for it.

                                  I can guarantee you, you'll be doing it wrong, for so many reasons.

                                  Here are a few:

                                  1). What happens in the case of rollback? You know, the application rolls back or an error occurs and the transaction is rolled back? Your autonomous transaction isn't rolled back because you committed it.

                                  2). If you're trying to read other rows in the same table from your trigger, you almost certainly have a flaw in your logic. The business rule you're probably trying to enforce cannot be enforced in a trigger.

                                  That's why I said - give us an example - from there we can explain to you why it's wrong.
                                  • 14. Re: Use of commit inside a trigger or an exception.
                                    John Stegeman
                                    Now we have a update trigger on employee table and the trigger has to fire thrice and back up those previous data somewhere
                                    You just back up the row that's being updated. The trigger will fire for each row and you have access to both the :OLD and the :NEW data. You don't need to read the table to do that.
                                    1 2 Previous Next