SQL: How to insert a custom increment value

Question

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.

You will get your answer from the following link https://stackoverflow.com/questions/10990347/how-to-set-auto-increment-column-with-sql-developer — Nazmul Hasan, Jul 11 '18 at 05:05
@NazmulHasan - auto incrementing is different from what the OP wants to do. — APC, Jul 11 '18 at 06:10

score 1 · Answer 1 · answered Jul 11 '18 at 05:23

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.

There can be gaps when you use autonomous_transaction. Consider the insert took place but afterwards rollbacked happened. The autonomous transaction was committed, main was rollbacked. registration_number is generated but not used. — Maxim Borunov, Jul 11 '18 at 05:30
@MaximBorunov - the function needs to apply the autonomous transaction pragma to minimize the impact of serializing on the `regnum` table. It's the only way to scale in a multi-user environment. — APC, Jul 11 '18 at 06:23
@Littlefoot - there can be legitimate reasons for doing this. If we are building an application with a legacy/user-facing business key of `year, branch_code, registration_number`: we can use a sequence as a technical primary key but we still need to support the reference the users are used to. — APC, Jul 11 '18 at 06:27
Right, @APC. I use something like this as well, as users wanted to "restart" the sequence every year. — Littlefoot, Jul 11 '18 at 07:31

score 0 · Answer 2 · answered Jul 11 '18 at 05:35

0

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 Jul 11 '18 at 05:35

Kaushik Nayak

30,772
5
32
45

The problem with this approach is the ORDER BY NULL, which turns `registration_number` into a random value. Let's assume there is a valid requirement for this number (such as implementing a (legacy) business key: in this case we need `year, branch_code, registration_number` to be stable. – APC Jul 11 '18 at 06:18
@APC : you're right. Still i feel using a primary key or redesigning the data model is better rather than triggers and other things. It's my opinion though. – Kaushik Nayak Jul 11 '18 at 06:33
I've worked on systems where the users regularly referenced cases by the case number, which was basically `year, branch_code, registration_number`. We shouldn't try force the users to change their business practices. – APC Jul 11 '18 at 06:38

score 0 · Accepted Answer · answered Jul 11 '18 at 07:04

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;

SQL: How to insert a custom increment value

3 Answers3