Custom Sequence using Trigger & Sequence in Oracle SQL

Question

I have a trigger which inserts a custom Sequence after every Parent Item is inserted. Now I have a sub-Item which is linked to the Parent Item through Parent Item Id (Sequence and Parent Item Id are 2 different column )and sub items can be more than 1. My trigger for Parent Item generates Sequence as "P2505" and I want my sub-Items Sequence should be "P2505-1", for 2nd Sub-Item it should be "P2505-2" and so on.

If the next parent Item has Sequence "P2506" then sub Item Sequence should be "P2506-1" and so on.

This is my trigger for generating Parent Item's Sequence

create or replace trigger REF_NO_TRIGGER
    before insert on LOST_ITEM
    for each row 
declare 
    PREFIX varchar2(254); 
begin
    select NAME into PREFIX
        from SUBCAT
        where SUBCAT.ID = :new.SUBCAT_ID; 
    :new.SEQUENCE := substr(PREFIX,1,1)
      ||(:new.CATEGORY_ID) 
      ||(:new.SUBCAT_ID) 
      ||(:new.ID); 
    end;

Parent Item Table

ID  NUMBER(15,0)
CATEGORY_ID NUMBER(3,0)
SUBCAT_ID    NUMBER(5,0)
NAME VARCHAR2(20)
SEQUENCE  VARCHAR2(22 BYTE)

Sub Item Table

ID  NUMBER(30,0)
PARENT_ITEM_ID  NUMBER(15,0)
NAME VARCHAR2(20)
SEQUENCE    VARCHAR2(22 BYTE)

CREATE TABLE FOR PARENT AND SUB ITEM TABLE

CREATE TABLE PARENT_ITEM(ID NUMBER(15) GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
CATEGORY_ID NUMBER(3) NOT NULL,
SUBCAT_ID NUMBER(5) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
SEQUENCE  VARCHAR2(22),
PRIMARY KEY (ID)
);




CREATE TABLE SUB_ITEM(ID NUMBER(30) GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
PARENT_ITEM_ID NUMBER(15) NOT NULL,
NAME VARCHAR2(20),
 SEQUENCE VARCHAR2(22 BYTE),
CONSTRAINT FK_LOST_IT_SUB_LOST_IT FOREIGN KEY (PARENT_ITEM_ID) REFERENCES PARENT_ITEM(ID),
PRIMARY KEY (ID)
);

INSERT INTO PARENT_ITEM(CATEGORY_ID, SUBCAT_ID, NAME) VALUES (2,5,'MAIN_ITEM');

The ID IS GENERATED automatically and Sequence for Parent Item is generated through Trigger. For Sub Item I am only inserting Name and Parent Item ID and I need a Sequence for that.

As per my understanding, I need to check for the parent Item Sequence in Sub-Item table and see if there is any entry , if there is no entry with that sequence then the first sub item should be "ParentItemSequence-1" and then again count and append "-2", "-3" and so on

But I don't know how to achieve this functionality through a trigger because I have to submit all items including Parent and Sub Item at the same time.

Answer 1

Gap Free Sequential Number

What you seek can be referred to as a "gap free sequential number".

These types of Business Requests are known to slow down performance for the tables involved. This is due to the required serialization on the entity that generates the number.

Parent Table

PARENT_ITEM.ID is not going to be a "gap free sequential number"; you are missing the NOCACHE option.

Even then, you can still have gaps.

Child Table

I recommend you keep CHILD_ITEM.ID as a surrogate key. I would keep the defaults for the Identity column. Also, you should have a separate column for the numerical value that represents "-1". I'll call this column CHILD_NUMBER_SEQUENCE.

Now, to serialize the "number generator" for CHILD_SEQUENCE, you must obtain a lock based on PARENT_ITEM_ID.

For Oracle, the easiest way is to lock the parent row. If the table PARENT_ITEM maintains "current child sequence number" the algorithm becomes easy since you can "lock the parent row" and "get the next value" at the same time.

Table Modifications

alter table parent_item add ( current_child_sequence_number number(5) default 0);
alter table child_item add (child_sequence_number number(5) not null);
alter table child_item add
  constraint child_item_uq1 unique (parent_item_id, child_sequence_number);

Code to generate `CHILD_ITEM.CHILD_SEQUENCE_NUMBER'

-- this locks and updates the parent row.
update parent_item p
set p.current_child_sequence_number = nvl(p.current_child_sequence_number,0) + 1
where p.id = :new.parent_item_id
returning p.current_child_sequence_number into :new.child_sequence_number;
-- make sure you check that 1 and only 1 row was updated.
-- fail if that isn't true.

Final Notes

The code is specific for single row processing.

If you want performance for bulk loading, you will have to use a staging table (eg Global Temporary Table) and a Procedure.

Again, the Business Requirement of "gap free sequential numbers" is slow by nature. Don't expect it to be fast.