Re: Problem with Triggers

Paul Wiles wrote:

>When I call PROC_A, from MySQL, the session just hangs. There is
>only one trigger on this table so how could this be caused by
>infinite recursion in the trigger?

You'd know if it was recursion, because after a short time (usually only a few seconds) you'd run out of cursors, and the query would terminate with an error to that effect.

>Do Oracle triggers end up calling themselves under certain conditions?

If you create a trigger on TABLE_A that updates TABLE_A (perhaps indirectly), then yes, they can. Normally, you won't do this.

>What else could cause this problem?

Check what the server is doing. I find that running vmstat during a query will give a quick picture of what the system is doing. If it's doing a boatload of I/O, or the CPU is pegged at 100% busy, then your code is just taking a long time to do its thing.

If that isn't the case, then you've possibly created a deadlock. If so, the RDBMS should detect the deadlock and terminate the query after a while. Received on Thu Apr 22 1999 - 00:00:00 CDT