web form insert and trigger before insert

Question

I have a oracle table called:

create table InsertHere(
    generate_id varchar2(10),
    name varchar2(100)
);

I have to insert data in this table from a web from. In the web form there are two elements:

type and name

. I have to generate 'generate_id' from the type user has selected.

Task :

Before insert into the InsertHere table i have to generate the generate_id. Then i have to insert 'generate_id' and 'name' into the InsertHere table. BUT THE GENERATION OF ID MUST BE DONE INSIDE THE DATABASE(may be with a procedure).

Question:

How this thing can be done effectively?

I need suggestions.

Thanks in advance

Answer 1

It all depends what you mean by "generating ID". The ID is I presume a primary key, so its value must be unique regardless of TYPE. So what rules do you want to apply to its generation?

Here is an indicative approach. This uses a sequence to get a value and prepends a character, depending on type.

SQL> create or replace function generator
  2      (p_type varchar2
  3       , p_name in inserthere.name%type)
  4      return inserthere.generate_id%type
  5  is
  6      c char(1);
  7      return_value inserthere.generate_id%type;
  8  begin
  9      case p_type
 10          when 'EXTREME' then
 11              c := 'X';
 12          when 'REGULAR' then
 13              c := 'R';
 14          when 'JUMBO' then
 15              c := 'J';
 16          else
 17              c := 'P';
 18      end case;
 19
 20      insert into inserthere
 21          ( generate_id,
 22          name)
 23      values
 24          (c || lpad(trim(to_char(my_seq.nextval)), 9, '0')
 25           , p_name )
 26      returning generate_id into return_value;
 27
 28      return return_value;
 29  end;
 30  /

Function created.

SQL>

here it is in action

SQL> var n varchar2(10)
SQL> exec :n := generator ('EXTREME', 'ABC')

PL/SQL procedure successfully completed.

SQL> print n

N
--------------------------------
X000000001

SQL> exec :n := generator ('WHATEVER', 'SOMETHING')

PL/SQL procedure successfully completed.

SQL> print n

N
--------------------------------
P000000002

SQL>

You can vary the precise implementation, depending on how you want to call it. As is often the case, details matter and more information will tend to result in a more relevant answer.