Forum Stats

  • 3,752,284 Users
  • 2,250,483 Discussions
  • 7,867,783 Comments

Discussions

Insert-procedure trigger fires instead of update procedure in Oracle forms

User_L6UFC
User_L6UFC Member Posts: 2 Green Ribbon

I have a form which is used to modify a column in a table. The record which is being updated is failing some validation, but in this form all those failures are overridden and the column will be updated. This works fine for majority of records. However for few records the commit is not happening. When debugged I found that for these problematic records INSERT-PROCEDURE trigger is fired where as for the successful records UPDATE-TRIGGER is fired which is correct. Can somebody tell why this is happening and how to solve this?

My form version is 12.2.1.3.0 and it is on Redhat Linux server.

Thanks

K Raghavendra Bhat

Answers

  • GauravNagpal
    GauravNagpal Member Posts: 20 Red Ribbon
    edited Jun 16, 2020 1:45PM

    Please follow the step by step path:-

    1. First., Delete the code from both triggers (On-Insert and On-Update). and then compile and deploy the form.

    2. Make a change to the only insert trigger  and then compile and deploy the form,

    3. Make a change to the On Update trigger with On-Insert trigger code and then compile and deploy the form.

    4. Look the difference & if you could not then use fnd_message.debug to print the values to determine where things are going wrong

    5. Share the result/finding with us also.

  • User_L6UFC
    User_L6UFC Member Posts: 2 Green Ribbon
    edited Jun 17, 2020 7:08AM

    Hi Gaurav,

    Thanks for the help. However I was able to find the issue. This issue was lying in the database. When the form updated a column the :SYSTEM.RECORD_STATUS was changing to INSERT and due to this INSERT-PROCEDURE trigger was firing. the culprit here was data in one of the column. It had a "=" sign at the beginning. After updating Oracle was querying the record from database and this column was part of the query. due to this '=' sign it was not able to re query the same record and no record were fetching from database. So forms was considering the record as New record and was changing the :SYSTEM.RECORD_STATUS to INSERT. After removing the '=' sign it worked fine.

  • GauravNagpal
    GauravNagpal Member Posts: 20 Red Ribbon
    edited Jun 17, 2020 8:45AM

    Thank you for letting everyone know - if the issue is resolved. could you please mark as answered so that we all can take help by looking at the description you wrote.

    Thx, Gaurav Nagpal