mutating table error help

hi.

create trigger a_trigger before update on a_table for each row
begin

        select something 
        from a_table
        where (using :new.values)

i know the mutating error happens because i 'm querying the a_table where the table's updating is not finished(inside begin/end transaction)

--oracle developer's guide states this.. but not concrete example..

If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.

should i make tmpTable like this..
a_table_id_col / queried_column_old_val /queried_column_new_val

and a_trigger updates tmpTable
tmpTable_after_trigger updates a_table

then, will the second update by tmpTable_after_trigger will trigger a_trigger again (and loop)?

thank you Received on Sun Sep 15 2002 - 08:04:19 CDT