This discussion is archived
12 Replies Latest reply: Apr 8, 2013 5:47 AM by fac586 RSS

apex form with TRIGGER on DML

Ramani_vadakadu Journeyer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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_vadakadu Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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_vadakadu Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MTajuddin Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MTajuddin Journeyer
    Currently Being Moderated
    Yes, I agree with fac586. It is not good a approach though for multiuser system.
  • 9. Re: apex form with TRIGGER on DML
    Ramani_vadakadu Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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_vadakadu Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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