title: Custom Sequence using Trigger & Sequence in Oracle SQL
body: <p>I have a trigger which inserts a custom Sequence (Ref_No.) after every Item(Parent Item) is inserted.
  Now I have a sub-Item which is linked to the first item (Parent Item) through Parent Item Id and it 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.</p>
  
  <p>also every time a new parent item is inserted the sub-item's sequence should append parent Items Sequence followed by 1 and increment further.</p>
  
  <p>This is my trigger for generating Parent Item's Sequence</p>
  
  <pre><code>    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.REF_NO := substr(PREFIX,1,1)||(:NEW.CATEGORY_ID)||(:NEW.SUBCAT_ID)||(:NEW.ID);
  
   end;
  </code></pre>
  
  <p>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 then the first sub item should be ParentItemSequence-1 and then again count and append "-2", "-3" and so on</p>
  
  <p>My Sub-Item Table is "Sub-Item" with a column "Sequence".</p>
  
  <p>Need Help !!!</p>