Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Triggers

Re: Triggers

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/25
Message-ID: <8j5pbc$mb$1@nnrp1.deja.com>#1/1

In article <961943072.582774_at_romulus.infonie.fr>,   "air_StExupery" <picardj_at_altern.org> wrote:
> I want to alter a name of a field when I'm adding it inside the
 database.
> The name given by the application is 'XXXX', and I want to add a
 sequence
> number which is unique and add it to the name. So the value of the
 name will
> be 'XXXX_1235' if the sequence is 1235. Name is not a primary key or a
> foreign key.
>
> Simple example is
> create table tablesample(
> key_id char(6) primary key,
> name char(25),
> ...);
>
> create or replace trigger Calculate_Name
> before insert on tablesample
> for each row when(new.key_id is not null)
> begin
> new.name := :new.name || '_' || TO_CHAR(MySequence.NextVal);
> end;
>
> I need anyway to calculate that field automatically, and I can't
 modify the
> librairy which is trying to insert the new record.
>
>

create or replace trigger Calculate_name before insert on tablesampe
for each row
begin
  select :new.name||'_'||mysequence.nextval into :new.name from dual; end;
/

you have to select sequences always.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sun Jun 25 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US