Re: ID's in a table

LouisD wrote:

> Dan
>
> I am sorry about the mess, I am not sure why the comuter is doing this i.e
> repeteadly posting the messages.
>
> What I am trying to achieve is to make the ID column in a table say Names_ID
> be automatically numerated but with a alphbet prefix so that the Name_id
> column will have start with N1 and continue to N2 N3 N4 etc as each new
> record is created
>
> Louis
> "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3ED13D8B.3EAC5C5A_at_exxesolutions.com...
> > LouisD wrote:
> >
> > > I understand that when you create a table the ID column is a numerical
> > > sequence. How can I get the ID prefixed with a letter every time record
> is
> > > saved so the the ID will always have a single pretermined alphabet
> preceding
> > > the ID numbers
> > >
> > > Thanks in advance.
> > >
> > > Louis
> >
> > You MISunderstand several things.
> >
> > First there are no ID columns in Oracle. What database, if any, do you
> have
> > experience with?
> >
> > Second what you are suggesting is a total, complete, and malicious
> violation of
> > relational integrity.
> >
> > Explain what you are trying to accomplish and I, or someone else, will
> help you
> > with the proper way to accompish it.
> >
> > Right now all you are making is an ugly mess.
> > --
> > Daniel Morgan
> > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> > damorgan_at_x.washington.edu
> > (replace 'x' with a 'u' to reply)
> >
> >

To do this you need a BEFORE-INSERT trigger. Something like this:

CREATE OR REPLACE TRIGGER bi_tablename
BEFORE INSERT
ON tablename
FOR EACH ROW DECLARE i PLS_INTEGER;

BEGIN
   SELECT sequence_name.NEXTVAL
   INTO i
   FROM dual;

   :NEW.id_column := 'N' || TO_CHAR(i);

END bi_tablename;
/

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)