Oracle Custom Sequence of a partitioned group (Insert trigger)

Question

Please help, need an insert trigger to insert the group_ID (next sequential number) of a partitioned group. Much like a dense_rank of repeating group of names. When a new record is created, if the name exists, then the group_id will be this name's group_id. If the name does not exist, then the group_id will be auto generated to a new number that hasn't been used.

Not sure what this is called.

Create table T1 (
Group_Name Varchar2(50)
,Group_ID Number
);
commit;
Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );
Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );
Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );
Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );
Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );
Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );
Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );
COMMIT;
SELECT * FROM T1;
GROUP_NAME GROUP_ID
---------- ---------
ABC 1
ABC 1
ABC 1
EFG 2
EFG 2
HIJ 3
HIJ 3

Need An insert trigger to insert the GROUP_ID based on this logic:

1. if the inserted value for GROUP_NAME already exists in T1, then GROUP_ID gets its already defined GROUP_ID for GROUP_NAME
2. if GROUP_NAME does not exist in T1, insert the next sequential number for a this new "Grouping" for GROUP_ID.

Insert examples and what GROUP_ID should be are below:

INSERT INTO T1 (GROUP_NM) VALUES('ABC');

*** Since 'ABC' is found in the table with Group_ID of 1, the insert trigger, should evaluate and update the table of GROUP_ID to 1.

INSERT INTO T1 (GROUP_NM) VALUES('PEP');

*** Since 'PEP' group name inserted is not found in the table T1, the trigger should auto generate the next value of 4 for the GROUP_ID.

• Michael Kutz: Can there be gaps in the sequence of the numbers? (please say "yes")
• Fokie: yes there can be gaps in the numbering, but the group name to id must be unique
• Michael Kutz: You do realize you are attempting to create a Child record before you create the Parent. Right? Use of a trigger for this process is not recommended.
• Fokie: There is no separate parent nor child table. An insert trigger should be needed for new inserts into T1 of only the Group Name. The Group ID will be auto generated by this "insert trigger". Group ID logic is based on the above. If Group Name exists within T1, Group ID = its existing Group ID else if Group Name is a new name and does not exist yet in T1, Group ID = newly generated number not existing yet in T1.
• stefan: @ALOM: Can you not just use a hash value (of the group name) as id?

Answer 1

Not Possible

Your suggested Implementation of your Business Requirement is not possible.

You cannot select from table T1 from within a trigger on T1. There are hacks around such limitation. But, that is what they are: hacks. One of them has a side effect of preventing you from performing a rollback.

In all cases: you'll still want to define a sequence for group_id.

create sequence group_id_seq;

Work Around - Correct your schema design

If you want to ensure that each Unique group_name contains a Unique group_id, then you need to create a table that enforces such data logic.

create table unique_groups (
 group_id int generate by default on null as identity
 ,group_name varchar2(50) not null
 ,constraint unique_group_pk primary key (group_id)
 ,constraint unique_group_uq1 unique (group_name)
);
alter table t1
 add constraint t1_fk1 foreign key (group_id) references unique_group( group_id );
alter table t1
 add constraint t1_fk2 foreign key (group_name) references unique_group( group_name );

Then, and only then, you can use a trigger. (I'd still not advise it.)

create or replace
trigger t1_tr
before insert
on t1
for each row
begin
 select a.group_id into :new.group_id
 from unique_groups a
 where a.group_name = :new.group_name;
exception
 when no_data_found then
 insert into unique_groups (group_name)
 values (:new.group_name)
 returning group_id into :new.group_id;
end;
/

Work Around - TAPI

One work is the use of Table APIs (TAPI).

This is a Package that performs all necessary tasks to perform an insert,update, or delete. Instead of your application performing the DML, the application calls the TAPI to perform the DML.

If necessary, you can wrap the Package with a VIEW and an INSTEAD OF trigger.

Package Spec:

create or replace package t1_tapi_pkg as
 -- I like to customize known exceptions for the application.
 data_corruption_detected exception;
 PRAGMA EXCEPTION_INIT (data_corruption_detected, -20000);
 table_in_use_try_again exception;
 PRAGMA EXCEPTION_INIT (lock_timeout, -20001);
 lock_timeout exception;
 PRAGMA EXCEPTION_INIT (lock_timeout, -54); -- These are caught and re-raised as -20001
 function ins (
 group_name in t1.group_name%type
 ) return t1.group_id%type;
 -- update logic has not been defined.
 -- regular SELECT and DELETE statements can be used.
end;
/

Package Body:

create or replace
package body t1_tapi_pkg
as
 function ins (
 group_name in t1.group_name%type
 ) return t1.group_id%type as
 group_id t1.group_id;
 begin
 -- You should change to "WAIT 3"
 lock table t1 in exclusive mode nowait;
 begin
 -- find existing
 select distinct a.group_id
 into ins.group_id
 from t1 a
 where a.group_name = ins.group_name;
 -- re-use that value
 insert into t1 a (group_name, group_id)
 values ( ins.group_name, ins.group_id );
 exception
 when no_data_found then
 -- create a new unique value
 insert into t1 a (group_name, group_id)
 values ( ins.group_name, group_id_seq.nextval )
 returning a.group_id
 into ins.group_id;
 when too_many_rows
 -- the GROUP_NAME does not have a unique GROUP_ID
 then raise data_corruption_detected;
 end;
 return ins.group_id;
 exception
 -- not necessary. I like to customize my exceptions.
 when lock_timeout then
 raise table_in_use_try_again;
 end;
end;
/

Answer 2

The above solution works great! Thank you Michael Kutz. Also works is using the hash value (of the group name) in an insert/update trigger since hash value is highly accurate to strings/number combinations and is persistent through sessions. Thank you also to Stefan.