0

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;
  • Ignoring the input value without generating an error, and using the sequence instead, sounds like a bad design. Any code should not reference the column at all, so presumably, only users who are running inserts AD-HOC (via: SQLPlus, SQLDev, TOAD, whatever...) would have issues here. If I was the aforementioned end user, I would much rather see "ORA-32795: cannot insert into a generated always identity column", then have it silently ignore my input and just do its own thing... – Kris Johnston Feb 16 '17 at 17:29
  • The main purpose would be convenience. Say if I have a large table with many columns. If I create inserts of selected data I would have to edit all those inserts and remove the identity column. With a trigger I could ignore them. – ap0 Feb 17 '17 at 10:26
  • The two main advantages of identity columns is speed + the best kind of code is no code, which Connor McDonald explains in a mere 4 minutes: youtube.com/watch?v=O62vLxQSxxI. Since you aren't benefiting from either of these advantages with your circumvention, If you're still set on doing this, you're probably better off not using an identity column at all and just create/maintain the sequences + triggers yourself like most Oracle shops did under 11g. – Kris Johnston Feb 17 '17 at 18:26
  • The correct solution to your problem is to fix the incoming code so that it will not specify value for column ID. You can then retain generated always as identity clause. – Raj Feb 20 '17 at 13:07
0

You are reverse engineering a concept.

Identities are simple, one time event entry. If one table doesnt fall within the complexity of the operations, skip the usage and work on a trigger logic solution. But dont try to complicate things, and create more calls than necessary.
Identity usage is not mandatory, triggers are not going away...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.