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.
Best Answer
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 theNOCACHE
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 columnCHILD_NUMBER_SEQUENCE
.Now, to serialize the "number generator" for
CHILD_SEQUENCE
, you must obtain a lock based onPARENT_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
Code to generate `CHILD_ITEM.CHILD_SEQUENCE_NUMBER'
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.