Re: Mutating table

A copy of this was sent to Matt Houseman <mhouseman_at_inprise.com> (if that email address didn't require changing) On Mon, 21 Dec 1998 11:22:42 -0800, you wrote:

>All,
>
>I'm attempting to load information from a spreadsheet that contains many
>columns; one of which is a phone number. In the source data, the phone
>number is either 'UNKNOWN' or the actual phone number. The requirement
>from the user community is to set the phone number to
>'UNKNOWN_<seq_num>' on insert if the source data is 'UNKNOWN'. It seems
>to me that any trigger that I write will cause a mutating table error.
>Does anyone know a method by which this can be accomplished? Thanks in
>advance...
>
>Matt Houseman
>

create or replace trigger mytrigger
before insert on mytable
for each row
begin

	if ( :new.phone_number = 'UNKNOWN' ) then
        select 'UNKNOWN_' || myseq.nextval into :new.phone_number
          from dual;

will do it. You can write to the :new record in an insert/update for each row trigger.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.