insert auto generated number of one column into another column

Question

I have a database table with two columns customer_id Number and first_name VARCHAR2
For customer_id, the number will be auto incremented. I need first_name with the value of customer_id in a specific format.

See the below example:

If the customer_id is auto incremented to value 1, the first_name should be NAME-1 if the customer_id is auto incremented to value 2, the first_name should be with 'NAME-2'

The only feasible option I got so far is performing 3 SQL statements.
One for insert and the next one is to read the incremented value and perform update to the first_name column.

I greatly appreciate the help and guidance if anyone can suggest a better way of handling this in minimum possible SQL queries.

Answer 1

One option is to create an identity column (for the ID column) and a database trigger which sets first name to desired value.

Table:

SQL> create table test (customer_id number generated always as identity,
  2                     first_name varchar2(10));

Table created.

Trigger:

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4  begin
  5    :new.first_name := :new.first_name ||'-'|| :new.customer_id;
  6  end;
  7  /

Trigger created.

Testing:

SQL> insert into test (first_name) values ('Scott');

1 row created.

SQL> insert into test (first_name) values ('Mike');

1 row created.

Result:

SQL> select * from test;

CUSTOMER_ID FIRST_NAME
----------- ----------
          1 Scott-1
          2 Mike-2

SQL>