Behaviour of insertion trigger when defining autoincrement in Oracle

Question

I have been looking for a way to define an autoincrement data type in Oracle and have found these questions on Stack Overflow:

1. Autoincrement in Oracle
2. 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:

1. What does this trigger do when the supplied value of "id" is different from NULL?
2. 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?

Answer 1

1. Nothing. That allows to specify a value manually.
2. It's a placeholder for the new value of the column.

Answer 2

The WHEN condition specifies a condition that must be TRUE for the trigger to fire. In this exampple, the trigger will only fire if the new row has a NULL IS. When the ID is not null, the trigger will not fire and so whatever value ID has been given in the insert statement will be left alone.

Yes, if you simply remove the WHEN condition then the trigger will always fire and so will always provide a sequence value for ID.

Answer 3

You have 2 methods you can do:

if the table looks like this:

create table my_test (
id number, 
my_test data varchar2(255)
); 

and your sequence is this:

create sequence test_seq 
start with 1 
increment by 1 
nomaxvalue;  

you can create a trigger like this (with no When statement like Tony Andrews said)

create trigger test_trigger
before insert on my_test
for each row
begin
select test_seq.nextval into :new.id from dual;
end; 

or you could just simply use this then you don't need a trigger:

insert into my_test values(test_seq.nextval, 'voila!');