10 Replies Latest reply: Jan 25, 2013 3:02 PM by HamidHelal RSS

    Which trigger to use for insert data into db table in Forms

    986451
      Hi,

      My form is current having a database block with table reference. When enter data into form field and click on save button. Automatically the record is inserted into database table.
      I want to make this as manual insert. I changed the data block to a non-database. Where should i write the insert statement in order to insert data into table.
      Is it Key-commit trigger at form level?

      Please advise.

      Thanks,
      Yuvaraaj.
        • 1. Re: Which trigger to use for insert data into db table in Forms
          HamidHelal
          983448 wrote:
          Hi,

          My form is current having a database block with table reference. When enter data into form field and click on save button. Automatically the record is inserted into database table.
          I want to make this as manual insert. I changed the data block to a non-database. Where should i write the insert statement in order to insert data into table.
          Is it Key-commit trigger at form level?
          Hi Yuvaraaj,
          If insertion is possible with forms by database block, it's not wiser to change to manual insert. However, it's depends on your requirement, In which way you want..

          you can input multiple row then insert all by clicking a button or after every row...
          Considering with a buttion, at When-Button-Press trigger write
          Insert into table_name(column,....)
          values(:block.Item_name,......);
          forms_ddl('commit');
          Hope this helps


          hamid


          Mark correct/helpful to help others to get right answer(s).*
          • 2. Re: Which trigger to use for insert data into db table in Forms
            986451
            Hi Hamid,

            Thanks for the reply. Currently i am going to insert only single record. Iam not supposed to created a save button and write the insert logic inside the button.
            Insert should happen when we click on the save which is inbuilt in the form. In this case where should i write the insert statement.


            Thanks,
            Yuvaraaj.
            • 3. Re: Which trigger to use for insert data into db table in Forms
              986451
              Hi hamid,

              Ypu are right. The reason i chnaged the database block to non-database is Currently i have a database block with form field canvas which insert only 1 record in to table when we click on standard save button. The requirement was to add a field called CHANNEL which should have multiple values displayed. (i created this channel field in a seperate datablock (non database) and used the same canvas.) When we insert data in all fields (single record) and channel we should be able to selected multiple channel (say A,B and C) when we click on save then 3 records should be inserted in to the table which looping values for each channel. This was the actual requirement and this is the reason why iam changing the block to non-database block.

              Thanks,
              Yuvaraaj.
              • 4. Re: Which trigger to use for insert data into db table in Forms
                HamidHelal
                Hi Yuvaraaj.
                Insert should happen when we click on the save which is inbuilt in the form. In this case where should i write the insert statement.
                Forms in built save commit's the form data where block is based on database not non database.

                @2nd reply
                Ypu are right. The reason i chnaged the database block to non-database is Currently i have a database block with form field canvas which insert only 1 record in to >table when we click on standard save button. The requirement was to add a field called CHANNEL which should have multiple values displayed. (i created this channel >field in a seperate datablock (non database) and used the same canvas.) When we insert data in all fields (single record) and channel we should be able to selected >multiple channel (say A,B and C) when we click on save then 3 records should be inserted in to the table which looping values for each channel. This was the actual >requirement and this is the reason why iam changing the block to non-database block.
                You are talking about two blocks.. 1. Master block and 2. Details block name channel
                You are inserting one record in master block then insert 3 record name A,B,C for that master record.
                Now you want master record should insert to each A,B,C record. Means
                'how are you' --master record 
                and you want
                
                'A'- 'how are you'
                'B'- 'how are you'
                'C'- 'how are you'
                OR
                ?
                Ok. If you want master record save in database and then want to save non-database(channel) data into database USE Post-Insert trigger at block level and do the rest.


                Hope this helps...


                Hamid


                Mark correct/helpful to help others to get right answer(s).*

                Edited by: HamidHelal on Jan 26, 2013 1:20 AM
                • 5. Re: Which trigger to use for insert data into db table in Forms
                  986451
                  Yes Hamid. You are right. To tell you in a more clear way

                  Master block: How are you
                  Detail block: choose Channel A, B and C

                  When save.

                  Master Record result: 3 inserts
                  A | How are you
                  B | How are you
                  C | How are you.

                  You got the requirement right. Thanks for helping me. In this case. If i keep the mast block as a database block, it will insert only 1 record.
                  Can you suggest me how exactly we can achieve this by having in data base block itself and then using Post-Insert trigger at block level to achieve the loop inserting process.

                  Thanks a lot.

                  -Yuvaraaj.
                  • 6. Re: Which trigger to use for insert data into db table in Forms
                    HamidHelal
                    983448 wrote:
                    Yes Hamid. You are right. To tell you in a more clear way

                    Master block: How are you
                    Detail block: choose Channel A, B and C

                    When save.

                    Master Record result: 3 inserts
                    A | How are you
                    B | How are you
                    C | How are you.

                    You got the requirement right. Thanks for helping me. In this case. If i keep the mast block as a database block, it will insert only 1 record.
                    Can you suggest me how exactly we can achieve this by having in data base block itself and then using Post-Insert trigger at block level to achieve the loop inserting process.

                    Thanks a lot.
                    Hi Yuvaraaj,
                    assuming both are non database block. and you want to insert by clicking built-in save.
                    YOu can try,
                    create On-Commit trigger at form level and write your code like --- Not sure on-commit works or not in this stage
                    go_block('channel');
                    first_record;
                    loop
                    insert into table(column_name,....)
                    values(:master.column,:channel.column..........);
                    next_record;
                    
                    exit when your_condition;
                    end loop;
                    forms_ddl('commit');
                    Hope this helps



                    Hamid








                    Mark correct/helpful to help others to get right answer(s).*

                    Edited by: HamidHelal on Jan 26, 2013 1:53 AM
                    • 7. Re: Which trigger to use for insert data into db table in Forms
                      986451
                      Hi Hamid,

                      I tried a simple insert in the Key - Commit trigger. When i save on save. Iam getting FRM-40401: No Changes to Save message and the record was not getiting inserted into table.

                      Any other ideas?

                      I tried the same insert by writing the insert logic in a save button as well. Still ima faicing the same error.

                      Thanks,
                      yuvaraaj.
                      • 8. Re: Which trigger to use for insert data into db table in Forms
                        HamidHelal
                        983448 wrote:
                        Hi Hamid,

                        I tried a simple insert in the Key - Commit trigger. When i save on save. Iam getting FRM-40401: No Changes to Save message and the record was not getiting inserted into table.

                        Any other ideas?

                        I tried the same insert by writing the insert logic in a save button as well. Still ima faicing the same error.
                        Hi yuvaraaj.
                        Message is not the issue. First you need to confirm data are getting save. Take a button and write simple insert statement on it and must write
                        Forms_DDL('commit'); 
                        at the end.

                        when success ,write your logic on the button and test. then try to use other triggers if need.


                        Hope this helps....


                        Hamid
                        • 9. Re: Which trigger to use for insert data into db table in Forms
                          986451
                          Hi Hamid,

                          Thanks a lot. I tried the insert statement in the Key - Commit Trigger at Form level as you said.

                          Data is getting inserted in to the table.

                          Now i will frame the logic in key-commit trigger itself to loop multiple times for channel insert. Thanks a lot.

                          Can you guide me how to suppress the message FRM-40401 No Change to Save and instead i wanted to put 1 transaction record successful.. some like like that.

                          Thanks,
                          Yuvaraaj.
                          • 10. Re: Which trigger to use for insert data into db table in Forms
                            HamidHelal
                            Can you guide me how to suppress the message FRM-40401 No Change to Save and instead i wanted to put 1 transaction record successful.. some like like that.
                            Hi Yuvaraaj.
                            And this is my last post as i'm going to sleep. ;)
                            Take Form Level trigger named Pre-Form
                            and write on it
                            :SYSTEM.MESSAGE_LEVEL := 5;
                            Hope this works...


                            Hamid


                            Mark correct/helpful to help others to get right answer(s).*