Re: Mutating table
Date: Mon, 21 Dec 1998 21:03:33 GMT
Message-ID: <367eb6cc.1092390_at_192.86.155.100>
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;
end if;
end;
/
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.Received on Mon Dec 21 1998 - 22:03:33 CET