Using the this table definition
CREATE TABLE testtable ( id NUMBER GENERATED AS IDENTITY, desc VARCHAR2(30) );
I would like to create a trigger before inserts and updates to ignore any value given by these statements and instead use the next value in sequence - as if no value was defined in that statement.
I don't want to use
GENERATED ALWAYS AS IDENTITY since this throws an error. I would rather ignore any value given and use the next possible value.
What I am looking for is a way to identify the sequence used for this identity ans use
nextval in a before-insert/update trigger.
In this article a SQL statement is given to get the name of the sequence that is used for the identity which I maybe could use within the trigger (???) but the statement doesn't work for me:
SELECT a.name AS table_name, b.name AS sequence_name FROM sys.idnseq$ c JOIN obj$ a ON c.obj# = a.obj# JOIN obj$ b ON c.seqobj# = b.obj#;
If the statement would work I would have the sequene name as a string but I guess I would need the object itself to request the nextval.
Is a trigger the right way to go or should I look for something else?
For Oracle 11g I have implemented this functionality using trigger and sequences. The trigger looked like this for inserts:
create or replace trigger testtable_sgk before insert on testtable for each row begin select testtable_seq.nextval into :new.id from dual; end;