Re: how to create unique automatic increment field?

Do I need to use a trigger
(on update of displayorder find other lower or equal values and increment them by one)
YES
or is there a different way?
NO
Please don't repost if you are not answered immediately, Usenet is a volunteer business.

Hth,

Sybrand Bakker,Oracle DBA

Sharkie <sharkie2_at_my-deja.com> wrote in message news:8c325o$h1t$1_at_nnrp1.deja.com...
> I have this table:
>
> Column Name Null? Type
> ------------------------------ -------- ----
> TEMPID NOT NULL NUMBER(38)
> DISPLAYORDER NUMBER(38)
> NAME VARCHAR2(100)
>
> I want displayorder field to (as the name implies) decide
> in which order records will be displayed using ORDER BY clause.
> This field has UNIQUE constraint.
>
> Now, let's say this table has following 5 records:
> TEMPID DISPLAYORD NAME
> ---------- ---------- ----------
> 20 1 1st record
> 21 2 2nd record
> 22 3 3rd record
> 23 4 4th record
> 24 5 5th record
>
> Now I want to change the last record (5th record) to be displayed first,
> and hence change displayorder field to 1. I can't just do a regular
> update since this will violate the unique constraint. Even without
> the unique constraint update would not give me wanted results,
> since now I would have two records with value of 1.
>
> Is there a way
> in oracle to automatically change the other values "up"? So, if I
> change the display order of 5th record to 1, the results would be:
> TEMPID DISPLAYORD NAME
> ---------- ---------- ----------
> 20 2 1st record
> 21 3 2nd record
> 22 4 3rd record
> 23 5 4th record
> 24 1 5th record
>
> Something like automatic increment that would still guarantee
> the uniqueness of the field. Do I need to use a trigger
> (on update of displayorder find other lower or equal values and
> increment them by one) or is there a different way?
>
> thanks for any help
>
>
>
> --
> If the human brain were so simple
> that we could understand it,
> we would be so simple we couldn't.
> -Makes Sense... don't it?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Mar 31 2000 - 15:49:30 CST