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.