8 Replies Latest reply on Dec 20, 2012 8:06 AM by O.Developer

    sequence in pre-insert trigger

      db , dev 10grel2 , xp sp3
      hi all,
      about dealing with sequences , if there is a pk field in a form , i should insert the sequence's nextval into it through
      a pre-insert trigger . that is what i know .

      the problem is :
      i've a block with two fields (pat_id "pk" , pat_name) , i inserts the nextval of pat_patid_seq sequence into "pat_id" through a pre-insert trigger .
      but i think it is not a good way , because the nextval of the sequence does not appear in the "pat_id" field to the user .

      how could i leave the field empty to the user ?

        • 1. Re: sequence in pre-insert trigger
          In your PRE-INSERT trigger if you did something like:
          select pat_patid_seq.nextval 
            into :BLOCK_NAME.PAT_ID;
          Then that value should appear for the user once the commit occurs - assuming that you display the field on the canvas. I probably don't understand what you are trying to do, but if you don't want the user to see it, then take the item off of the canvas.
          1 person found this helpful
          • 2. Re: sequence in pre-insert trigger
            what do you want me to do ,
            do you want me to only view the pat_name? is this the way to deal with such these issues ? should not i display the
            pat_id field on the canvas .

            i've tried before to "insert into table name values(pat_patid_seq.nextval) , but it inserts a row with only the "nextval" ,
            then it can not insert the record in the form , because "pat_id" can not be null .

            what is the right way to solve this problem ?
            • 3. Re: sequence in pre-insert trigger
              What you do is totally up to you and your customer's requirements.

              I always populate my primary key values via the PRE-INSERT trigger using:
              select sequence_name.nextval
                into :BLOCK_NAME.PRIMARY_KEY_ITEM
                from dual;
              That way Oracle handles the population via forms not an insert statement.

              As far as do you display the PAT_ID field that is up to you and your customer. It is very rare that I display a primary key value populated by a sequence, but I have done it if that number is something like a Case Number or Employee ID field that the user needs to know. If it is just a primary key, that is used to tie two table together and the user doesn't need to know it, then I don't show it.
              • 4. Re: sequence in pre-insert trigger
                my form is for inserting patients data into the patients table , i think the user wants to know the number of the patient
                he inserts ,
                suppose that he want ,
                how could i display the field and the number of the sequence to the user , i've tried many ways , but vainly .
                how can i do it ?
                • 5. Re: sequence in pre-insert trigger
                  First, you would have to make sure that both the Patient ID and the Patient Name items are visible on the canvas.

                  You then want to add the populate sequence logic to your PRE-INSERT trigger like you mentioned originally (where BLOCK_NAME is the name of your database block):
                  select pat_patid_seq.nextval 
                    into :BLOCK_NAME.PAT_ID
                    from dual;
                  As soon as that trigger fires, then the user should be able to see the Patient ID because you are assigning the sequence directly to the database item.
                  • 6. Re: sequence in pre-insert trigger
                    how is that ? i think the pre-insert trigger does not fire untill you press the commit button , so
                    the pat_id field is empty untill the user clicks the save button , then the value is appeared in the field after the commit is done , and sure i've to clear my_block after commit , so there is no way the user can see the nextval .
                    • 7. Re: sequence in pre-insert trigger
                      You kind of left out the part of "i've to clear my_block after commit" until now...

                      You are right - if you are committing the record then clearing the block then the user won't see the patient id. Now you have to figure out the best way of handling this based on your user's requirements.

                      Possible solutions:
                      1. You do not clear the block after commit. Maybe you add a "Clear" button to the form that they press after they see the patient id.
                      2. You populate the Patient Id in WHEN-NEW-BLOCK-INSTANCE before you ever commit the record. This way the patient id exists before the users even enter the name of the patient in the form.
                      3. You create an alert that displays the patient id after the commit and clear block is initiated. That will give the user the ability to record the patient id if they need it.
                      4. etc...
                      • 8. Re: sequence in pre-insert trigger

                        It is basically depends upon how you design the form.

                        However , in case , you are not bother about sequence is generated and if not used, then put your sequence populate statement on new_form_instance trigger.

                        This way the moment user enter the menu, he will get he sequence, but will be sequence lost if not save.

                        Hope u got the logic