This discussion is archived
2 Replies Latest reply: Jan 28, 2013 9:39 AM by Ramani_vadakadu RSS

how to Trigger affect to  Sequence  in Apex Page ..

Ramani_vadakadu Journeyer
Currently Being Moderated
I have Apex page with following trigger..
CREATE OR REPLACE TRIGGER MAS_PK_TRIG  
   before insert on "KAX"."DMS_MASTER" 
   for each row
begin  
   if inserting then 
      if :NEW."DOC_ID" is null then 
         select MASTER_SEQ.nextval into :NEW."DOC_ID" from dual; 
      end if; 
   end if; 
end;

CREATE SEQUENCE MASTER_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MINVALUE 0 NOCACHE ;

----Generating Seq ID----

Declare 
   Seq_Val_ Number;
Begin
    if :P2_DOC_ID is null then
      Select Nvl(Max(DOC_ID),0) + 1
        into Seq_Val_ 
        from DMS_MASTER;
         :P2_DOC_ID := Seq_Val_;
    end if
end;
When i inserting new data its stored and Update also working fine.

my problem is

When i deleting the ROW,the Sequence number still stay in last number.
Eg .total 12 record stored in DMS_MASTER.
I deleted ROW number 12(as Primary Key)...AFTER i creating new record it taking Sequence number 13???

It should be coming last number as 12?

Anyone help me plz...

Ram
  • 1. Re: how to Trigger affect to  Sequence  in Apex Page ..
    jariola Guru
    Currently Being Moderated
    Hi,

    You might misunderstand how sequence work.
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm
    I deleted ROW number 12(as Primary Key)...AFTER i creating new record it taking Sequence number 13???
    It should be coming last number as 12?
    Do you have any special reason why next value in your case need to be again 12?

    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai
  • 2. Re: how to Trigger affect to  Sequence  in Apex Page ..
    Ramani_vadakadu Journeyer
    Currently Being Moderated
    @Jari...thank you for your reply..i understand and clear from your guidelines ..

    my Special Reason is

    I have bulk data uploading entry at one time.Reference # based on Seq number generating by the trigger.so i need both of numbers should be same.

    P2_DOC_REF_NO with default value as pl/sql fn body
    Declare 
      
      Ref_val_ varchar2(16):= Null;
      Seq_Val_ Number;
    begin 
        if  :P2_DOC_ID is not null and  :P3_BTA_REF is null then
     
               Select DOC_ID
               into Seq_Val_ 
               from DMS_MASTER where doc_id=:p2_doc_id;
    
              select 'DOC'||'/'||lpad(Seq_Val_,7,0)||'/'||to_char(sysdate,'YY')
              into Ref_val_ 
              from dual;
            -- Populate ref #
          return  Ref_val_;
        
            
    elsif :P3_BTA_REF is null and  :P2_DOC_ID is null then
           Select Nvl(count(DOC_ID),0) + 1
            into Seq_Val_ 
            from DMS_MASTER;
           select 'DOC'||'/'||lpad(Seq_Val_,7,0)||'/'||to_char(sysdate,'YY')
              into Ref_val_ 
              from dual;
            -- ref number
            return  Ref_val_; 
            
     
     else
            return Ref_val_; 
    
        end if;
          
    
    end;
    Please if u have any special root for solving the issue!
    Thanx
    Ram

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points