4 Replies Latest reply: Nov 25, 2012 2:43 AM by user346369 RSS

    PRE INSERT TRIGGER not firing

    NZK
      Dear All
      I'm using Oracle Forms 6i with 9i Database
      I have written the following code.

      This procedure will be fired when i press the Save button

      PROCEDURE P_COMMIT IS
      v_msglvl NUMBER;
      BEGIN
      IF NOT FORM_SUCCESS THEN
      RAISE FORM_TRIGGER_FAILURE;
      ELSE
      v_msglvl := :SYSTEM.MESSAGE_LEVEL;
      :SYSTEM.MESSAGE_LEVEL := 25;
      COMMIT_FORM;
      :SYSTEM.MESSAGE_LEVEL := v_msglvl;
      MESSAGE('1 Record applied and saved');
      END IF;
      END;

      THE PRE-INSERT-TRIGGER WRITTEN AS FOLLOWS

      IF FORM_SUCCESS THEN
      DECLARE
      v_ponum ng_purchase_hd%phd_po#%TYPE;
      BEGIN
      SELECT ng_po#_seq.NEXTVAL
      INTO v_ponum
      FROM dual

      :ng_purchase_hd.phd_po# := v_ponum;
      END;
      END IF;

      Here it should fire the Pre Insert Trigger when press the save button and it should create unique purchase order number. But pre insert trigger not firing. Please help.

      NZK
        • 1. Re: PRE INSERT TRIGGER not firing
          Andreas Weiden
          First, i would remove the IF FORM_SUCCESS-part in your PRE-INSERT-trigger.

          Are you sure the trigger doesn't fire at all? Where do have defined the PRE-INSERT-trigger (form, block, item)? Are you sure you have a newly created record in your form? I would also try the same without your KEY-COMMIT-trigger (just to check).
          • 2. Re: PRE INSERT TRIGGER not firing
            NZK
            Dear Andreas

            Thank you for you response.

            As you told me I removed the IF FORM_SUCCESS part from the PRE-INSERT-TRIGGER, except this I didn't change anything. My trigger is created FORM-LEVEL. I made a small change in the :PHD_PO# TEXT ITEM. I changed the REQUIRED to be as NO. Now the trigger is firing and creating unique numbers as well. What could be the reason for this? Thank you for you response
            • 3. Re: PRE INSERT TRIGGER not firing
              Andreas Weiden
              Before you can commit, forms makes sure all data in your form is valid. And if you define a visible item as required, forms will check if it has a value, otherwise it cannot commit.
              • 4. Re: PRE INSERT TRIGGER not firing
                user346369
                The problem is here:
                  v_msglvl := :SYSTEM.MESSAGE_LEVEL;
                  :SYSTEM.MESSAGE_LEVEL := 25;
                  COMMIT_FORM;
                  :SYSTEM.MESSAGE_LEVEL := v_msglvl;
                By setting Message_Level to 25, you are hiding error messages that you SHOULD see. Setting it to 25 caused your "Field is required" message to be hidden.

                Set Message_Level to zero and never set it higher. If there is any message you need to treat specially, do so in the on-error form-level trigger.