2 Replies Latest reply: Jan 28, 2013 11:39 AM by Ramani_apex RSS

    how to Trigger affect to  Sequence  in Apex Page ..

    Ramani_apex
      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
          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_apex
            @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