Hi i got sequance
CREATE SEQUENCE "BAZA"."SEKW_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 10001 CACHE 10000 NOORDER NOCYCLE ;
create or replace
BEFORE INSERT ON spolki
FOR EACH ROW
when i delete the row i created it gives me new value like i created 1,2 and deleted 1 then run trigger and i have 2,3 how to do it to have 1,2 after running trigger?
Dont fix it..
It should be 2 a,nd 3 only..
Why do you want to make it 1 and 2 ? Any business rule? What about the records in child tables, when you change the ID? What you want to happen in a multi user environment?
And it will lead to serialization..
See all these discussions: sequence numbers - with no gaps!
i want to make continous numbering after deleting row
Tom Kyte, Oracle ACE and noted author, said it best over 12 years ago:
SEQUENCES WILL NEVER GENERATE A GAP FREE SEQUENCE OF NUMBERS!
See this AskTom is from 2001. It is just as valid now as it was then
Sequences will never generate a gap free sequence of numbers.
If someone calls seq.nextval and doesn't insert the value somewhere (or does insert it
and later rolls back their transaction) that number is GONE.
Sequences are database objects and may be aged out of the shared pool like everything
else -- it doesn't take a database shutdown to produce a gap.
You should never count on a sequence generating anything even close to a gap free
sequence of numbers.