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.

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.