SQL & PL/SQL

Hi,

I have a row level before update trigger written on a table A which calls a procedure to undergo some processing. Procedure has some select queries against table A and it inturn causes the following error.

ORA-04091: table name is mutating, trigger/function may not see it

To overcome this I have used a combination of PL/SQL collection types (nested tables based on the following definition TYPE t_table_a is table of A.colname%TYPE index by binary_integer;), a row level before update trigger and statement level after update trigger. The mutating problem is fixed, but to update one row of data it took around 3 min and I am wondering if its the problem with PL/SQL tables I have used.

In before update trigger I am storing the unique id's which needs to be updated into the PL/SQL table and in the after update trigger I am looping through the PL/SQL table and peforming the rest of the processing by calling in the procedure.

Can anyone help how to minimize the run time of this process or if any other better solution exists?

Thanks for the help