| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with Triggers
A copy of this was sent to "Paul Wiles" <john_at_adzi.com>
(if that email address didn't require changing)
On Mon, 19 Apr 1999 14:26:39 +0100, you wrote:
>I'm running Oracle 8 server on Linux (Redhat 5.2)
>
>I've got a TRIGGER on TABLE_A AFTER UPDATE OR INSERT
>
>I also have a stored procedure (PROC_A) which performs an INSERT or UPDATE
>to TABLE_A.
>
>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? Do Oracle triggers end up calling themselves under certain
>conditions? What else could cause this problem?
>
they only are invoked recursively if you program them that way. If proc_a can fire the trigger and if the trigger happens to call proc_a, then recursion can happen.
To see if that is the case, try this:
create or replace package my_state_package as
cnt number default 0;
end;
/
create or replace trigger YOUR_TRIGGER
after update or insert on table_a
begin
if ( my_state_package.cnt > 0 ) then
raise_application_error( -20001, 'bummer, called recursively' );
end if;
my_state_package.cnt := my_state_package.cnt+1;
... your original code here ....
my_state_package.cnt := 0;
end;
/
that way, you can catch the recursion if its happening and then try to figure out why. Another thing you can do is 'instrument' your existing code with calls to dbms_application_info (see $ORACLE_HOME/rdbms/admin/dbmsapin.sql for info on that package). Then, you can set upto 3 columns in the dynamic v$session table to 'watch' your code. you can start your stuff in one session, and from another session "select username, module, action, client_info from v$session where username = USER" to see what the other session is doing.
>Any help much appreciated.
>
>Paul
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |