René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

December 19, 2005: On triggers, sequences, autonumbers and failing inserts

Oracle's way to generate the SQLServer's equivalent of autonumbers is to use triggers and sequences.
I am particularly interested in what happens when I create a sequence and a trigger, do some inserts of which one fails. Will the sequence be increased or not? Here's the answer.
As usual for such cases, I start by creating a table: 
create table autonumber_tbl (
  autonumber  number        primary key,
  column_1    varchar2(10),
  column_2    varchar2(20) check (column_2 = upper(column_2))
);
There's a check check constraint on the table that allows uppercase words in column_2 only.
Next: the sequence... 
create sequence autonumber_seq;
... and the trigger. 
create trigger autonumber_trg
  before insert on autonumber_tbl for each row
begin
  select autonumber_seq.nextval into :new.autonumber 
    from dual;
end;
/
Finally: a little autonomous block: 
declare

  prevent_ora_08002 number; -- sequence AUTONUMBER_SEQ.CURRVAL is not yet defined in this session

  procedure fill(
    col_1 in varchar2,
    col_2 in varchar2
  )
  is 

    returned_autonumber number;
    seq_value           number;
  
  begin
    dbms_output.new_line;
    dbms_output.put_line('inserting: ' || col_1 || ' ' || col_2);
    select autonumber_seq.currval into seq_value from dual;
    dbms_output.put_line('  sequence before: ' || seq_value);

    begin

      insert into autonumber_tbl 
        (column_1, column_2) values
        (col_1   , col_2   ) returning
        autonumber into returned_autonumber;

    exception when others then null;
    end;

    select autonumber_seq.currval into seq_value from dual;
    dbms_output.put_line('  sequence after: '      || seq_value);
    dbms_output.put_line('  returned autonumber: ' || returned_autonumber);

  end;


  begin

    -- prevent: sequence .... is not yet defined in this session
    select autonumber_seq.nextval into prevent_ora_08002
    from dual;


    fill('foo', 'FOO');
    fill('bar', 'BAR');
    fill('BAZ', 'baz'); -- col_2's baz is in small caps, will fail
    fill('qux', 'QUX');

end;
/
Executing this block gives: 
inserting: foo FOO
  sequence before: 1
  sequence after: 2
  returned autonumber: 2

inserting: bar BAR
  sequence before: 2
  sequence after: 3
  returned autonumber: 3

inserting: BAZ baz
  sequence before: 3
  sequence after: 4
  returned autonumber:

inserting: qux QUX
  sequence before: 4
  sequence after: 5
  returned autonumber: 5
As can be seen, the sequence is incremented even in the case where an insert fails.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.