12 Replies Latest reply: Apr 8, 2013 7:47 AM by fac586 RSS

    apex form with TRIGGER on DML

    Ramani_apex
      i have using DB trigger as following..in Apex IR page with form,when i upload 10 files. after i deleted 5 files.the next sequence number coming with 11
      <b>my problem,i need sequence number 6 After the deleted row's,
      How to chage the DB trigger for Dynamic Seq number when DML action processing.</b>
      create or replace 
      trigger LEGAL_TRIG  
         before insert on "KU"."LEGAL_DMS_MASTER" 
         for each row 
      begin  
         if inserting then 
            if :NEW."DMS_BLOB_ID" is null then 
               select LEGAL_MASTER_SEQ.nextval into :NEW."DMS_BLOB_ID" from dual; 
            end if; 
         end if; 
      end;
      Before iam using Pl/SQL code for getting Seq number as following code
      declare
      seq number;
      begin
      
       if :P2_DMS_BLOB_ID is null then
            select nvl(max(DMS_BLOB_ID),0)+1 into seq from KU. LEGAL_DMS_MASTER;
          :P2_DMS_BLOB_ID :=seq;
       end if;
       
      end
      PL/Sql code Its working fine,but when i using the DB trigger its showing seq number issue.


      Thanx,
      Ram
        • 1. Re: apex form with TRIGGER on DML
          fac586
          Ramani_vadakadu wrote:
          i have using DB trigger as following..in Apex IR page with form,when i upload 10 files. after i deleted 5 files.the next sequence number coming with 11
          <b>my problem,i need sequence number 6 After the deleted row's,
          Why?
          How to chage the DB trigger for Dynamic Seq number when DML action processing.</b>
          create or replace 
          trigger LEGAL_TRIG  
          before insert on "KU"."LEGAL_DMS_MASTER" 
          for each row 
          begin  
          if inserting then 
          if :NEW."DMS_BLOB_ID" is null then 
          select LEGAL_MASTER_SEQ.nextval into :NEW."DMS_BLOB_ID" from dual; 
          end if; 
          end if; 
          end;
          Before iam using Pl/SQL code for getting Seq number as following code
          declare
          seq number;
          begin
          
          if :P2_DMS_BLOB_ID is null then
          select nvl(max(DMS_BLOB_ID),0)+1 into seq from KU. LEGAL_DMS_MASTER;
          :P2_DMS_BLOB_ID :=seq;
          end if;
          
          end
          PL/Sql code Its working fine,but when i using the DB trigger its showing seq number issue.
          This approach will not generate unique values in a multi-user environment. To do so access to this code must be serialized, which does not scale.

          Sequence-generated values are guaranteed to be unique, not gap-free. For never-ending discussion on this topic see any or all of these links.
          • 2. Re: apex form with TRIGGER on DML
            Ramani_apex
            hi,
            is it possibe to fix this issue over the DB trigger?when deleted the row!
            plz help!
            • 3. Re: apex form with TRIGGER on DML
              fac586
              Ramani_vadakadu wrote:
              hi,
              is it possibe to fix this issue over the DB trigger?when deleted the row!
              There is no issue. Sequences are not gap free.
              • 4. Re: apex form with TRIGGER on DML
                Ramani_apex
                hai fac586 ,
                my constraint is not in Seq number,i have another column called Reference number based on the Seq number (e.g) Seq number 1 .my ref number is DOC/00001/2013 generated by default value(pl/sql code)
                <b>The problem</b >
                when the row is deleted ,my reference number gab has raising. so i could not find what happened missing reference number!

                can you suggest any idea about this case?

                Thanx,
                Ram
                • 5. Re: apex form with TRIGGER on DML
                  fac586
                  Ramani_vadakadu wrote:
                  hai fac586 ,
                  my constraint is not in Seq number,i have another column called Reference number based on the Seq number (e.g) Seq number 1 .my ref number is DOC/00001/2013 generated by default value(pl/sql code)
                  What PL/SQL code?

                  Do not store derived values like this. Generate them in a view or (in 11g) a virtual column.
                  <b>The problem</b >
                  when the row is deleted ,my reference number gab has raising. so i could not find what happened missing reference number!

                  can you suggest any idea about this case?
                  No I can't. I have no idea what you are trying to do, nor how you are attempting to do it. Describe the problem in detail, as described here: +{message:id=9360002}+ , or reproduce it on apex.oracle.com and post guest developer credentials for the workspace.
                  • 6. Re: apex form with TRIGGER on DML
                    M Tajuddin
                    Hi
                    I am not sure though what is your exact problem, my understanding if you don't want to use your sequence to generate auto number and don't want to leave any gap then why cannot you include the other portion of your code in the trigger like this -
                    create or replace 
                    trigger LEGAL_TRIG  
                       before insert on "KU"."LEGAL_DMS_MASTER" 
                       for each row 
                    begin  
                       if inserting then 
                          if :NEW."DMS_BLOB_ID" is null then 
                            select nvl(max(DMS_BLOB_ID),0)+1 into :NEW."DMS_BLOB_ID" from KU. LEGAL_DMS_MASTER;
                          end if; 
                       end if; 
                    end;
                    This will add 1 on the max value of the DMS_BLOB_ID. Hope this helps.

                    Regards,
                    Tajuddin
                    Blog: http://tajuddin.saradesh.com
                    • 7. Re: apex form with TRIGGER on DML
                      fac586
                      M Tajuddin wrote:
                      Hi
                      I am not sure though what is your exact problem, my understanding if you don't want to use your sequence to generate auto number and don't want to leave any gap then why cannot you include the other portion of your code in the trigger like this -
                      create or replace 
                      trigger LEGAL_TRIG  
                      before insert on "KU"."LEGAL_DMS_MASTER" 
                      for each row 
                      begin  
                      if inserting then 
                      if :NEW."DMS_BLOB_ID" is null then 
                      select nvl(max(DMS_BLOB_ID),0)+1 into :NEW."DMS_BLOB_ID" from KU. LEGAL_DMS_MASTER;
                      end if; 
                      end if; 
                      end;
                      This will add 1 on the max value of the DMS_BLOB_ID. Hope this helps.
                      As pointed out above this is not a safe method in a multi-user environment. Two or more users can get the same value at the same time. To do this, access to the query must be serialised, meaning that the system is less scalable.
                      • 8. Re: apex form with TRIGGER on DML
                        M Tajuddin
                        Yes, I agree with fac586. It is not good a approach though for multiuser system.
                        • 9. Re: apex form with TRIGGER on DML
                          Ramani_apex
                          hai fac586 ,
                          as you request, i Publish my application in apex.oracle.com.please login and see.
                          WKS : ram_r&d
                          User name/password: aramani/apex
                          
                          Single file entry : Seq number generate from pl/sql code
                          http://apex.oracle.com/pls/otn/f?p=19617:2
                          
                          bulk entry :  Seq # from DB trigger
                          http://apex.oracle.com/pls/otn/f?p=19617:17
                          this case i need the seq number to reflect reference number.
                          Thanx,
                          Ram

                          Edited by: Ramani_vadakadu on Apr 8, 2013 1:48 PM
                          • 10. Re: apex form with TRIGGER on DML
                            fac586
                            Ramani_vadakadu wrote:
                            hai fac586 ,
                            as you request, i Publish my application in apex.oracle.com.please login and see.
                            WKS : ram_r&d
                            User name/password: aramani/apex
                            
                            Single file entry : Seq number generate from pl/sql code
                            http://apex.oracle.com/pls/otn/f?p=19617:2
                            
                            bulk entry :  Seq # from DB trigger
                            http://apex.oracle.com/pls/otn/f?p=19617:17
                            this case i need the seq number to reflect reference number.
                            Thanx,
                            Ram
                            I don't understand what the problem is. As both the file and the ref number are stored in the same row in DMS_MASTER, there's no referential problem when the row is deleted.

                            DOC_REF_NO appears to be derivable information and therefore should be computed when required (for example using a view or virtual column) rather than stored in the table.
                            • 11. Re: apex form with TRIGGER on DML
                              Ramani_apex
                              HI fac586 ,

                              when i upload single file with reference number coming with same seq#.same time another user uploading bulk entry.
                              if i deleted one row then uploading documents have Ref # not equal Seq #
                              Please login now..I DELETED 2 ROW by Single entry user.
                              Thanx,
                              Ram

                              Edited by: Ramani_vadakadu on Apr 8, 2013 4:26 PM
                              • 12. Re: apex form with TRIGGER on DML
                                fac586
                                Ramani_vadakadu wrote:
                                HI fac586 ,

                                when i upload single file with reference number coming with same seq#.same time another user uploading bulk entry.
                                if i deleted one row then uploading documents have Ref # not equal Seq #
                                Yes, this is why your approach cannot work. You have to:

                                1. Generate the sequence number using an Oracle sequence.
                                2. Derive the reference number when it is needed using the sequence value, rather than storing it in the table.