8 Replies Latest reply on Mar 22, 2019 6:53 AM by fac586

    Trigger delete not expected

    I-Chang Lee

      I write a pl/sql and a Javascript to handle button dynamic action and following content are the source code,when I click the button ,system show a confirm box to check I want to recreate the whole data, but when I click 『OK』,system trigger to delete the master data. and I have foreign key between the tables and can't be deleted. system show

      1 error has occurred

      • ORA-02292: integrity constraint (ORATRIX.HR_EXAM_SET_B2_FK) violated - child record found

       

      I were confirmed that I am not write any code to delete the master data. Why system trigger to delete data not expected?

       

      pl/sql content :

       

      DECLARE

         v_return   VARCHAR2 (1);

         v_cnt      NUMBER;

         v_cnt1     NUMBER;

         v_cnt2     NUMBER;

      BEGIN

         SELECT COUNT (*)

           INTO v_cnt

           FROM ORATRIX.hr_exam_set_b2

          WHERE exam_no = :P14_exam_no AND seg_segment_no = :P14_seg_segment_no;

         IF v_cnt > 0

         THEN

            SELECT COUNT (*)

              INTO v_cnt1

              FROM ORATRIX.hr_exam_set_b4

             WHERE     exam_no = :P14_exam_no

                   AND seg_segment_no = :P14_seg_segment_no

                   AND canceled = 'N';

       

       

            IF v_cnt1 > 0

            THEN

               SELECT COUNT (*)

                 INTO v_cnt2

                 FROM ORATRIX.hr_exam_set_a

                WHERE     exam_no = :P14_exam_no

                      AND seg_segment_no = :P14_seg_segment_no

                      AND fire_yn = 'Y';

       

               IF v_cnt2 > 0

               THEN

                   RAISE_APPLICATION_ERROR(-20001,apex_lang.message('14_1'));

               ELSE

                  :P14_RETURN := '1';

               END IF;

            ELSE

                ORATRIX.p_hrfpi0053_fw (v('SESSION_CO'), :P14_exam_no);

       

            END IF;

         ELSE

            RAISE_APPLICATION_ERROR(-20001,apex_lang.message('14_3'));              

         END IF;

        

      END;

       

      Javascript content:

      if ($v("P14_RETURN")=='1' ) {

          val = apex.confirm( apex.lang.getMessage( "14_2"));

          if (val){

             

              execute_procedure;

             

          }

         

      }

       

       

      function execute_procedure(){

         

          apex.server.process("execute_p_hrfpi0053_fw",

         { x01: $v("P14_SEG_SEGMENT_NO"), x02: $v("P14_EXAM_NO") },

         {

            success: function(pData) {

               //you can do anything here

               console.log(pData);

               apex.message.alert(pData.v_result);

            }

         }

      );

         

      }

       

      Application Process

      execute_p_hrfpi0053_fw

      declare

      p_seg_segment_no VARCHAR2(10) := APEX_APPLICATION.g_x01;

      p_exam_no VARCHAR2(20) := APEX_APPLICATION.g_x02;

       

      begin

          #OWNER#.P_HRFPI0053_FW(p_seg_segment_no,p_exam_no);

       

       

          apex_json.open_object; 

          apex_json.write('success', true); 

          apex_json.write('v_result', apex_lang.message('14_4')); 

          apex_json.close_object;

       

       

      end;

        • 1. Re: Trigger delete not expected
          Scott Wesley

          What does P_HRFPI0053_FW do?

           

          I hope it at least re-checks whatever validation you're doing here, because otherwise you're going to have concurrent handling problems.

          • 2. Re: Trigger delete not expected
            I-Chang Lee

            P_HRFPI0053_FW is a procedure to generate the performance evaluation people data, those are detail data,

             

            and can be regenerated several times.

             

            Yes I checked the procedure ten times up. It was not contain any command to delete the master data.

             

            Why the dynamic action trigger form's Automatic Row Processing(DML) delete action.

            • 3. Re: Trigger delete not expected
              fac586

              2981933 wrote:

              Please update your forum profile with a recognisable username instead of "2981933".

              P_HRFPI0053_FW is a procedure to generate the performance evaluation people data, those are detail data,

               

              and can be regenerated several times.

               

              Yes I checked the procedure ten times up. It was not contain any command to delete the master data.

               

              Why the dynamic action trigger form's Automatic Row Processing(DML) delete action.

              Is the dynamic action actually being executed or is the page being submitted? What Action is defined for the OK button?

               

              What is logged when the OK button is clicked with the application running in debug mode?

              • 4. Re: Trigger delete not expected
                Scott Wesley

                ORA-02292 does not necessarily have to be DELETE, it could happen with an UPDATE.

                • 5. Re: Trigger delete not expected
                  I-Chang Lee

                  I write a pl/sql and a Javascript to handle button dynamic action  as follow

                  The Refresh action is refresh the region data.

                  2019-03-14_151007.jpg

                  The following is the screen for debugging,the message has 『accept delete』 ,so I think the system trigger the delete procedure.

                  2019-03-14_150835.jpg

                  • 6. Re: Trigger delete not expected
                    fac586

                    I-Chang Lee wrote:

                     

                    I write a pl/sql and a Javascript to handle button dynamic action as follow

                    The Refresh action is refresh the region data.

                     

                    The following is the screen for debugging,the message has 『accept delete』 ,so I think the system trigger the delete procedure.

                    2019-03-14_150835.jpg

                    The system is not going to initiate the delete on its own. Something in the application is causing it to do so, with the most likely explanations being either a button with an incorrect action, or a dynamic action that is being unexpectedly triggered.

                     

                    • Check the Action and Database Action properties on all of the buttons.
                    • If there is a dynamic action that invokes the delete process, check its triggering event, considering whether this might come about unexpectedly from a cascade of other events.
                    • 7. Re: Trigger delete not expected
                      I-Chang Lee

                      I remove the process (Automatic Row Processing(DML)) and wrote the insert and update action by my own PL/SQL.

                      And everything was fine .

                      So I think this was an error on Automatic Row Processing(DML).

                      • 8. Re: Trigger delete not expected
                        fac586

                        I-Chang Lee wrote:

                         

                        I remove the process (Automatic Row Processing(DML)) and wrote the insert and update action by my own PL/SQL.

                        And everything was fine .

                        So I think this was an error on Automatic Row Processing(DML).

                        I don't. No one else is reporting this. The chances of that happening and only being apparent in one page of one application are negligible.

                         

                        What are the Action and Database Action properties on all of the buttons?