SQL: How to insert a custom increment value

Currently I have a table that looks like:

Year | Branch_Code | Registration_Number | ...
______________________________________________
2018 | BRANCH1     | 1                   | ...
2018 | BRANCH1     | 2                   | ...
2018 | BRANCH2     | 1                   | ...

So every time I insert my data into the table I want the Registration_Number to be auto-increment with the dependency to the Year and the Branch_Code. I've tried to get the max value first and insert later, but it sometimes insert duplicate number if my clients insert at the same time.

Does anybody have any solutions?

P.S. I'm using Laravel Framework and Oracle Database.

3 answers

  • answered 2018-07-11 05:23 Littlefoot

    I suggest you to use a sequence and stop worrying about it.

    Alternatively, you might try with something like this:

    • create a table (regnum in my example) which holds the last registration_number for the [year, branch_code] combination
    • increment it in a function that is an autonomous transaction (so that its COMMIT won't affect main transaction)
    • populate the target table's (yourt in my example) in a trigger

    Here's how:

    Tables first:

    SQL> create table yourt (year number, branch_code varchar2(20), registration_number number, datum date);
    
    Table created.
    
    SQL> create table regnum (year number, branch_code varchar2(20), registration_number number);
    
    Table created.
    

    Function:

    SQL> create or replace function f_regnum (par_year in number, par_branch_code in varchar2)
      2    return number
      3  is
      4    pragma autonomous_transaction;
      5    l_nextval number;
      6  begin
      7    select registration_number + 1
      8      into l_nextval
      9      from regnum
     10      where year = par_year
     11        and branch_code = par_branch_code
     12    for update of registration_number;
     13
     14    update regnum set
     15      registration_number = l_nextval
     16      where year = par_year
     17        and branch_code = par_branch_code;
     18
     19    commit;
     20    return (l_nextval);
     21
     22  exception
     23    when no_data_found then
     24      lock table regnum in exclusive mode;
     25
     26      insert into regnum (year, branch_code, registration_number)
     27      values (par_year, par_branch_code, 1);
     28
     29      commit;
     30      return(1);
     31  end;
     32  /
    
    Function created.
    

    Trigger:

    SQL> create or replace trigger trg_bi_yourt
      2    before insert on yourt
      3    for each row
      4  begin
      5    :new.registration_number := f_regnum(:new.year, :new.branch_code);
      6  end;
      7  /
    
    Trigger created.
    

    Testing:

    SQL> insert into yourt (year, branch_code, datum) values (2017, 'branch 1', date '2017-01-01');
    
    1 row created.
    
    SQL> insert into yourt (year, branch_code, datum) values (2017, 'branch 1', date '2017-01-25');
    
    1 row created.
    
    SQL> insert into yourt (year, branch_code, datum) values (2017, 'branch 2', date '2017-04-14');
    
    1 row created.
    
    SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 3', date '2018-07-11');
    
    1 row created.
    
    SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 1', date '2018-05-21');
    
    1 row created.
    
    SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 3', date '2018-03-14');
    
    1 row created.
    
    SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 3', date '2018-05-17');
    
    1 row created.
    

    The result:

    SQL> select * from yourt order by branch_code, year;
    
          YEAR BRANCH_CODE          REGISTRATION_NUMBER DATUM
    ---------- -------------------- ------------------- ----------
          2017 branch 1                               2 2017-01-25
          2017 branch 1                               1 2017-01-01
          2018 branch 1                               1 2018-05-21
          2017 branch 2                               1 2017-04-14
          2018 branch 3                               2 2018-03-14
          2018 branch 3                               3 2018-05-17
          2018 branch 3                               1 2018-07-11
    
    7 rows selected.
    
    SQL> select * from regnum order by branch_code, year;
    
          YEAR BRANCH_CODE          REGISTRATION_NUMBER
    ---------- -------------------- -------------------
          2017 branch 1                               2
          2018 branch 1                               1
          2017 branch 2                               1
          2018 branch 3                               3
    
    SQL>
    

    That solution will work in a multi-user environment, won't raise the mutating table error, but performance might (will) suffer if you load a lot of rows at once (for example, using SQL*Loader). Once again, use a sequence.

  • answered 2018-07-11 05:35 Kaushik Nayak

    In my opinion, it is worthless to use auto-increment triggers or other procedures to maintain the integrity of the Registration_Number column, I mean there could be operartions like delete/ update etc which would be difficult to track using such a setup. I would rather use a simpler approach and create a view

    CREATE OR replace VIEW view_t 
    AS 
      SELECT year, 
             branch_code, 
             row_number() 
               over ( 
                 PARTITION BY year, branch_code 
                 ORDER BY NULL) AS REGISTRATION_NUMBER 
      FROM   t; 
    

    Demo

    This way, the registration_number will "automatically" contain the desired number when you query the view, which you may want to use it for displaying or from an external application.

  • answered 2018-07-11 07:04 Sopheakdey Moeun

    Thanks to a guy (I was not be able to remember his name since he has deleted his answer) who told me to create the before insert trigger. I tried to create one and it works like a charm.

    CREATE OR REPLACE TRIGGER TRIGGER_NAME 
    BEFORE INSERT ON TABLE_NAME 
    FOR EACH ROW
        BEGIN
            SELECT NVL(MAX(REGISTRATION_NUMBER), 0) + 1 
            INTO :NEW.REGISTRATION_NUMBER
            FROM TABLE_NAME 
            WHERE TABLE_NAME.YEAR = :NEW.YEAR 
            AND TABLE_NAME.BRANCH_CODE = :NEW.BRANCH_CODE;
        END;