7 Replies Latest reply: Jan 31, 2013 6:29 PM by SomeoneElse RSS

    Recursive Trigger

    700928
      Hi,
      Pls give me a brief idea about Recursive Trigger.


      user10313295
        • 1. Re: Recursive Trigger
          Boneist
          Other than it sounds like a bad idea, you mean?

          What do you mean by Recursive Trigger? Do you mean where updating one row then triggers other rows to be updated? Oracle doesn't allow this; you'll get a mutating table error.
          • 2. Re: Recursive Trigger
            Hoek
            'Recursive' means the program calls itself:

            http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1811

            A trigger calling itself seems a 'funky' requirement/bad idea/impossible.
            • 3. Re: Recursive Trigger
              riedelme
              I have to agree with Boneist and Hoek on this. I have a bad feeling about the concept of a trigger calling itself, assuming that's what you mean by "recursive".

              Triggers are programs that execute according to an event. The concept of recursion as procedure-calls-itself does not really apply to triggers as they are generally understood.

              You probably can call a recursive procedure from a trigger if necessary, but be careful to avoid endless loops if you do so.
              • 4. Re: Recursive Trigger
                Frank Kulash
                Hi,

                Like all the other respondents, I don't know what you mean by "recursive trigger", but it sounds like a bad idea.
                I don't even know how a trigger could call itself.
                A trigger could cause changes to other rows in the same table, but this is a bad idea, and usually indicates a bad table design.

                If you want to call a recursive procedure from a trigger (for example, if you want to calculate a Fibonacci number in the trigger), then go ahead. That's no problem.
                If you want to make some change to a parent row whenever a child is changed, then rethink you design. Whatever calculation you're trying to do in the trigger, why can't you do it in a query or a view?

                Explain what you want to do (with a specific example or two) if you want help finding the best way to do it.
                • 5. Re: Recursive Trigger
                  riedelme
                  I don't even know how a trigger could call itself.
                  I think its possible (sort of) but have no intention of testing the theory. Suffice to say that it requires a lot of creative thinking and that there can be a very fine line between genius and foolishness.

                  Nothing is idiot-proof because idiots are ingenious, and just because we can do something does not mean we should :)
                  • 6. Re: Recursive Trigger
                    user448875
                    I think what he means is, if you're writing a trigger and calling one of more procedures that would accidently update the table (somewhere in the long code) which will fire the same trigger over again. That being said, it's possible if you have multiple triggers and a bunch of code under it , at some point you've to be careful not to call statements that would fire the same trigger you're in for some distance procedure. That would be a pretty bad idea. It's easy to get lost, if too much goes into triggers. Keep them compact and only as needed.
                    • 7. Re: Recursive Trigger
                      SomeoneElse
                      (pssst...thread is 4 years old dude)