5 Replies Latest reply on Jul 24, 2013 1:53 PM by Andreas Weiden

    On-lock Trigger not working properly.


      Hi Everyone,


      I need to use on-lock trigger to satisfy the requirement.

      The requirement is - for a given supplier name and supplier profile the form should be locked.Normally forms locks row by row. But my requirement is if any user doing some changes on a specific supplier and supplier profile then no other user can change for that supplier and supplier profile. If any user opens that then we need to show that it has been locked by some other user.

      But the 2nd user can open the same form for some other supplier and supplier profile.

      I need to write an on-lock trigger.

      Can you please suggest what should I write in on-lock trigger ?

      let table name is SUPPLIER  sp.


      sp.supplier_name = :blk1.supplier_name

      and sp.supplier_prof = :blk1.supplier_prof

      The above condition should be locked.

      Please help me out.

        • 1. Re: On-lock Trigger not working properly.
          Andreas Weiden

          maybe you can give some more details. What table is your form based on? Is it the same table where you want to lock records in? Are there any other datablocks based on other tables in your form?

          • 2. Re: On-lock Trigger not working properly.

            I am using oracle forms 10g. My form has two database block- blk1 and countrylist.having master-details relationship.blk1 is master.

            blk1  is on a view supplier_v and countrylist is on another view land_spring_v.

            blk1 has 3 i/p text fields -country, supplier name,supplier profile. Depending on the i/p given in blk1 countrylist block gets populated. Here its shown the country names where that supplier business.and for each country name shown in countrylist block, there is a check-box, User can check or uncheck these countries.

            Now my concern is if an user is working on a specific supplier(given in blk1) (for ex- country-INDIA,supplier - BSNL,supplier profile - bruger ) doing some checking or unchecking of the countries in countrylist block, then another user cannot come and do the same for that specific supplier ( country-INDIA,supplier - BSNL,supplier profile - bruger ). But he can do that for any other supplier.

            This locking should be done.

            please let me know if the requirement is clear now.

            • 3. Re: On-lock Trigger not working properly.
              Andreas Weiden

              Ok, so whenever you select a new supplier/profile in your blk1, you want to lock that supplier/profile.


              What you could do:


              1. lock the supplier immediately after the selection. I guess your block blk1 is not a db-block, so you would ad the locking at the end of your selection-procedure

              2. lock the supplier as soon as you start editing one of the country-rows, here you could use the ON-LOCK-trigger.


              An example of how the Locking-code looks is here Re: [Block based on From Query] On-Lock Trigger

              You have to do some additional steps, in the exception case (lock failed) you have to disable the whole county-block (with using SET_BLOCK_PROPERTY)


              Hope this helps

              • 4. Re: On-lock Trigger not working properly.

                blk1 is a db block....I already mentioned that. I need to write on-lock trigger . I think the 2nd option.What should be written in on-lock trigger and will it be a form-level or blk1 level? that's my confusion..

                • 5. Re: On-lock Trigger not working properly.
                  Andreas Weiden

                  In which block does the user change things, as i understood, in the second block. So a ON-LOCK-trigger on blk1 would not fire by default. So i would see the following options:


                  1. Create the ON-LOCK-trigger at the second block. But in it, lock the record from blk1.

                  2. Create the ON-LOCK-trigger at blk1. In the WHEN-DATABASE-RECORD-trigger on the second block, "tick" the record in block1 so that its ON-LOCK-trigger fires (like :blk1.SUPPLIER:=blk1.SUPPLIER;


                  An example of what should be written in the ON-LOCK-trigger was in my previous post.