I have been looking for a way to define an autoincrement data type in Oracle and have found these questions on Stack Overflow:
Autoincrement in Oracle
Autoincrement Primary key in Oracle database
The way to use autoincrement types consists in defining a sequence and a trigger to make insertion transparent, where the insertion trigger looks so:
create trigger mytable_trg
before insert on mytable
for each row
when (new.id is null)
begin
select myseq.nextval into :new.id from dual;
end;
I have some doubts about the behaviour of this trigger:
What does this trigger do when the supplied value of "id" is different from NULL?
What does the colon before "new" mean?
I want the trigger to insert the new row with the next value of the sequence as ID whatever the supplied value of "new.id" is. I imagine that the WHEN statement makes the trigger to only insert the new row if the supplied ID is NULL (and it will not insert, or will fail, otherwise).
Could I just remove the WHEN statement in order for the trigger to always insert using the next value of the sequence?
