I have an autoincrement id column in my table by using a sequence and an update triggers. Working okay so far.
Assume we already have 10 records, and the sequence value is 10 as well. Then, when the next record is inserted the sequence will be incremented by 1 and the id column will be 11, okay.
But, I want that trigger to be able to handle situations where a duplicate key should occur.
Assume now that - for some reason - we already have a record with id 12, then the next insert will cause a duplicate key error.
How can I change the trigger to repeat the sequence.nextval until it finds a situation where it can safely insert the record?
create trigger trg_autonumber
before insert on tbl_autonumber
for each row
select seq_autonumber.nextval into :new.id from dual;