Re: Automatic random number on row creation

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 29 Dec 2005 19:46:17 +0100
Message-ID: <43b42efa$0$30403$626a14ce@news.free.fr>

"Kuon" <kuon_at_goyman.com> a écrit dans le message de news: dp0uo3$1t3$1_at_news.hispeed.ch...
| Hello,
|
| I have a ticket system which need a random (act as a password) string
| generated. But this string also act as a key (need to be unique).
|
| The approach of the former dev does not suites me.
|
| He was generating a random string, doing a select key where key = ... To
| verify the uniqueness of the key.
|
| If the select returned nothing, he inserted the new row.
|
| Of course this is not good, for many reasons.
|
| I want to do it database side.
|
| I know something like that:
| select dbms_random.string('U', 20) str from dual
|
| for my random number.
|
| But how, when I do:
|
| insert into myTable (otherInfos) values ("myotherinfos"),
|
| how can the "key" column been automaticaly assigned a random and unique
| string?
|
| Then I can select this column and pass it in my app.
|
| Thanks a lot
|
| Regards
|
| Kuon

Notwithstanding Mladen answer (you can handle the duplicate key in your application and retry the insert), you can do something like:

SQL> create table t2 (id varchar2(20), val number);

Table created.

SQL> create trigger t2_bir before insert on t2 for each row   2 begin
  3 :new.id := dbms_random.string('U', 20) ;   4 end;
  5 /

Trigger created.

SQL> var id varchar2(20)
SQL> insert into t2 (val) values(1) returning id into :id;

1 row created.

SQL> print id
ID

ZFQACDEGCROHBYTXXXNK SQL> select * from t2;

ID                          VAL
-------------------- ----------
ZFQACDEGCROHBYTXXXNK          1

1 row selected.

Regards
Michel Cadot Received on Thu Dec 29 2005 - 12:46:17 CST