This content has been marked as final. Show 7 replies
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.
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.
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 :)
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.