auto generated primary key

[imranhad]

Trigger : when create recorde.

select Max(id)+1 int :table1.ID
from table1;

i am having problem when more than one user are using same form.
If i use sequence no then, there are chances of having gap between primary
key no like 1,2,3,5,7,20 etc. because if sequence generated id lost ,it can not be generated again.

in need result like this
1,2,3,4,5...n with gap in primary ket field.
thanks

[naumaan]

Use pre-insert trigger instead of when create record and also minimize the sequence cache size

Thanks

Regards,
Naumaan Sagheer

[mirza_rehan]

Yes Pre-Insert trigger is batter for your required tast



Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807

When ever you pray
Please remember me and my family

[imranhad]

ok i will use pre-insert trigger,
but problem is same
i don't want gap between ids.
if one entery is deleted, sequence will not check
missing value, it will generated new unique number.

[mirza_rehan]

For this task you do not use Sequence try to use Nvl(Max(ColumnName),0) + 1 logic, this logic cover if last record deleted it fulfill yours logic but not full fill yours whole requirement for that task you use PL/SQL or you can try to Logical delete logic....... but in logical delete it is difficult to handle.

take care
Allah Hafiz

Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807

When ever you pray
Please remember me and my family